Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: generating SQL in SQL
SQL> select 'select count(*) from ' || table_name || ';' from all_tables;
'SELECTCOUNT(*)FROM'||TABLE_NAME||';'
select count(*) from DUAL; select count(*) from SYSTEM_PRIVILEGE_MAP; select count(*) from TABLE_PRIVILEGE_MAP; select count(*) from STMT_AUDIT_OPTION_MAP; select count(*) from AUDIT_ACTIONS; select count(*) from PSTUBTBL; select count(*) from USER_PROFILE; select count(*) from HELP; select count(*) from ROSSTRINGS; select count(*) from ROSLFDESC; select count(*) from ROSTFDESC; select count(*) from ROSOBJMAP; select count(*) from ROSSEQUENCES; select count(*) from REG_YA_ERRATA_TRANSLATIONS; select count(*) from COUNTRY_TEMP;
This will work inside a procedure using cursors as well.
Scott Freeman <freeman_at_cs.purdue.edu> wrote in message
news:7v79t5$60n$1_at_nnrp1.deja.com...
> I am trying to generate SQL select statements dynamically through a
> select. What I have is:
>
> select "select """||table_name||""", count(*) from "||table_name||";"
> from all_tables
> where table_name = 'foo';
>
> The statement I am trying to get out of this is:
>
> select "foo", count(*) from foo;
>
> I get the following error...
>
> select "select """||table_name||""", count(*) from "||table_name||";"
> *
> ORA-00904: invalid column name
>
> Help with this would be appreciated.
>
> Scott
>
> --
> Scott E. Freeman
> freeman_at_cs.purdue.edu
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Oct 27 1999 - 18:53:51 CDT