Re: Pass a query into a stored proc?

Thank you Sergey,

This does indeed work for me, including the print command :o)

Possibly, you were having problems because you missed off the : in the exec line??

thanks,

• Kev

sergey_s_at_my-deja.com wrote:

> 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 Mon Feb 28 2000 - 03:48:10 CST