Calling Oracle Function with "complex" return value from C#

Question

I have an Oracle function returning record defined in the package, so one can do:

select a,b,c FROM my_function(...);

Calling this oracle function from .NET is as simple as executing normal sql query.

Unfortunately the function has to do updates now and when it is called like this Oracle complains that updates are not allowed within selects and that makes sense. So now I am left with the choice to change the function call or to split the function. Basically I have to get rid of the select in the function call and need something like this in C#:

EXEC :var:= my_func(...);

where the type of var is custom tuple defined in the package. I have already tried using ParameterDirection.ReturnValue without success. Does anyone have an idea?

Answer 1

You can execute first your function and then select statement in one batch. Smth like this:

begin

EXEC var:=my_func(...);

select var.a, var.b, var.c ...;

end;

Answer 2

As Andrew says, you can (should!) split your function into the two distinct (ahem) functions it performs:

update data
return data

'Hidden magic' in stored procedures/functions should be avoided where ever possible.
This improves readability and maintainability