Re: Pass a query into a stored proc?

Kev,

The following compiled successfully for me:

create or replace procedure retcurs_proc (my_sql in varchar2, mycurs out types.cursorType) is
begin
open mycurs for my_sql;

end retcurs_proc;
/

What I was also trying to do was to show the proc in action:

SQL>var temp_refcurs refcursor;
SQL>exec retcurs_proc('select * from emp', temp_refcurs);
SQL>

That part also worked successfully. But when I actually tried to show the result set, I got a weird error (that has to do with something else I think):

SQL> print :temp_refc
sp2-0625: Error printing variable "temp_refcurs" SQL> Anyway, the print command should have printed the entire result set to the screen. So, try that. I think it should work. Post here about your findings.

Good lick!

Sergey

In article <38B6B595.B4E3CDDB_at_fast.no>,   kev <kevin.porter_at_fast.no> wrote:
> Hi,
>
> I want to be able to pass a select query into a procedure, open a
cursor
> for that query then return the cursor.
>
> So far, I have managed to make a function that opens a cursor for a
> hardcoded query, but is it possible to pass in a varchar2 which holds
> the query to execute? How's it done?
>
> Here's the simple proc I have at the moment:
>
> create or replace package types
> as
> type cursorType is ref cursor;
> end;
> /
>
> create or replace procedure retcurs_proc
> (mycurs out types.cursorType)
> is
> begin
> open mycurs for select * from emp;
>
> end retcurs_proc;
> /
>
> How could I change the above proc to be able to pass in a query?
>
> thanks,
>
> - Kev
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Feb 26 2000 - 15:21:29 CST