Tuesday, July 29, 2014
48Hrs Help PAQ's
MS Alerts
Write to Us

a) What are .NET My Services?
b) Why use .NET My Services?

Get This Blog via Email:

Email This Feed Using Squeet

Making best out of Oracle batabases using ODP.net - Part 3

By Srini S Rajan
September 06, 2004
Page is Viewed 11120 times

Srini S Rajan

Please visit my earlier article Making Best out of Oracle Databases Using ODP.net – Part I for an introduction and to get started with ODP.net which is part of ADO.NET. This time I am continuing with other features.

Oracle Speaks XML – Native XML

We have seen XML view of the relational data using part 1 of this series, also from Oracle 9i Release 2, Oracle database supports XML natively through Oracle XML DB. As part of this XML data is stored natively in the database server as the Oracle database native type XMLType. ODP.net provides OracleXmlType class to retrieve Oracle native XMLType data.

To setup an example, let us build a new design to store all the employee data as a full XML document using XMLType. Please note that this is not a recommendation, native XML inside the database is double edged sword while it has its own advantages there are lots of issues about reporting, DML etc needs to be taken care. This design is done only for illustration purposes.

CREATE TABLE employees
empinfo XMLTYPE

Now having built a XMLTYPE table, we will convert all our relation employee data stored in emp table using ODP.net XML features.

The following code piece will convert the relational data using OracleXmlCommandType and will insert the xml document created as OracleDbType.XmlType. First part of the code deals with creation of xml document from relational data and the second part of the code deals with loading xml document as native XML inside database.

OracleCommand cmd = new OracleCommand("", con);
cmd.CommandText = "SELECT * FROM emp";
cmd.XmlCommandType = OracleXmlCommandType.Query;
cmd.XmlQueryProperties.RootTag = "EMPLOYEE";
cmd.XmlQueryProperties.RowTag = "EMP";
XmlReader dr = cmd.ExecuteXmlReader();
XmlDocument doc = new XmlDocument();
doc.PreserveWhitespace = true;

OracleCommand orainscmd = new OracleCommand("INSERT INTO employees(empinfo) " +
" VALUES (:empinfoxml)", con);
orainscmd.Parameters.Add(":empinfoxml", OracleDbType.XmlType);
OracleXmlType xmlinfo = new OracleXmlType(con,doc);

Having loaded all our employee data as a native XML inside database now let us retrieve the native xml type using ODP.net, we will also perform XPath operations on the document. Explaining the XPATH language is outside the scope of this article, but it conveys the power of native XML data type in performing XML operations.

Now look at the code.

OracleCommand cmd = new OracleCommand("SELECT empinfo FROM employees",con);
OracleDataReader dr = cmd.ExecuteReader();
OracleXmlType empinfoxml = null;
while (dr.Read())
empinfoxml = dr.GetOracleXmlType(0);

