Re: Variable Table Names in PL/SQL???

In article <319A67C9.38A9_at_neuraltech.com>, "Darrell Duane Jr." <dduane_at_neuraltech.com> writes:
|> I've got a PL/SQL program here that will be run from SQL*Plus.
|> It gives me an error. I've got data across many tables that I'd
|> like to analyze as if it was one big table. Here's one way
|> I tried to do it, but its not working. Any ideas?
|>
|> VARIABLE tot NUMBER;
|> VARIABLE tcnt NUMBER;
|> VARIABLE tablename CHAR(15);
|> DECLARE
|> jdate NUMBER(2) := 67;
|> BEGIN
|> :tot := 0;
|> :tcnt := 0;
|> WHILE jdate < 74 LOOP
|> :tablename := 'water19960' || TO_CHAR(jdate);
|> select count(*) from :tablename into :tcnt where WATER_SAMPLE =
|> '37';
|> :tot := :tot + :tcnt;
|> jdate := jdate + 1;
|> END LOOP;
|> END;
|> /
|> PRINT tablename;
|> PRINT tot;
|>
|>
|> The error I get is:
|>
|> SQL> _at_tryit
|> select count(*) from :tablename into :tcnt where WATER_SAMPLE = '37';
|> *
|> ERROR at line 8:
|> ORA-06550: line 8, column 26:
|> PLS-00103: Encountered the symbol "" when expecting one of the
|> following:
|> a PL/SQL variable or double-quoted string
|> an expanded name
|> an expanded name link
|> etc.
|> Resuming parse at line 8, column 72.
|>
|>
|> I have also tried this by making tname a PL/SQL variable, and
|> also get an error. Do you have any idea how I might use a
|> variable for a table name? I don't think a cursor would help me.

You can't do this. The table name is a syntatic part of the statement, and thus you can't use a bind variable for it. This is a property of the fact that PL/SQL uses early binding. The solution is to use the DBMS_SQL package to execute dynamic SQL. See $ORACLE_HOME/rdbms/admin/dbmssql.sql, the Application Developer's Guide, or Chapter 10 of _Oracle PL/SQL Programming_.

|>
|> Thanks,
|> Darrell
|>
|> Darrell Duane, Jr., Developer e-mail: dduane_at_neuraltech.com
|> NeuralTech, Inc. phone: (703) 359 2400
|> 11320 Random Hills Road, Suite 630 fax: (703) 359 2419
|> Fairfax, VA 22030-6001 URL: http://www.neuraltech.com
 

-- 
------------------------------------------------------------------------
Scott Urman            Oracle Corporation           surman_at_us.oracle.com
------------------------------------------------------------------------
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2
Published by Oracle Press - http://www.osborne.com/oracle/index.htm
------------------------------------------------------------------------
"The opinions expressed here are my own, and are not necessarily that of
 Oracle Corporation"
------------------------------------------------------------------------