DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: A Dynamically Named Cursor

  1. #1
    Join Date
    Oct 2001
    Posts
    22
    Okay folks, here's the situation:
    I have a script that based on a certain criteria (gotten at runtime) will perform many operations on one of 2 cursors. Now, the operations to be done will be the same either way.
    So instead of:

    cursor x is ....
    cursor y is ....

    Begin
    if flag = 0 then
    for i in x loop
    ...
    ...
    ...
    end loop;

    elsif flag = 1 then
    for j in y loop
    ...
    ...
    ...
    end loop;
    end if;

    I want to do just:

    cursor x is ....
    cursor y is ....
    cursor_name

    Begin
    if flag = 0 then
    cursor_name := 'x';
    elsif flag = 1 then
    cursor_name := 'y';
    end if;

    for i in cursor_name loop
    ...
    ...
    ...
    end loop;
    End;

    Now, I tried this and it didnt work, is there anyway to make it work?
    (I dont want to create a procedure for what is inside the for loops, i just want to know if cursor_name can be dynamic like that)
    thanx in advance
    Peace,
    Sakitah




  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi

    u can do it using a dynamic pl/sql block. for example,

    declare
    cursor c1 is
    select count(*) cnt from emp;
    cursor c2 is
    select count(*) cnt from dept;
    j number:=1;
    x varchar2(200);
    plsql varchar2(200);
    begin
    if j=0 then
    x:='c1';
    else
    x:='c2';
    end if;
    plsql := 'declare
    cursor c1 is
    select count(*) cnt from emp;
    cursor c2 is
    select count(*) cnt from dept;
    begin
    for i in ' || x || chr(10) ||
    'loop
    dbms_output.put_line(''cnt gen. >>> ''||i.cnt);
    end loop;
    end;';
    execute immediate plsql;
    end;
    /
    Cheers!
    OraKid.

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi ,
    sorry forgot to remove the DECLARE CURSOR stmt @ declaration part.

    declare
    j number:=0;
    x varchar2(200);
    plsql varchar2(200);
    begin
    if j=0 then
    x:='c1';
    else
    x:='c2';
    end if;
    plsql := 'declare
    cursor c1 is
    select count(*) cnt from emp;
    cursor c2 is
    select count(*) cnt from dept;
    begin
    for i in ' || x || chr(10) ||
    'loop
    dbms_output.put_line(''cnt gen. >>> ''||i.cnt);
    end loop;
    end;';
    execute immediate plsql;
    end;
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width