Re: when is a variable not a variable

John,

Those who suggested that you use dynamic SQL are correct, but perhaps you may not have been given a sufficient explanation. Oracle's PL/SQL requires that table and column names be explicitly (or statically) defined in your code.

Using dynamic SQL (via the package DBMS_SQL) would solve your problem. However, I think it is overkill for what you are trying to do. I suggest making a small change to what you had. The strategy is to write your script so that it's output is a second script that when executed populates the table: table_count. In effect, you are using SQL*PLUS to generate code.

spool script.sql;
set serveroutput on size=100000
BEGIN

	FOR x IN c_tables LOOP
		dbms_output.put_line(
		    'INSERT INTO table_count (table_name, count)' || 
                    'select ''' || x.table_name || ''', count(*) ' ||
                        'from ' || x.table_name || ';'

	END LOOP;

Then use SQL*PLUS a second time to run the file script.sql to populate the table. Since COUNT is a reserved word, I am surprised that your TABLE_COUNT table could have a column called COUNT. You may want to rename it.

Note: This was written outside of sql*plus so it may not compile, but it should be close to the final solution.

Al

Al Received on Thu Jan 30 1997 - 00:00:00 CST