Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Question Unanswered: St.Proc. - Using a variable for a table name

    I'm trying to write a Procedure takes takes as input the table name to be used in a cursor. This, at a basic level, is what I want to do:

    CREATE OR REPLACE PROCEDURE "TEST1" (table_name IN varchar)
    as
    cursor rows_cur1 is SELECT idno FROM table_name order by idno;
    ...etc...


    I've tried all sorts of different combinations of
    TYPE rows_cur1 IS REF CURSOR;
    with
    OPEN CURSOR_TYPE for execute_sql_statement;

    but nothing seems to work. Is this even possible? If so. how do you do it???

  2. #2
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    You can use a REF cursor, just build your query string first:

    Code:
    PROCEDURE test1 (table_name VARCHAR2 DEFAULT NULL)
    IS
       TYPE curvar_type IS REF CURSOR;
    
       curvar                        curvar_type;
       myquery                       VARCHAR2 (1000) := 'select * from ' || table_name;
    BEGIN
    
       OPEN curvar FOR myquery;
    
        WHILE TRUE LOOP
             FETCH curval INTO ...
             EXIT WHEN curvar%NOTFOUND;
              .....
         END LOOP;
    
         CLOSE curvar;
    END;
    This is working in our evironment...

    JoeB

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    Excellent!! Thanks very much. I'm not an expert so it also took me a few minutes to figure out how to get the

    rows1 table_name.column_name%TYPE;
    fetch v_refcursor into rows1;

    working. Then realized I can just do this:

    tempvar varchar(30);
    rows1 tempvar%TYPE;
    fetch v_refcursor into rows1;

    Thanks again. I can stop swearing at my monitor and PL/SQL book now.....

Posting Permissions

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