-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|