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

From: Muhammad A. Malik <mamalik_at_cs.wmich.edu>
Date: 1996/05/16
Message-ID: <319B9079.67E0_at_cs.wmich.edu>#1/1


Darrell Duane Jr. wrote:
>
> 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

Try to create a lexical variable. See example:

define tablename=emp;

Select * from &tablename order by bla, bla;

undefine tablename;

But this is a SQL script not a PL/sql. Try to embed this idea in your code.

-- 

Muhammad A. Malik
mamalik_at_cs.wmich.edu
+1 (616) 342-4569
Received on Thu May 16 1996 - 00:00:00 CEST

Original text of this message