Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: generating SQL in SQL

Re: generating SQL in SQL

From: Steve McDaniels <steve.mcdaniels_at_sierra.com>
Date: Wed, 27 Oct 1999 16:53:51 -0700
Message-ID: <7v83db$pcm$1@plo.sierra.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US