Monday, September 01, 2008

Sending XML string as an input parameter to Oracle Stored Procedure from VB.NET

Sending XML string as an input parameter to Oracle Stored Procedure from VB.NET

Overview:

This document will guide you to insert or update multiple rows into the oracle stored database using VB.NET code. This is common requirement across any project when you have to insert or update multiple rows in one shot from the code. This is especially true when you are working on web application where you have a restriction not to hit database multiple time. Using this document you can create VB/C#.NET program to achieve this. This document will also gives sample Oracle Store procedure.

Introduction:

Passing XML string as an input parameter to Oracle Stored procedure is always need some research. This way we have to spend lots of time to really understand the architecture and flow. To save research and other POC development time, I am trying to give very simple way to describe how we can pass XML string as an input parameter to oracle stored procedure using VB.NET code. You can convert this code in C#.NET also.
Assume you have a requirement; to insert 10 rows into the database; how you do? Normally you execute stored procedure 10 times to insert 10 rows into the database table from the code. While doing this your application will hit the performance and slow down the process especially in case of web applications.
Here I am giving the best way to pass multiple rows into the database table from VB.NET; you have to call oracle stored procedure only one time. This method you can use for insert/update multiple rows/data into the database table.
Microsoft Application Block use "System.Data.OracleClient"for oracle stored procedure execution and this namespace not allow to use "OracleDbType.XmlType" datatype, that's requires for sending XML string as a input parameter into the oracle stored procedure.
You require "Oracle.DataAccess.Client" namespace to use "OracleDbType.XmlType" data type and pass XML value into the Oracle Stored procedure. Using this document you can modify application block using following lines of code.

VB.NET 2.0 Source Code


Name Space
Imports Oracle.DataAccess.Client
Imports System.Text
VB.NET Code
Dim oraCmd As New OracleCommand

Dim oraCon As New OracleConnection

Dim recordEffected As Integer

Dim XMLString As String


try

{


'Generate XML String, here I am taking 2 rows in the xml

'This XML can be taken from XML file or can be generated using .NET 'Serialization

XMLString.Append("<?xml version='1.0' encoding='utf-8' ?>")

XMLString.Append("<EmployeeData>")

XMLString.Append("<Root>")

XMLString.Append("<EmpName>Ritesh</EmpName>")

XMLString.Append("<EmpSal>10000</EmpSal>")

XMLString.Append("<DeptNo>1010</DeptNo>")

XMLString.Append("<JoinDate>03-Apr-2006</JoinDate>")

XMLString.Append("</Root>")

XMLString.Append("<Root>")

XMLString.Append("<EmpName>Rajiv</EmpName>")

XMLString.Append("<EmpSal>20000</EmpSal>")

XMLString.Append("<DeptNo>2020</DeptNo>")

XMLString.Append("<JoinDate>05-May-2007</JoinDate>")

XMLString.Append("</Root>")

XMLString.Append("</EmployeeData>")

'Create the connection String

oraCon.ConnectionString = "Data Source=DNSName;UserID=UID;Password=PWD;"

oraCon.Open()

oraCmd.CommandType = CommandType.StoredProcedure

oraCmd.CommandText = "INSERT_EMPLOYEE_DATA" ' Oracle SP Name

oraCmd.Connection = oraCon

oraCmd.Parameters.Add("ip_emp_details", OracleDbType.XmlType,

XMLString, ParameterDirection.Input) ' Oracle SP Input Parameter name

'Execute the Stored Procedure

recordEffected = oraCmd.ExecuteNonQuery()

catch

{

throw;

}

}

Finally

{

oraCon.Close()

oraCon.Dispose()

}
Oracle Stored Procedure

Oracle stored procedure will be different for different XML format,but the logic for facthing the XML node value will be the same.According to above XML format used the oracle stored procedure will be as following
Oracle Database Table
CREATE TABLE employeeDetails

(

EmpName VARCHAR2(50),

EmpSal INTEGER,

DeptNo INTEGER,

JoinDate DATE

)
Oracle Stored Procedure for Insert data
CREATE OR REPLACE PROCEDURE "INSERT_EMPLOYEE_DATA"

(

ip_emp_details IN XMLTYPE

)

IS


BEGIN


