is it possible in pl/sql?

From: Guang Mei <GMei_at_ph.com>
Date: Wed, 9 Feb 2005 16:16:30 -0500
Message-ID: <7E62D965D357694C993D4F0E13B3C39B04D37EA6@phexchange.ph.com>

• 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?

TIA. Guang

---

PRIVILEGED AND CONFIDENTIAL:
This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies.

---

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

Received on Wed Feb 09 2005 - 16:15:42 CST