Home » Infrastructure » Windows » How to return data structure from stored procedure (server: Oracle 11g on Red Hat 4 client: Windows 7 C#.NET)
How to return data structure from stored procedure [message #460629] Sun, 13 June 2010 10:12 Go to next message
rodnower
Messages: 17
Registered: June 2010
Location: Israel
Junior Member
Hello, I have C# application that retrieve data from AQ with some oracle stored procedure, that stored in package. The scheme is:
C# code -> Stored Procedure in Package -> AQ

Inside of this stored procedure I use DBMS_AQ for dequeue the data to some object of some type.
Now I have this object. My question is how I return it?
Previously I:

1. Created some virtual table,
2. Make EXTEND() to table
3. Inserted the data from object to table,
4. Perform select on the table,
5. And return sys_refcursor.

In side of C# I filled DataSet with help of OracleDataAdapter.Fill()

After that I upgraded it to return data fields during OUT parameters.
But now I have much fields, and I may not to create so much OUT parameters...

What the best way to do this?
Thank you for ahead.
Re: How to return data structure from stored procedure [message #460630 is a reply to message #460629] Sun, 13 June 2010 10:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Create object type to return each current parameter as an attribute of this object.
But I don't think it is easier to handle one object type with N attributes than N out parameter.

Regards
Michel
Re: How to return data structure from stored procedure [message #460670 is a reply to message #460630] Mon, 14 June 2010 01:23 Go to previous messageGo to next message
rodnower
Messages: 17
Registered: June 2010
Location: Israel
Junior Member
I yet have object that I get from DBMS_AQ.Dequeue(), but do you know how I get it in side of C#? I may not use ExecuteReader()...
Re: How to return data structure from stored procedure [message #460674 is a reply to message #460670] Mon, 14 June 2010 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No I don't know C#.
I move the question to the appropriate forum.

Regards
Michel
Re: How to return data structure from stored procedure [message #460966 is a reply to message #460674] Tue, 15 June 2010 14:49 Go to previous messageGo to next message
rodnower
Messages: 17
Registered: June 2010
Location: Israel
Junior Member
Thank you for moving my thread to right forum
Re: How to return data structure from stored procedure [message #460967 is a reply to message #460629] Tue, 15 June 2010 14:59 Go to previous message
rodnower
Messages: 17
Registered: June 2010
Location: Israel
Junior Member
So I want bit to clarify some things:
In second way I perform dequeue() from AQ to instance of my payload's type. Lets call this variable: msg, and return instance's fields during out parameters to ODP.
My type looks like:

create or replace type msg_t (
  id        int,
  recipient varchar2(20)
)

The header of my procedure looks like this:

procedure cmn_msg_sel(
   id        out int,
   recipient out varchar2
) 

In the body of procedure I do:

id := msg.id;
recipient := msg.recipient;


In C# side I use something like: OracleCommand and OraclePrameter.ParameterType = ParameterTypes.Out for retrieve values from AQ.

So my question is: may I get some data structure from stored procedure not in previous two ways?

[Updated on: Tue, 15 June 2010 15:02]

Report message to a moderator

Previous Topic: Load Data into Oracle DataBase from Excel in a single hit.
Next Topic: Oracle 11g: established two database(d1, d2) but not able to connect the second database(d2).
Goto Forum:
  


Current Time: Thu Mar 28 14:56:26 CDT 2024