Calling an Oracle function with return type PL/SQL RECORD

[Taavi Kuusik]

Is there any data provider that allows me to call an Oracle stored
function that returns a record?

The function is defined like this:
FUNCTION some_function_name(some_number NUMBER) RETURN
some_user_defined_type
IS
.....
RETURN p_some_user_defined_type;
END;


Taavi Kuusik

 

[Sahil Malik [MVP]]

ExecuteNonQuery will do this work for you for scalar return values.
For resultsets you have to use ref cursors.

HTH,

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync

 

[Taavi Kuusik]

The function returns a pl/sql record, not a scalar or a ref cursor. And
I have no way to change the function myself since it's in a customer
database. So am I correct to say there is no way for me to call that
function from .Net and get it's returned data?


Taavi Kuusik

 

[Sahil Malik [MVP]]

I see !! it'd help if I read the subject of your post .. hehe

Well, in this case what you will have to do is, create an anonymous PL/SQL
block string and use simple oracle syntax to create a parameterized command
that passes in a pl/sql record to the underlying oracle database.

Now once you have specified that anonymous pl/sql block, set as the command
text, add parameters ( remember to set proper parameterdirection).

Command type should be text.

Execute - Bingo - it works !!!

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
----------------------------------------------------------------------------
---------------

The function returns a pl/sql record, not a scalar or a ref cursor. And
I have no way to change the function myself since it's in a customer
database. So am I correct to say there is no way for me to call that
function from .Net and get it's returned data?


Taavi Kuusik

ExecuteNonQuery will do this work for you for scalar return values.
For resultsets you have to use ref cursors.

HTH,

- Sahil Malik [MVP]
Upcoming ADO.NET 2.0 book - http://tinyurl.com/9bync
--------------------------------------------------------------------------

--