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.

Listing 1: Web Form code behind for consuming a string from a Java Stored Procedure

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace xmlSample
{
    /// <summary>
    /// GetStringForm examplifies returning string 
    /// data from a Java Stored Procedures using
    /// the Oracle data provider for .NET 
    /// </summary>
    public class GetStringForm : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.Label lblString;
        protected System.Web.UI.WebControls.Label Label1;
        
        private void Page_Load(object sender, System.EventArgs e)
        {
            // Put user code to initialize the page here
            GetString();
        }

	#region Web Form Designer generated code
    override protected void OnInit(EventArgs e)
    {
        InitializeComponent();
        base.OnInit(e);
    }

    /// <summary>
    /// Required method for Designer support - do not modify
    /// the contents of this method with the code editor.
    /// </summary>
    private void InitializeComponent()
    {    
        this.Load += new System.EventHandler(this.Page_Load);
    }
	#endregion

    public void GetString()
    {
        // create and open a connnection the the Oracle database
        string connstr =  "User Id=scott;Password=tiger;DataSource=General";
        OracleConnection conn = new OracleConnection(connstr);
        conn.Open();

        // Create a Command object from the OracleCommand
        // class using the open connection
        OracleCommand cmd = new OracleCommand("", conn);
        // Set the PL/SQL Stored Procedure name
        cmd.CommandText = "GetStringProc";
        cmd.CommandType = CommandType.StoredProcedure;
        // Bind the Varchar2 data type as an output parameter
        OracleParameter param = cmd.Parameters.Add("strData", 
        OracleDbType.Varchar2);
        // Set the maximum return size for the string
        param.Size = 512;
        // Set the parameter direction to OUT
        param.Direction = ParameterDirection.Output;
        
        // Execute the stored procedure command
        try
        {
            cmd.ExecuteNonQuery();

            // Cast the Oracle Varchar2 into a .NET string throught the OracleString object
            string string_data = (string)((OracleString)(cmd.Parameters[0].Value)).Value;

            // Print out the string
            this.lblString.Text = string_data;
        }
        catch (Exception e)
        {
            this.lblString.Text = e.Message;
        }
        finally
        {
            // Dispose OracleCommand object
            cmd.Dispose();
            // Close and Dispose Oracleconnnection object
            conn.Close();
            conn.Dispose();
        }
    }
}

import java.sql.*;
import java.io.*;
import oracle.sql.CLOB;
import oracle.xml.sql.query.*;
import oracle.xml.sql.docgen.OracleXMLDocGenLob;
import oracle.jdbc.driver.OracleDriver;

  public class MyJavaClass {

    // Define constants for setting attributes of the
    // OracleXMLQuery object
    private static Connection _conn;
    private static int maxRows = -1;
    private static int skipRows = 0;
    private static String rowSetTag = "ROWSET";
    private static String rowTag = "ROW";
    private static String rowIdCol = null;
    private static String rowIdAttr = "num";
    private static String errorTag = "ERROR";
    private static boolean nullAttrInd = false;
    private static String collIdAttr = null;
    private static boolean raiseException = false;
    private static Reader metaHeader = null;
    private static Reader dataHeader = null;
    private static String docTag = "DOCUMENT";
    private static int docType = 1;
    
    
    public static String GetString() 
    {
      return "Hello World";
    }

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

    public static void getXMLClob(int schemaCode, CLOB clob) throws Exception  
    {
        ResultSet rs;
        OracleXMLQuery xmlquery = null;

        // Instantiate a copy of the ExistingCode class
        ExistingCode existingCode = new ExistingCode();
    
        try 
        {        
            // get a connection
            if(_conn == null)
                _conn = (new OracleDriver()).defaultConnection();

            // get the Resultset from the existing process
            rs = existingCode.ComplexFunction();
            // Create an OracleXMLQuery from the Resultset
            xmlquery = new OracleXMLQuery(_conn, rs);
            // Set the XML attributes on the XMLQuery object
            xmlquery.setRowsetTag(rowSetTag);
            xmlquery.setRowTag(rowTag);
            xmlquery.setCollIdAttrName(collIdAttr);
            xmlquery.setRaiseException(raiseException);
            xmlquery.setErrorTag(errorTag);
            xmlquery.useNullAttributeIndicator(nullAttrInd);
            xmlquery.setDataHeader(dataHeader, docTag);
            xmlquery.setMetaHeader(metaHeader);          
            xmlquery.setMaxRows(maxRows);
            xmlquery.setSkipRows(skipRows);
            // 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();
        } catch (Exception e) {
            throw (Exception)e.fillInStackTrace();
        } finally {
            if(xmlquery != null)
                xmlquery.close();
        }
    }
  }

