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)?
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.