Using 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.

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

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.

Oracle and Java - How Java Stored Procedures Are Published and Called

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.

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.

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

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.

Using XML to Transfer Data from JDBC to PL/SQL to .NET

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:

Supported datatype

PL/SQL procedure

Java stored procedure

.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

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 Listings 2a (MyJavaClass) and 2b (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 3 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 4 for the complete listing of EmpService.

Conclusion

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.