Re: is it possible in pl/sql?

  • From: Anthony Wilson <amwilson@xxxxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 12 Feb 2005 02:48:00 +0800

I only have 10g here to test on but I believe MERGE will work in 9i.
Don't know if this is any more efficient than other solutions already
offered, but it DOES get rid of the cursor looping, for better or
worse(!) and also allows for new tables which are not already present
in my_tables.

create function count_from_table(table_name in varchar2)
return number
  cnt number(12);
  execute immediate 'select count(*) from '||table_name
  into cnt;
  return cnt;

merge into my_tables m
  using (select table_name from user_tables where table_name != 'MY_TABLES') n
  on (m.table_name = n.table_name)
  when matched then update set m.new_rows = (select 
count_from_table(m.table_name) from dual)
  when not matched then insert (m.table_name, m.new_rows)
    values (n.table_name, (select count_from_table(n.table_name) from dual));

Now I'm sure the gurus can pick it to pieces... in fact please do.  I
learn more that way...

Oh, and here is more than you ever wanted to know about count(1) vs.
count(*).  Short answer, it's a myth according to the venerable Tom
Kyte.  The SQL engine silently rewrites count(1) to count(*):

Anthony Wilson

On Wed, Feb 09, 2005 at 04:16:30PM, Guang Mei wrote:
> -- oracle 9i
> -- code works something like this now:
> CURSOR c_user_tables  IS
> SELECT table_name FROM user_tables ;
> begin
> FOR x  IN c_user_tables  LOOP
>     BEGIN
>            SELECT table_name INTO dummy FROM myTables
>            WHERE table_name = x.Table_Name;
>                sqlstmt := 'UPDATE myTables SET new_Rows = (select count(*)
> from ' || x.Table_Name||  ') WHERE Table_Name = ' || x.Table_Name||';
>                -- execute dynamic sql
>    END;   
> end;
> /
> Is there a way to get rid off the cursor looping. And
> 1. I don't want to use dynamic sql
> 2. I don't want to analyze user_tables
> 3. I want to update myTables is one sql statment, prefer using static sql.
> Possible?

Anthony Wilson

-- Attached file included as plaintext by Ecartis --
-- File: signature.asc
-- Desc: Digital signature

Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see



Other related posts: