Re: is it possible in pl/sql?

Hi,
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
is
  cnt number(12);
begin
  execute immediate 'select count(*) from '||table_name   into cnt;
  return cnt;
end;

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(*):

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1156159920245

cheers,
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 LOOP;
> 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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFCDP3gApUeo2ZHBDwRAqo3AKCEwaE0oWC5aexyNZDQbL5aVTatfgCgpMUU
Q72ueA441tecKdmspWzK1mU=
=6yc2
-----END PGP SIGNATURE-----


--
http://www.freelists.org/webpage/oracle-l