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

One way to do it is to write your SELECT as many SELECTs UNIONed together. If you need to use dynamic SQL like you are attempting below, you will need to use the Oracle 7 Server DBMS_SQL built-in package (verison 2.1 of PL/SQL or greater). In it you have the ability to perform dynamic SQL.

In article <319A67C9.38A9_at_neuraltech.com>, dduane_at_neuraltech.com says...
>
>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.
>
>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
Received on Wed May 22 1996 - 00:00:00 CEST