import java.sql.*;
import java.math.*;
import java.util.*;
import oracle.jdbc.driver.OracleDriver;

public class ExistingCode {

  private Connection _conn;
  private Statement _pstmnt;
  
  public ExistingCode(){
  }

  public ResultSet ComplexFunction() throws SQLException {
    ResultSet rs = null;
    StringBuffer SbSql = new StringBuffer();
    String str = new String();
    try {        
        if(_conn == null)
          _conn = (new OracleDriver()).defaultConnection();  
  
          SbSql = new StringBuffer();
          SbSql.append(" SELECT *");
          SbSql.append(" FROM emp");
          _pstmnt= _conn.createStatement();
          rs = _pstmnt.executeQuery(SbSql.toString());
      }   catch (SQLException e) {
             throw (SQLException)e.fillInStackTrace();
      }
      return rs;
  }
}     

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.

Listing 3: MyPlsqlXML PL/SQL source code, returns CLOB from Java

create or replace package MyPlsqlXML is

  -- Public constant declarations
  lobDuration  NUMBER :=  DBMS_LOB.SESSION;

  PROCEDURE getXML(metaType IN NUMBER, xmlClob OUT CLOB);
  PROCEDURE getXMLData(metaType IN NUMBER, xmlClob IN CLOB);
  
end MyPlsqlXML;
/
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;
/

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

Listing 4: Web service consuming XML from a Java Stored Procedure

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Web;
using System.Web.Services;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Xml;
using System.Xml.Xsl;

namespace xmlSample
{
/// <summary>
/// EmpService examplifies returning XML
/// data from a Java Stored Procedures using
/// the Oracle data provider for .NET 
/// </summary>
public class EmpService : System.Web.Services.WebService
{
public EmpService()
{
InitializeComponent();
}

	#region Component Designer generated code

//Required by the Web Services Designer 
private IContainer components = null;

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if(disposing && components != null)
{
components.Dispose();
}
base.Dispose(disposing);
}

	#endregion

[WebMethod]
public XmlDocument getXMLData()
{
XmlDocument doc = new XmlDocument();
            // create and open a connnection to the Oracle
// database
string connstr = 
             "User Id=scott;Password=tiger;Data  Source=General";
OracleConnection conn = new OracleConnection(connstr);
conn.Open();

// Create a Command object from the OracleCommand
// class using the open connection
OracleCommand cmd = new OracleCommand("", conn);
// Set the PL/SQL Stored Procedure name
cmd.CommandText = "MyPlsqlXML.getXML";
cmd.CommandType = CommandType.StoredProcedure;
// Bind the input parameter
OracleParameter param1 = 
               cmd.Parameters.Add("SchemaCode",OracleDbType.Int32);
param1.Direction = ParameterDirection.Input;
param1.Value = 0;
// Bind the Oracle CLOB 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 OracleClob object
string lob_data = (string) 
               ((OracleClob)(cmd.Parameters[1].Value)).Value;
// Load the XML string into the document variable
doc.LoadXml(lob_data);

// Dispose OracleCommand object
cmd.Dispose();
// Close and Dispose OracleConnection object
conn.Close();
conn.Dispose();
return doc;
}
}
}

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.