.NET and Oracle Java Stored Procedures—Bridging the Gap with XML

sing Oracle as your database in the .NET environment is relatively simple, except when returning JDBC ResultSets from Java stored procedures. The Microsoft and Oracle .NET data provider drivers allow for easy access to data from SQL queries and PL/SQL stored procedures. There is not, however, a simple interface to return ResultSet data from a Java stored procedure to a .NET client.

I was recently asked to build a new .NET interface on to an existing back-end process. The challenge was to return a ResultSet of data from an existing Oracle Java stored procedure to a .NET Web service. The problem definition seemed trivial; until I discovered that JDBC ResultSets produced by the Java stored procedures do not map to either PL/SQL or .NET datatypes.

My goal was to re-use an existing code base without changing that existing code base. Before considering XML, I thought that I could simply return the JDBC ResultSet from Java stored procedures to the .NET client. Since the JDBC ResultSet is not supported by ODP.NET (Oracle Data Provider driver for .NET) or by PL/SQL, I quickly found that my challenge revolved around data interchange between differing technologies. Neither Oracle’s stored procedure languages?PL/SQL, or Java?support data exchange of JDBC ResultSets. Oracle Java stored procedures can use PL/SQL REF cursors but PL/SQL cannot use a JDBC ResultSet.

I set out to prove that I could bridge the data gap. I was sure that XML would enable the data exchange from JDBC ResultSets to the .NET client via the ODP.NET Data Provider driver.

This article will show you how to consume Oracle Java stored procedures from .NET, and will show you how to transfer data from a JDBC ResultSet via XML.

Publishing and Calling Java Stored Procedures
Some understanding of how Java fits into the Oracle database is required to understand the complexity of the data exchange this article will discuss. Oracle stored procedures are written using either PL/SQL (Oracle’s scripting language) or Java. The catch with Java is that in order to publish the Java class you must create a public static method and expose the method through a PL/SQL function or procedure.

The PL/SQL function or procedure does not contain code?it contains a reference to a Java class and method. The function or procedure specifies the PL/SQL data type and the corresponding Java data type. For example, the PL/SQL function and procedure call the GetString method of the class called MyJavaClass. The function returns VARCHAR directly and the procedure returns the VARCHAR by reference as an OUT parameter rStr:

   Create or Replace Function GetStringFunc       return VARCHAR as language java name       'MyJavaClass.GetString()       'return java.lang.String';    Create or Replace Procedure GetStringProc(      rStr OUT VARCHAR) as language java name       'MyJavaClass.GetString(java.lang.String[])'; 

The Java class called MyJavaClass defines two public static methods called GetString. The PL/SQL function references the first GetString method and the PL/SQL procedure references the second GetString method:

   public class MyJavaClass {      public static String GetString()       {         return "Hello World";      }         public static void GetString(String[] rStr)       {         rStr[0] = "Hello World";      }   }

Notice that to pass a primitive data type by reference from PL/SQL to Java it MUST be an array of the primitive type. Primitive types in Java are passed by value; creating an array of primitives creates an object which is then passed by reference.

Notice that to pass a primitive data type by reference from PL/SQL to Java it MUST be an array of the primitive type.

The .NET Web Form accesses the Java methods of MyJavaClass through the PL/SQL function or procedure called GetStringFunc or GetStringProc. The key lines in this next code snippet show binding a parameter of type OracleDbType.Varchar2 to the command and casting the returned Varchar2 to a .NET string:

   // Bind the Oracle Varchar2 data type   OracleParameter param = cmd.Parameters.Add("str",   OracleDbType.Varchar2);   .   .   .   // Cast the Oracle data type to .NET string   string string_data = (string)       ((OracleString)(cmd.Parameters[0].Value)).Value;

See Listing 1 for the complete Web Form code listing.

Casting the Oracle native datatype returned as the output parameter to a .NET datatype takes two steps. The Native Oracle type is cast to an ODP.NET class or structure, then the ODP.NET’s Value property is cast to a .NET Framework datatype. See Table 1 for a subset list of datatype mappings.

Table 1: A subset of the Oracle native types supported by the ODP.NET, their corresponding ODP.NET type, and the .NET Framework datatype.

Oracle Native Type

ODP.NET Type

.NET Framework Datatype

CHAR

OracleString class structure

System.String

DATE

OracleDate structure

System.DateTime

CLOB

OracleString class structure

System.String

NUMBER

OracleDecimal

System.Decimal

VARCHAR2

OracleString class structure

System.String

The ODP.NET provides a datatype mapping between Oracle Native types and the .NET Framework for all Oracle Native datatypes; the same is not true for datatype mapping between PL/SQL and Java.

Transfer Data to .NET via XML
Oracle provides a set of tools called the Oracle XML Developer’s Kit (XDK), specifically in this case the XDK for PL/SQL. The XDK contains XML component libraries and utilities in the form of PL/SQL functions and procedures and Java classes that let you use XML from an Oracle database.

To transfer data from JDBC to PL/SQL and eventually to .NET as XML data requires four things:

  1. Supported datatype
  2. PL/SQL procedure
  3. Java stored procedure
  4. .NET client or Web service

The GetString example (two code snippets previous) used the String datatype across the .NET Framework, PL/SQL, and Java. However, the size limitation of the PL/SQL datatype, VARCHAR2, would only allow for a few rows of data. To overcome this limitation you can use the CLOB datatype.

