| How to return data structure from stored procedure [message #460629] |
Sun, 13 June 2010 10:12  |
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 #460967 is a reply to message #460629] |
Tue, 15 June 2010 14:59  |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
