Variable Table Names in PL/SQL???

From: Darrell Duane Jr. <dduane_at_neuraltech.com>
Date: 1996/05/15
Message-ID: <319A67C9.38A9_at_neuraltech.com>#1/1

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 15 1996 - 00:00:00 CEST