Run a query inside an Oracle Stored Procedure

Question

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?

Answer 1

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

Answer 2

Check this link out. It has code samples on how to do what you are trying to in MSSQLServer and Oracle10g.

Answer 3

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;

Answer 4

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%5F01/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).