Results 1 to 3 of 3

Thread: pl/sql cursor

  1. #1
    Join Date
    Nov 2004
    Posts
    35

    Unanswered: pl/sql cursor

    i had 2 cursor c1 and c2 which is some select statement.
    how is it possibe tat for eg A is true select using c1 then when B is true select using c2? after which both run some same code

    i can't do if else loop befor the begin and i cannot do like:
    if A
    open c1
    run code
    if B
    open c2
    run code

    in that way, the code will be duplicated and will slow down my whole project.

    thanks any expert who can help....

  2. #2
    Join Date
    Nov 2004
    Posts
    3
    you can use dynamic SQL in this case with the use of Oracle supplied package dbms_sql. Which parse the query at run time. e.g.

    declare
    c number;
    d number;
    sqlstring varchar2(2000);
    begin
    c:=dbms_sql.open_cursor;

    if A=true then
    sqlstring:='select ename from emp';
    else
    sqlstring:='select dname from dept';
    end if;

    dbms_sql.parse(c,sqlstring,dbms_sql);
    d:=dbms_sql.execute(c);

    loop
    .... fetch data and other manipulation.....
    .... exit when <condition>;
    end loop;

    dbms_sql.close_cursor(c);

    exception
    ..... exception handling.....
    end;

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Or using a cursor variable and static SQL:
    Code:
    declare
      l_refcur sys_refcursor;
      l_name varchar2(30);
    begin
      if <condition> then
        open l_refcur for select ename from emp;
      else
        open l_refcur for select dname from dept;
      end if;
      -- Common code:
      loop
        fetch l_refcur into l_name;
        exit when l_refcur%notfound;
        dbms_output.put_line(l_name);
      end loop;
      close l_refcur;
    end;

Posting Permissions

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