Browse DevX
Sign up for e-mail newsletters from DevX


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

Oracle's XML toolkit (the XDK), provides the foundation and base classes that you can use to bridge the data gap between Java Stored Procedures and .NET.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date