Re: How to select from variable table?
Date: 18 Apr 2003 14:28:46 -0700
Message-ID: <130ba93a.0304181328.14a51d82_at_posting.google.com>
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
TEST5 2
TEST5_LOG 4
TEST5_MV 2 PL/SQL procedure successfully completed.
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