Can Oracle Ref Cursors only be used with Procedures and Functions, or can they also be used with PL/SQL issued via an ODP.NET OracleCommand object (contained as a string in the CommandText property)?
1 Answer
It depends on what you are trying to accomplish.
You can certainly execute an anonymous PL/SQL block from a .Net or a Java application that internally makes use of a REF CURSOR. Since anonymous PL/SQL blocks do not accept or return parameters, however, you cannot return a REF CURSOR that you open in an anonymous PL/SQL block to the calling application.
From an application architecture standpoint, I would strongly advocate that if you need to do processing that requires PL/SQL, that processing ought to be incorporated into a named PL/SQL block (a procedure or a function) that is in a package and stored in the database. Having logic implemented in PL/SQL that is stored outside the database is generally a bad idea.
4 Comments
Paul
I thought the anonymous PL/SQL could return data via output bind variables/parameters in ODP.NET, is that not the case with Ref Cursors? I can appreciate that it is a bad idea to be placed outside the database, unfortunately that decision is out of my hands.
Justin Cave
@Paul - I don't believe that is possible with a
REF CURSOR. Obviously, I'd welcome someone pointing out a gap in my knowledge.Paul
It would seem it's possible with a stored function (e.g. [stackoverflow.com/questions/6360244/…), but as far as I can determine, you are correct, it is not possible with anonymous PL/SQL.
Paul
Good news for anyone who reads this later. In a related question, @Juergen-Hartelt just showed the way to use a Ref Cursor as an output bind variable/parameter with an anonymous PL/SQL command used via C# with ODP.NET (stackoverflow.com/questions/11267746/…).
begin ... end;