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

Thread: Reference a table in a script

  1. #1
    Join Date
    Jul 2000
    Location
    EROS Data Center, South Dakota
    Posts
    2

    Question

    List,

    I am trying to write a script that displays the contents of the Comments column for a database dictionary. However, for columns that reference, through a foreign key, a lookup table I would like to display the contents of the lookup table. I don't want to refer to the actual table names, but possibly reference the table through a variable, but I'm having no luck. Could someone point me in the right direction, please? Here's an example:


    DECLARE
    nameoftable all_tables.table_name%type;

    BEGIN
    select table_name into nameoftable from all_tables;

    select * from nameoftable;

    END

    Here are the errors I'm getting:
    ERROR at line 20:
    ORA-06550: line 20, column 4:
    PLS-00356: 'TABLENAME' must name a table to which the user has access
    ORA-06550: line 14, column 3:
    PL/SQL: SQL Statement ignored


    Thanks in advance,

    Franklin

  2. #2
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    I'm not sure what you are trying to do but try this:

    DECLARE
    T_NAME VARCHAR2(100);
    T_TEXT VARCHAR2(4000);
    CURSOR C1 IS SELECT TABLE_NAME FROM USER_TABLES;
    TYPE c_CUR IS REF CURSOR;
    C C_CUR;
    BEGIN
    FOR V1 IN C1 LOOP
    T_NAME := V1.TABLE_NAME;
    OPEN C FOR 'SELECT * FROM '||T_NAME;
    LOOP
    --DO SOMETHING
    EXIT WHEN C%NOTFOUND;
    END LOOP;
    END LOOP;
    END;
    /

  3. #3
    Join Date
    Jul 2000
    Location
    EROS Data Center, South Dakota
    Posts
    2
    Thanks Highlander,

    As you could probably already tell, my experience with pl/sql is very small. I tried your idea and I'm getting the following error:

    ORA-06550: line 9, column 18:
    PLS-00103: Encountered the symbol "SELECT * FROM " when expecting one of the
    following:
    select

    This is after running:
    8 T_NAME := C1.TABLE_NAME;
    9 OPEN C FOR 'SELECT * FROM ' ||T_NAME;

    Do I need to parse the sql, if I try that? Also, I would like to output the results. What's the best (easiest) way to do this? Thanks again for your help.

    Franklin

  4. #4
    Join Date
    Jul 2000
    Posts
    296
    In a SQL statement the table cannot be a variable. If you want be be able to specify the table dynamically, use dynamic sql, in Oracle 7 en 8.0 with package DBMS_SQL, in 8i with execute immediate command or with ref cursor, as Highlander suggested.

  5. #5
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    I can try to help you but I don't really know what you are trying to do. Try to describe excacly what you are trying, including table names and column names, and what is the resault you expect.

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