Dynamic SQL/DBMS_SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Dynamic SQL/DBMS_SQL

  1. #1
    Join Date
    Jan 2001
    Posts
    3

    Unhappy

    I need help. I'm very new to PL/SQL and Oracle. Mainly transact SQL up to about a week ago. I need to get some information on using one of the above in the following manner:

    I need to set column values on a schema.table to be named as a variable in a procedure. I realize that I need to create a cursor to look through the table and find the rows that need updating and setting the proper column flags. What I cannot figure out so far is how to pass the table name as a variable to the declare statement of the cursor. If anyone has info, please pass it along, I have chapters from a couple of books, but it is not making much sense. I could really use some examples.

    Thanks from a newbie.

  2. #2
    Join Date
    Jul 2000
    Posts
    296
    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;

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