-
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
-
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;
/
-
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
-
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.
-
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.
