Re: INSERT w/inline SELECT

In our last gripping episode fpuhan_at_my-deja.com wrote:
> Hi Gurus,
>
> I'm having a brain cramp; this is something I've done a number of
 times
> before. I used to have my source code, but left it behind at my last
> job. :-(
>
> Simplified, here's what I'm attempting. I want to build a table and
> populate it with table names and row counts for a particular schema
> (the schema owner may change, so the general flow is as follows):
>
> drop table tab_info
> /
> create table tab_info
> ( table_name varchar2(40),
> num_rows number(10))
> /
> spool tabins.sql
> -- Here's where it goes sour
> select
> 'insert into tabinfo values ('''||table_name||''',
> (select count(*) from &&SCHEMA_OWNER..'||table_name||'));'
> from dba_tables
> where owner = upper('&&SCHEMA_OWNER')
> /
> spool off
>
> @tabins
>
> -- The problem is that the 'COUNT' subquery is not resolved. Geez,
> I've done this more times that I care to think about, and each time it
> turns into a hair-pulling experience (and I can't afford to lose any
> more hair!). Can someone who's more fluent in SQL help me out? TIA.
>
> =Fred=
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

The difficulty arises with the VALUES statement. If you re-write the code:

 drop table tab_info
 /
 create table tab_info
   ( table_name varchar2(40),
     num_rows number(10))
 /
 spool tabins.sql
 select
  'insert into tab_info select '''||table_name||''',count(*) from '||owner||'.'||table_name||';'
 from dba_tables
 where owner = upper('&&SCHEMA_OWNER')
 /
 spool off

 @tabins

This works quite nicely.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.