Retreiving TABLE as out FUNCTION arg

  • Thread starter Thread starter piotr p. via .NET 247
  • Start date Start date
P

piotr p. via .NET 247

Hi all, I need to call oracle FUNCTION from C# (ODP.NET) which has the TABLE as the out argument.
My_Table which I have to retreive looks like that:

TYPE MY_RECORD IS RECORD (ID NUMBER(10),DESCRIPTION VARCHAR2(100));
TYPE MY_TABLE IS TABLE OF MY_RECORD INDEX BY BINARY_INTEGER;

FUNCTION TEST(OUT_TABLE OUT MY_TABLE) RETURN NUMBER;

I don't know how to set myCommand.CommandText=? and which OracleType I should to use?
I tried "SELECT * FROM TABLE (TEST(table))" but I had errors (probably invalid argument).
I have to use function (not stored proc.)
Plz help or give me links connected to that.
Tnx in advance.
Peter
 
piotr p. via .NET 247 said:
Hi all, I need to call oracle FUNCTION from C# (ODP.NET) which has the TABLE as the out argument.
My_Table which I have to retreive looks like that:

TYPE MY_RECORD IS RECORD (ID NUMBER(10),DESCRIPTION VARCHAR2(100));
TYPE MY_TABLE IS TABLE OF MY_RECORD INDEX BY BINARY_INTEGER;

FUNCTION TEST(OUT_TABLE OUT MY_TABLE) RETURN NUMBER;

I don't know how to set myCommand.CommandText=? and which OracleType I should to use?
I tried "SELECT * FROM TABLE (TEST(table))" but I had errors (probably invalid argument).
I have to use function (not stored proc.)
Click to expand...


ODP only supports PL/SQL tables of scalar types. Not tables of records or
objects, and it supports REF CURSOR's.

You can access a function with a signature like

TYPE NUMBER_TABLE IS TABLE OF NUMBER
TYPE STRING_TABLE IS TABLE OF VARCHAR2

FUNCTION TEST(OUT_TABLE OUT sys_refcursor) RETURN NUMBER
FUNCTION TEST(IDS OUT NUMBER_TABLE, DESCRIPTIONS OUT STRING_TABLE) RETURN
NUMBER

David
 
Back
Top