c# returning table from pl\sql function

Question

So, i have a function (that i can't change), that returns table of objects, and i need to get them in c# code.

create or replace FUNCTION choose_name( )

  RETURN  TBL_names IS
      result TBL_names:=TBL_names();

   /*
CREATE OR REPLACE TYPE OBJ_names as object (
    "Param1" varchar2(555),
    "Param2" NUMBER
    )
    CREATE OR REPLACE TYPE TBL_names is table of OBJ_names
    */

And here is a c# code for this:

command.CommandType = CommandType.StoredProcedure;
command.CommandText = "choose_name";
command.Parameters.Add("TBL_names", OracleDbType.RefCursor).Direction = ParameterDirection.ReturnValue;

But i 'm getting an error - wrong types of arguments. I understand that it is a table, not a ref cursor. How am I suppose to accomplish that?

btw it's best to leave type attribute names case-insensitive by defining them without double-quotes. Once you name something "Param1" (in double-quotes) then everyone who ever refers to it, ever, has to use "Param1", quotes and all. — William Robertson
– William Robertson, Commented May 22, 2017 at 14:06

Patrick Hofman · Accepted Answer · 2017-05-22 13:17:05Z

2

The easiest way in my opinion to query table functions, is by treating them like regular SQL:

select * from table(choose_name)

That will yield rows like you are used to when using ExecuteReader.

answered May 22, 2017 at 13:17

Patrick Hofman

158k23 gold badges270 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eve Over a year ago

I dont like this idea, it's better for me to turn the tableinto a refcursor and read it . but i didnt manage to do it right way

Patrick Hofman Over a year ago

You are free to do that, but my 2 cents are that the ADO.NET provider from Oracle isn't that good, so I always like to keep it as straightforward as possible.

Collectives™ on Stack Overflow

c# returning table from pl\sql function

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related