I have a query
select * from myTable
...and I want to wrap this query inside a stored procedure, and have the store procedure output the results of this query.
How do I do it?
In ms-sql, i can store my query as a string to a string variable. And then do "Execute (variable)". Why no such thing in Oracle?
Use:
CREATE OR REPLACE PROCEDURE MY_SPROC() RETURN SYS_REFCURSOR
L_CURSOR SYS_REFCURSOR;
BEGIN
OPEN L_CURSOR FOR
SELECT * from MYTABLE;
RETURN L_CURSOR;
END;
Use this if you want to run dynamic SQL on Oracle:
CREATE OR REPLACE PROCEDURE MY_SPROC() RETURN SYS_REFCURSOR
L_CURSOR SYS_REFCURSOR;
L_QUERY VARCHAR2(5000) DEFAULT 'SELECT ...';
BEGIN
OPEN L_CURSOR FOR L_QUERY;
RETURN L_CURSOR;
END;
If you want to include bind variables in the dynamic SQL:
CREATE OR REPLACE PROCEDURE MY_SPROC() RETURN SYS_REFCURSOR
L_CURSOR SYS_REFCURSOR;
L_QUERY VARCHAR2(5000) DEFAULT 'SELECT ...';
BEGIN
OPEN L_CURSOR FOR L_QUERY
USING bind_var1;
RETURN L_CURSOR;
END;
You need to use a ref cursor.
Check out the odp documentation. It has a very good example, covering both the DB and the .Net code.
It comes with the installation of the oracle client, but it is hidden deep in the directory structure. Go to the -> odp -> doc -> .
Ref Cursors have been the standard way of doing this for years, but there is a slightly different alternative using pipelined table functions: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2345
They are fairly widely used in data warehousing applications and the execution can be parallelised so they're very high performance (not as good as just running a SELECT though).
