Best way to pass multiple records into Oracle Stored Procedure in one call from .NET


I have been looking to find optimal way to pass record set (which is collection of records to be inserted into an oracle table which containts about 100+ columns) as parameter to Oracle stored procedure. This way, I would not need to create parameter for each column and call the Stored Proc too many times. I am thinking to make use of some kind of data structure which can be interpretted in both Oracle and .NET sides,something similar to Table of Oracle Object type or Oracle Record(of type <Table>%RowType) . I did not find support for neither of these types by various Oracle data access technologies like .NET framework provider for Oracle, ODAC or ODP.NET etc. I would appreciate any help on this.





3/25/2009 3:01:03 AM 2269 articles. 0 followers. Follow

5 Replies

Similar Articles

[PageSpeed] 30

you can use array as parameter in oracle SP. Below link talks about show to do it from You can also do the same in c#



Please mark the answer if it helped you
3/25/2009 4:28:07 AM

You can achieve it using XML or array binds.

Please refer the below link.

Please mark it as answer if it resolves the issue




Please mark it as answer if it resolves the issue.

3/25/2009 5:00:27 AM

I appreciate your responses. ArrayBinding seems to be a good way to send parameters of different datatypes into the stored procedure in a single call. I will try it out.


3/25/2009 12:22:01 PM

 Hi Rajesh,

Please mark the post as answer which helped you to resolve the issue, so that others can refer it.

Please mark it as answer if it resolves the issue.

4/19/2009 5:42:03 PM



Please remember to click "Mark as Answer" on the post that helps you.
4/21/2009 2:17:56 PM

Similar Artilces:

is there any way to call oracle stored function, not stored procedure using .net?
I am just wondering if I can call oracle stored function instead of stored procedure.Thank in advance! you call a function just like s procedure.except you need to add a parameter for the return value.myparam ... ParameterDirection.ReturnValue;--dweezilSELECT * FROM users WHERE clue = 'yes';Records found: 0...

Passing a .NET collection to a Oracle Stored procedure?
In my ASP.NET page I have a list box.  For each row in the list box, I need to invoke an Oracle SP that inserts some data. How would I go about passing the contents of the list box collection to the Stored procedure so that I can loop through it?   Hi,   If you're using Oracle's provider, it allows you to pass PLSQL Associative Arrays, which would be the equivalent of passing an array of strings.    I don't believe Microsoft's ODP has that functionality though so if you're using it you'd probably need to concatenat...

Passing boolean values to oracle function / procedure from C#.Net
Hi,  I dont know how to pass boolean values to oracle function / procedure arguments, could you please help me to know ?  Thank you, Sreedhar After spending lot of time on google, I understand C#.Net doest support PL/SQL boolean values. Thanks Sreedhar...

Report based on Oracle Stored Procedure -- call from .NET
I've developed a report in Crystal Reports 10, and now want to add it to my Web Application. When I call the report, with the parameters, the problem I hit is it asked you to continually log into the Database itself. I'd like to set all the connection properties in .NET such that it seems seemless to the user. Robert, I've moved your post from the Crystal Reports forum to this Oracle forum. Your question is really about the connection to Oracle, so I think you're much more likely to receive help here in this forum.Alister I just now came across the following that may help you: Using...

Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...

Error while calling .Net web service from Oracle Stored Procedure
Hi,     We are getting the below error while calling an .net web service ( WCF) from Oracle Stored procedure.  Type: System.Exception, mscorlib, version =, culture = neutral,publickeytoken=b77a5c561934e089 Message: No client Registry found Data: System.Collections.ListDictionaryInternal Any information on the above will be helpful. Thanks. Sowmya Jayappa  ...

how to call an oracle stored function/procedure using ODBC.NET
Hi all, I have been trying to call an oracle stored funtion and cannot make it. Can anyone give me a clue. Thanks, Ted Code........ <%@ Page Language="VB" %> <%@ Import Namespace="System" %> <%@ Import Namespace="System.Web" %> <%@ Import Namespace="System.Web.UI" %> <%@ Import Namespace="System.Web.UI.WebControls" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Configuration" %> <%@ Import Namespace="Microsoft.Data...

