CODEWITHSUNDEEP
sqloracle-databaseplsql
Saobi
Saobi

Reputation: 17041

Run a query inside an Oracle Stored Procedure

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?

Upvotes: 2

Views: 8864

Answers (3)

David Aldridge
David Aldridge

Reputation: 52376

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

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332731

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;

Upvotes: 6

Vitaliy
Vitaliy

Reputation: 8206

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

Upvotes: 0

Related Questions