Re: PL/SQL variables in the "from" part of a select statement

Not possible the way you attempted to do it. On 8i onwards it's possible with native dynamic sql though:

fetch list_tables into local_var;
EXECUTE IMMEDIATE 'select count(*) from '||local_var INTO count1;

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"rac" <caronra_at_pweh.com> wrote in message news:3fee8adb.0211220906.32984e6e_at_posting.google.com...


> Does anyone know if it is possible to access a variable in the "from

> table" section of a query?

> I have the following code (not exact)

> 

> DECLARE

>  local_var VARCHAR2(30);

>  count1 NUMBER;

> 

> CURSOR list_tables is

>   select table_name from all tables;

> 

> 

> BEGIN

> open list_tables;

> LOOP

> FETCH list_tables INTO local_var;

> 

> select count(*) into count1 from local_var;

> 

> 

> Error resulting is:

> ORA-06550: line .......

> PLS-00201: identifier 'LOCAL_VAR' must be declared.

> 

> 

> Any suggestions??

> 

> Thanks.