Re: Dynamic cursor definition

From: Jusung Yang <jusungyang_at_yahoo.com>
Date: 14 Aug 2002 10:14:50 -0700
Message-ID: <42ffa8fa.0208140914.79091e6c@posting.google.com>

You may be going about it the wrong way. Many ways you can avoid this code repetition, which I think is your concern. You can pass the "order by" caluse the the procedure and build the SQL dynamically with EXECUTE IMMEDIATE, or you can simply pass a refcursor to the procedure like this:

SQL> declare
  2 ref1 SYS_REFCURSOR;
  3 recvar test2%rowtype;
  4 procedure proc1 (refin in out SYS_REFCURSOR) is   5 begin
  6 loop

  7      fetch refin into recvar;
  8      exit when refin%NOTFOUND;
  9      dbms_output.put_line('c1 := '||recvar.c1||' , c2:=

'||recvar.c2 );
10 end loop;
11 end;
12
13 begin
14 open ref1 for select * from test2 order by c1; 15 proc1 (ref1);
16 close ref1;
17 dbms_output.put_line('---------------'); 18 open ref1 for select * from test2 order by c2; 19 proc1 (ref1);
20 close ref1;
21
22 end;
23 /

c1 := a , c2:= 1
c1 := a , c2:= 4
c1 := a , c2:= 6
c1 := a , c2:= 5
c1 := a , c2:= 7
c1 := a , c2:= 7
c1 := a , c2:= 6
c1 := b , c2:= 2
c1 := c , c2:= 3
c1 := e , c2:= 1

c1 := a , c2:= 1
c1 := e , c2:= 1
c1 := b , c2:= 2
c1 := c , c2:= 3
c1 := a , c2:= 4
c1 := a , c2:= 5
c1 := a , c2:= 6
c1 := a , c2:= 6
c1 := a , c2:= 7
c1 := a , c2:= 7

PL/SQL procedure successfully completed.

SQL> "Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<ajcupb$1al1ba$1_at_ID-152732.news.dfncis.de>...
> Hello,
>
> I would like to ask if there is a way to declare a cursor dynamically. In my
> case I want to have a dynamic ORDER BY
> clause at the end of a Cursor - the entire rest of the cursor and the
> procedure should not be altered.
>
> As far as I did not know how to do it, I made 2 prodecures which only
> difference is the ORDER BY in the cursor.
> But this is a bad design of course, I would appreciate Your help.
>
> ThanX in advance,
>
> Jan Gelbrich
Received on Wed Aug 14 2002 - 12:14:50 CDT