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

"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.

Version?

Try this in SQLPlus:

set serveroutput on size 1000000
declare
cursor c1 is
select table_name from user_tables order by 1; v_heading varchar2(100);
v_count number :=0;
begin
-- assuming you have privileges
select 'Count for '||name||' as at '||to_char(sysdate,'DD Mon YYYY HH24:MI') into v_heading from v$database;
dbms_output.put_line(v_heading);
for r1 in c1 loop

    execute immediate ('select count(*) from '||r1.table_name) into v_count;     dbms_output.put_line(rpad(r1.table_name,30)||chr(9)||to_char(v_count)||' rows');
end loop;
end;
/

Regards,
Paul Received on Fri Nov 22 2002 - 15:05:19 CST