Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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

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.




Full Text Search: The Key to Better Natural Language Queries for NoSQL in Node.js

Date: 1/31/2018 @ 2 p.m. ET

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


.NET Framework Datatype


OracleString class structure



OracleDate structure



OracleString class structure






OracleString class structure


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.

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