Oracle store procedure problem using Powerbuilder and Oracle Net
Powerbuilder version 6.0.00 / 6.5 Build 444 Oracle Net I found there is problem with Oracle store procedure having more than 6 in parameters. I got GPF error on Window 95 and Dr. Watson error on NT. Also, I found it works find in Oracle Net Is this a bug? Is there any new patch for this if it is a bug. Any recommendation about What version Oracle Net or SQL Net should I used for Oracle server version (I don't think I am using any Oracle 8 new features)? Thanks in advance, Sharon Li If you use PB 6.5, you can try the new Oracle 8 driver: ftp...

urgent: .NET Provider for Oracle or Oracle Data Provider for .NET
If I want to access Oracle DB in ASP.NET application, and I am using .NET Provider for Oracle or Oracle Data Provider for .NET, should I have to install OracleClient on my .NET server? Is there other choice? Thank you very much.Johnson Johnson2007:should I have to install OracleClient on my .NET server?  You have to options1. install the ODP.NET on your web serverOR2. put the oracle assemblies in your BIN directory   Hi jimmy,Thank you for you reply. Because I could not find a server right now, I should test it first before I agree or disagree with you, as most articles I...

Oracle stored procedure with .net
Dear all I wrote a following stored procedure and program coding. ------------------------------------------------------------------------------------------ Stored procedure ------------------------------------------------------------------------------------------- create or replace procedure Delete_emp (p_empno  number) is begin     delete from emp      where empno = p_empno; end delete_emp; ---------------------------------------------------------------------------------------------- program coding ---------------------------------...

PLS-00221: is not a procedure or is undefined FUNCTIONS VS. STORED PROCEDURES
PLS-00221: is not a procedure or is undefined FUNCTIONS VS. STORED PROCEDURES - ADO.NET TO ORACLE FUNCTION   I have a very simple oracle function I'm trying to call from The code works fine for a stored procedure but I get the error  PLS-00221: is not a procedure or is undefined  when I try to call the function. here's my code:  I've manually tested both the function and stored procedure and they are both working as expected.  here's my code Protected Overrides Sub OnLoad(ByVal e As EventArgs) Dim cnn As OracleConnection Dim sConnSt... function and Oracle procedure to get an Oracle Dataset??
I have similar code (a procedure and function) working perfectly with a sql server data source. I;'m trying to do the same things with Oracle, but have run into the problems.    I'm not an oracle expert, so I'm not sure if producing a cursor is my only and best  option. create or replace package body PK_RETAILER isPROCEDURE sp_Get_Order_History(v_ani IN number,output_cursor out PK_CURSORES.generic_cursor) ASBEGINopen output_cursor forselect o.account_number,, o.amount,, o.init_datefrom orders ojoin users_phones p on p.account_number...

Passing Multiple rows into a stored procedure
I have a stored procedure which accepts a collection of records. How can I take a datawindow full of records (that match the general datatype of the collection) and, pass it to the stored procedure? Is there a way to do this? I've read numerous bits about getting data into datawindows using refcursors but, I can't find an example of this. Any help is appreciated. You can't pass a ref cursor in, at least not from PB. PB also doesn't support passing in an array of a record type. You would need to pass in an array of a simple datatype. Or store the data in the d...

Return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle
Hello, I am wondering if anyone in this community knows the shorter way to return an Oracle Ref Cursor to a .NET DataReader object by using the .NET Managed Provider for Oracle, something likereturn (IDataReader) SqlHelper.ExecuteReader(ConnectionString, "GET_ACCOUNT", Id); instead of using the way below (look at the C# code) Stored Procedure create or replace PROCEDURE "GET_ACCOUNT" ( p_ID IN ACCOUNT.ID%type, retCursor OUT SA.MYGEN.sqlcur ) IS BEGIN OPEN retCursor FOR SELECT ID, NAME FROM ACCOUNT WHERE ID = p_ID ; ...

Web resources about - Best way to pass multiple records into Oracle Stored Procedure in one call from .NET -

Invasiveness of surgical procedures - Wikipedia, the free encyclopedia
There are three main categories which describe the invasiveness of surgical procedures . These are: non-invasive procedures , minimally invasive ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Rescued tiger cub "Himmel" dies during medical procedure - CBS News 8 - San Diego, CA News Station ...
Alpine animal rescue sanctuary Lions, Tigers & Bears is sadly reporting Himmel, the tiger cub found roaming the streets of Hemet and rescued ...

Mix-up leads to surgical procedure on wrong baby
Tennessee mom says her newborn was mistakenly given treatment he didn't need

New Chipotle Food Safety Procedures Include Shutting Down Restaurant If Anyone Barfs
... what happened inside, but it’s pretty much exactly what you would expect: repetitive instructional videos about food safety and cleaning procedures, ...

Resources last updated: 2/11/2016 5:23:31 PM