INSERT w/inline SELECT

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. Received on Thu Nov 16 2000 - 14:24:28 CST