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

Thread: DBMS_SQL

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    hi,
    I am trying to pass the table name dynamicaly to the query and want to fetch the records.
    I went through the forum database and got this solution
    (((
    You cannot use tablenames as variables in cursors. In 8.1.6 you can use cursor variables with dynamic select statements.

    declare
    type refcur_ty is ref cursor;

    refcur refcur_ty;
    emprec emp%rowtype;

    tablename varchar2(30);
    begin
    tablename := 'EMP';
    open refcur for 'select * from '||tablename;
    fetch refcur into emprec;
    close refcur;
    dbms_output.put_line(emprec.ename);
    end;
    )))
    This solution is working but outputs only the first record, the other records are not display, could you help me.
    Badrinath


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    You have to get all the records in the cursor via a loop:

    open refcur for 'select * from '||tablename;
    LOOP
    fetch refcur into emprec;
    EXIT WHEN refcur%NOTFOUND;
    dbms_output.put_line(emprec.ename);
    END LOOP;
    close refcur;
    end;
    Jeff Hunter

  3. #3
    Join Date
    Jan 2001
    Posts
    642
    Thanxs,
    It's working now
    Badrinath

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