Looking for some help with stored procedure call issues. Conceptually, I
need to pass a data structure as the sole parameter to the Oracle stored
procedure. Sounds simple enough....but how?
First, looking at this from the stored procedure side, I see a few things
the procedure can do:
1) receive a CLOB/BLOB
2) receive a user defined data type
3) receive multiple params, one for each distinct data element
Looking at this from the C# app side, I would be making the stored procedure
call with parameterized SQL. The goal here would be to marry the options for
how the oracle stored procedure can receive the params with my OleDb Provider
ability to make the call.
The obvious solution I see is to make the stored proc call with a string
containing formatted XML (via XmlTextWriter features of fw2.0), and receive
it in the stored proc as a CLOB.
In the proc, I would parse the XML, save the element data content in local
vars, and go about my INSERT/UPDATE/DELETE business as needed.
HOWEVER...
In our Oracle environment, we do not have the Oracle XDK (XML Dev Kit)
installed, so the procedure cannot actually do the xml parsing, etc, etc...
without it. I know, I know...install the XDK, problem solved. But that is
a policy matter that will not be resolved in a timely manner (by my standard).
I am trying really hard to avoid sending distinct params for each data
element, especially since I have not disclosed to you if I have 1-10 data
elements, or 100-1000 data elements that would need to be passed.
All this leaves me with a user-defined data type on the oracle side as the
last way to receive a parameter into the stored procedure. So now the
question....
How, if at all, can I do a stored proc call from my C# app, sending "data"
into a user-defined data type on the oracle side????
Or...is there another option I do not see????
Any help is appreciated here.
Thanks in advance....Tom