FOR i IN
(

SELECT XMLTYPE.EXTRACT (VALUE (a),

'/Root/EmpName/text()').getstringval() AS ipEmpName,

XMLTYPE.EXTRACT (VALUE (a),

'/Root/EmpSal/text()').getstringval() AS ipEmpSal,

XMLTYPE.EXTRACT (VALUE (a),

'/Root/DeptNo/text()').getstringval () AS ipDeptNo,

XMLTYPE.EXTRACT (VALUE (a),

'/Root/JoinDate/text()').getstringval () AS ipJoinDate

FROM TABLE

(XMLSEQUENCE (ip_emp_details.EXTRACT

('/EmployeeData/Root')

)

) a )


LOOP


INSERT INTO employeeDetails

(EmpName, EmpSal, DeptNo,JoinDate)

VALUES

(i.ipEmpName, i.ipEmpSal, i.ipDeptNo,i.ipJoinDate);


END LOOP;


END INSERT_EMPLOYEE_DATA;

/
Oracle Stored Procedure for Update data
CREATE OR REPLACE PROCEDURE "UPDATE_EMPLOYEE_DATA"

(

ip_emp_details IN XMLTYPE

)

IS


BEGIN


FOR i IN


(

SELECT XMLTYPE.EXTRACT (VALUE (a),

'/Root/EmpName/text()').getstringval() AS ipEmpName,

XMLTYPE.EXTRACT (VALUE (a),

'/Root/EmpSal/text()').getstringval() AS ipEmpSal,

XMLTYPE.EXTRACT (VALUE (a),

'/Root/DeptNo/text()').getstringval () AS ipDeptNo,

XMLTYPE.EXTRACT (VALUE (a),

'/Root/JoinDate/text()').getstringval () AS ipJoinDate

FROM TABLE

(XMLSEQUENCE (ip_emp_details.EXTRACT

('/EmployeeData/Root')

)

) a )


LOOP


UPDATE employeeDetails SET EmpName = i.ipEmpName,

EmpSal= i.ipEmpSal,

JoinDate = i.ipJoinDate

WHERE DeptNo = i.ipDeptNo;
END LOOP;

END UPDATE_EMPLOYEE_DATA;

/
SQL Server Database
If you are working with SQL Server as a Database and want to do same operation as above then see the following link
http://www.riteshkk2000.blogspot.com/2005/06/sent-multiple-data-to-storeprocedure.html

9 comments:

Anonymous said...

Cool Buddy.. In very simple way, you gave a good concept.. I needed this for a proof of concept

Many thanks

Sam

Anonymous said...

Hi Ritesh,

Thnx for solution for xml.

How to handle the spl, character like A&b if passing in xml and store in oracle table and it save like A&B
and shows but it should save like A&B.
Please provide some tips.

Thnx
Rajesh Kumar

Arun said...

Thanku very much

Chamako De Barrio said...

Thank u man ... u save my life ... i was looking an example like this.

Yairt said...

Hi
Realy good and helpfull solution, but still got a problem, I used your code, my sp looks like that:

create or replace PROCEDURE ABC
( FE_Param IN XMLType)
AS
BEGIN
FOR i IN
(
SELECT XMLTYPE.EXTRACT (VALUE (a),
'/Root/FE_ID/text()').getstringval() AS FE_ID
FROM TABLE
(XMLSEQUENCE (FE_Param.EXTRACT
('/FEData/Root') ) ) a )

LOOP
INSERT INTO table1
VALUES (i.FE_ID);
END LOOP;
END;
END ABC;

my xml file looks like that:



900000031
900000032
900000050



The problem is that instead of getting three seperate rows in the table
900000031
900000032
900000050

I recieved a single row that looks like that:
900000031900000032900000050

What am i doing wrong?
thanks, Yair

siva kumar said...

In C# am created the the required values using string builder and string and passed to db as xmltype.
But details are not saved and am getting error as "Oracle Type exception was caught.

Can you let me know the solution?

omprakash kurmi said...
This comment has been removed by the author.
omprakash kurmi said...

Thnx Sir,
Greate Work....

divs said...

Thanks for the artical sir .it is easy to understand and implement too :)

I am very new to .net and oracle development.yet understood it easily