Re: How to select from variable table?

You can not use identifier for the bind variable, so just concatnate the string like:

SQL> set serveroutput on
SQL> declare
  2 cursor x is select table_name from user_tables;   3 w number;
  4 begin
  5 for y in x loop
  6 execute immediate 'select count(1) from '||y.table_name into w;   7 dbms_output.put_line(y.table_name||' '||w);   8 end loop;
  9 end;
 10 /
TEST1 2
TEST1_MV 2
TEST2 1
TEST3 4

TEST3_LOG 8
TEST3_MV 4
TEST3_MV2 3

SQL>

• Jusung Yang

tho_pic_at_yahoo.com (Tho Nguyen) wrote in message news:<d177c6d9.0304180329.68d4756d_at_posting.google.com>...
> Hi,
> I have to make a report with 2 columns. The first one is the table
> name and the second one is the number of rows in that table. I think
> the best solution for me is to create a cursor to select all the table
> from user_tables, after that using for loop iterator to get each
> elements in that cursor and select count(*).
> Unfortunately, dynamic SQL doesn't accept identifier(table name) as
> variable. so I can't do it.
> Any solutions and hints are appreciated.
> Thanks so much.
Received on Fri Apr 18 2003 - 23:28:46 CEST