Character Large Object (CLOB) Datatype
The CLOB datatype is supported by Java, ODP.NET, and by the .NET Framework. (See Table 1 for mapping CLOB to .NET datatypes.) To use the CLOB datatype you must declare and instantiate a variable of type CLOB in the PL/SQL procedure:

   clb CLOB;   dbms_lob.createtemporary(clb,true,session);

To create a local copy of a CLOB, execute the createtemporary function from the built-in package, dbms_lob, passing to the createtemporary function the declared CLOB variable, (clb), true or false (for caching), and a duration parameter. You must create the CLOB variable in the PL/SQL procedure and you must pass it to the Java stored procedure by reference.

Java Stored Procedure
Now you need to add a method to the class called MyJavaClass that will receive the JDBC ResultSet returned from the existing code base, convert the ResultSet to XML, and return the XML as a CLOB to the .NET Web service. The OracleXMLQuery and OracleXMLDocGenLob classes, a part of the XDK, have the necessary functionality to transpose the JDBC ResultSet into a CLOB containing an XML representation of the data. The OracleXMLQuery class has two constructors:

   OracleXMLQuery(Connection, String);   OracleXMLQuery(Connection, ResultSet);

The constructor for the OracleXMLQuery class accepts either a SQL query, or a JDBC ResultRet as a datasource.

Methods of the OracleXMLQuery class render the datasource as XML in the form of a Document object, string, or as a CLOB:

   Document OracleXMLQuery.getXMLDOM();   Document OracleXMLQuery.getXMLDOM(int);   String OracleXMLQuery.getXML();   String OracleXMLQuery.getXML(int);   void OracleXMLQuery.getXML(OracleXMLDocGenLob);   void OracleXMLQuery.getXML(OracleXMLDocGenLob, int);

The rendered XML can include a header in the form of a DTD or schema. Passing an integer parameter specifies the type of header; the default value of the parameter is 0 (no header). The parameter values are: 0 (None), 1 (DTD), and 2 (Schema).

The new method of the class called MyJavaClass is called getXMLClob. This method uses the OracleXMLQuery class to create an OracleXMLQuery object from the ResultSet returned from the ExistingCode class. An OracleXMLDocGenLob object is created as a CLOB container. The OracleXMLQuery then populates the OracleXMLDocGenLob with an XML representation of the data. If a non-zero value was supplied for the schemaCode parameter, the XML representation will contain the corresponding header information. The key points of the method are:

   public static void getXMLClob(int schemaCode,                        CLOB clob) throws Exception  {   .   .   .   // get the Resultset from the existing process      rs = existingCode.ComplexFunction();   // Create an OracleXMLQuery from the Resultset      xmlquery = new OracleXMLQuery(_conn, rs);   // Create OracleXMLDocGenLob from the CLOB parm      OracleXMLDocGenLob xmllob = new                OracleXMLDocGenLob(_conn, clob);   // Populate the CLOB with XML rendering of data      xmlquery.getXML(xmllob, schemaCode);      xmllob.flush();

To see the full code, see Listing 2 (MyJavaClass) and Listing 3 (Java code).

PL/SQL Procedure
The PL/SQL package, MyPlsqlXML, contains two procedures. The getXMLData procedure is a reference to the getXMLClob method of the Java class called MyJavaClass. The getXML procedure creates the temporary CLOB object used to transfer data between PL/SQL and JDBC and is the procedure called by the .NET client:

   CREATE or REPLACE package body MyPlsqlXML is     PROCEDURE getXML(metaType IN NUMBER,       xmlClob OUT CLOB) is   clb CLOB;        begin       dbms_lob.createtemporary(clb, true, lobDuration);           getXMLData(metaType, clb);           xmlClob := clb;        end;          PROCEDURE getXMLData(metaType IN NUMBER,       xmlClob IN CLOB)         as LANGUAGE JAVA NAME         'MyJavaClass.getXMLClob(int, oracle.sql.CLOB)';   end MyPlsqlXML;

See Listing 4 for the complete MyPlsqlXML code listing.

.NET Web Service
The .NET Web service is very simple and similar to the Web Form example. The getXMLData WebMethod of the Web service named EmpService returns an XmlDocument object. The Web service calls the PL/SQL procedure getXML, which returns a CLOB object containing an XML representation of the JDBC ResultSet. The CLOB object is then cast to a string variable, lob_data, through the ODP.NET OracleClob object. The XmlDocument object doc is then loaded from lob_data and returned to the calling program:

   // Bind the Oracle CLOP object Output parameter   OracleParameter param =        cmd.Parameters.Add("clobdata",OracleDbType.Clob);   param.Direction = ParameterDirection.Output;   // Execute command   cmd.ExecuteNonQuery();   // Cast the Oracle CLOB into a .NET   // string throught the Oracle CLOB object   string clob_data = (string)        ((OracleClob)(cmd.Parameters[1].Value)).Value;   // Load the XML string into the document variable   doc.LoadXml(clob_data);   .   .   return doc;

See Listing 5 for the complete listing of EmpService.

There was, in fact, a solution to the problem and it did include using XML. The solution presented above is not a very difficult or complex programming scenario, and when you look at all of the components that make the data exchange work, there is nothing that is really out of the ordinary. The complexity of the solution lies not only in the need to know and understand three different development tool sets, .NET, PL/SQL, and Java, but also understanding how they work or don’t work together.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS