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?

Pls. try scenario from this article http://stackoverflow.com/questions/4098944/oracle-stored-procedure-and-custom-data-type — Mihal By
– Mihal By, Commented Dec 2, 2016 at 20:47

Andrew Bezzub · Accepted Answer · 2010-03-18 09:49:54Z

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;

answered Mar 18, 2010 at 9:49

Andrew Bezzub

16.1k7 gold badges54 silver badges73 bronze badges

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

Comments

lexu · Accepted Answer · 2010-03-18 09:57:25Z

0

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

answered Mar 18, 2010 at 9:57

lexu

8,8495 gold badges48 silver badges63 bronze badges

1 Comment

devdimi Over a year ago

The problem is that the updates are dependent on the select, so to separate them cleanly I need multiple DB calls in very high traffic scenario.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related