` }
OracleXmlType fragment = empinfoxml.Extract("/EMPLOYEE/EMP[1]"," ");
XmlDocument xdoc = fragment.GetXmlDocument();

The following output will be produced.


A fragment of the whole XML document as specified by the XPATH expression /EMPLOYEE/EMP[1] is returned in the above case.

Interoperability At Work

If you are part of any serious enterprise development system, by this time you would have come across interoperability between .net and J2EE/Java applications. Web Services has been an official answer from the leading vendors towards this solution. But a XML/SOAP based approach will not fit the performance and sclability needs of every system. Also additional setup regarding web services container, security etc needs to be taken care.

Third party bridges like IIOP.net, Janeva, JNBridge have been in market for some time and they use the pluggable nature of .net remoting especially from channel point of view. Again discussion on these technologies will go beyond the scope of this article and there are lot of material available on the web on this account. I personally find .net remoting is very interesting technology especially in terms of deployment perspective, which gives you an array of deployment choices like Windows Services, IIS, Console Applications etc, which also includes XML configuration files.

However not many from the .net world knows that Oracle Database Servers also contain an inbuilt JVM which can provide a custom platform for integrating Java and .net applications using ODP.net. Let us see some simple examples of integrating Java and .net applications using the JVM inside oracle databases. Some of the typical issues in integration like Security, Mapping of data types and even portability of code etc are taken care by oracle instead of third party bridges.

Let us look into a sample Java class which returns the list of run time properties as a string to the calling application. We will invoke this Java class from an ODP.net application to demonstrate interoperability.

package com.dotnet.meets.java;
import java.util.*;

public class GetProperties {

public static String get() {
Enumeration keys = System.getProperties().keys();;
String retString = null;
while (keys.hasMoreElements()) {
String propname = keys.nextElement().toString();
String propvalue = System.getProperty(propname);
retString = retString + propname + " " + propvalue + "\n";
return retString;

For people with C# knowledge the above Java class is no magic as it gets the System’s run time properties and returns a string which concatenates all the properties returned as an Enumeration. This Java class needs to compiled and the class file will be loaded inside oracle JVM using loadjava utility.

For users with sql server 2000 background, similar functionality of built-in CLR has been introduced in Yukon (SQL 2005) and I hope to cover this feature in future articles.

Now this java class needs to wrapped as stored function so that ODP.net clients can invoke them. The following will explain the data type mappings between .net and Java applications.

NAME 'com.dotnet.meets.java.GetProperties.get() return String';

OracleDbType.Varchar2 -> VARCHAR2 -> java.lang.String
ODP.net Oracle DB JVM(Java)

Oracle database clearly acts as a bridge between .net and java environments. Let us now look at the ODP.net client in invoking this java class which is wrapped as a database function.

The corresponding C# client to pass an array will be as follows.

OracleCommand cmd = new OracleCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.Add("props", OracleDbType.Varchar2,4000, DBNull.Value, ParameterDirection.ReturnValue);

The partial output from the above application is as follows.

java.vm.version 1.4.1
oracle.aurora.ncomp.lib.component.prefix jox10
java.vm.vendor Oracle Corporation
java.vendor.url http://www.oracle.com/java/
path.separator ;
java.vm.name JServer VM
file.encoding.pkg sun.io
user.dir C:\ORACLE\PRODUCT\10.1.0\DOTNET\
java.vm.specification.name Java Virtual Machine Specification
java.awt.graphicsenv oracle.aurora.awt.OracleGraphicsEnvironment
os.arch x86
java.io.tmpdir C:\WINDOWS\TEMP\

It is amazing to see that your calling .net application able to get the environment properties of a Java class running in a different environment without much investment and setup in bridging solutions. As mentioned earlier this is not a final solution, but can be used for some quick fix custom integration solutions.


With this I complete my three part series on features on ODP.net. Please note that this is mere technical capabilities of the product and your decision to use all or part of these features should be based on your portability, licensing requirements. But use of these advanced features will improve the performance and scalability of your applications connecting to Oracle databases. Click here to check other articles on ADO.NET.
The .NET Framework is a new computing platform that simplifies application development in the highly distributed environment of the Internet.
The suite of data access technologies included in the .NET Framework class libraries that provide access to relational data and XML. ADO.NET consists of classes that make up the DataSet (such as tables, rows, columns, relations, and so on), .NET Framework data providers, and custom type definitions (such as SqlTypes for SQL Server).
A new programming language designed for building enterprise applications that run on the .NET Framework. C#, which is an evolution of C and C++, is type safe and object oriented. Because it is compiled as managed code, it benefits from the services of the common language runtime, such as language interoperability, security, and garbage collection.
A reference type that encapsulates data (constants and fields) and behavior (methods, properties, indexers, events, operators, instance constructors, static constructors, and destructors), and can contain nested types. Class types support inheritance, a mechanism whereby a derived class can extend and specialize a base class. See also: encapsulation, indexer, property, reference type.
SQL Server:
Microsoft SQL Server is a set of components that work together to meet the data storage and analysis needs of the largest Web sites and enterprise data processing systems.
SQL Server:
Microsoft SQL Server is a set of components that work together to meet the data storage and analysis needs of the largest Web sites and enterprise data processing systems.
.NET Force is optimised for Microsoft Internet Explorer 5 browsers.
Copyright © 2004 .NET Force.
Terms and Condition. All rights reserved.