Re: is it possible in pl/sql?

From: david wendelken <davewendelken_at_earthlink.net>
Date: Wed, 9 Feb 2005 17:04:24 -0800 (PST)
Message-ID: <7780946.1107997464556.JavaMail.root@beaker.psp.pas.earthlink.net>

Gosh, the original programmer was somewhat confused about what they are doing and/or coding in general and/or coding in pl/sql.

Try something like this:

DECLARE
  CURSOR c_user_tables IS
  SELECT table_name FROM mytables ;
  stmnt VARCHAR2(4000);
BEGIN
  FOR x IN c_user_tables LOOP
    stmnt := 'UPDATE myTables SET new_rows = '

                   || '(SELECT COUNT(1) '
                   || 'FROM ' || x.table_name || ') '
                   || 'WHERE table_name = ''' || x.table_name || '''';

    EXECUTE IMMEDIATE stmnt;
  END LOOP;
END;
/

I leave it to you to handle the situation where a table name is in mytables that doesn't exist or the user doesn't have update access to it or is missing the synonym to it.

See my inline comments about the original code.

-----Original Message-----
From: Guang Mei <GMei_at_ph.com>
Sent: Feb 9, 2005 1:16 PM
To: "'oracle-l_at_freelists.org'" <oracle-l_at_freelists.org> Subject: is it possible in pl/sql?

• oracle 9i
• code works something like this now:
• This cursor makes no sense. it should be from myTables.
• User_tables has no bearing on this. CURSOR c_user_tables IS SELECT table_name FROM user_tables ;

begin
FOR x IN c_user_tables LOOP

    BEGIN

• this statement does not make any sense.
• it will cause an unhandled exception
• as soon as a table in user_tables isn't in mytables.
  • if all records in user_tables are guaranteed to be in
    • mytables, then why not loop from it instead?
      • if records can be in mytables that aren't in user_tables,
      • then join my_tables and user_tables in the cursor statement. SELECT table_name INTO dummy FROM myTables WHERE table_name = x.Table_Name;
      • count(*) is wasteful. count(1) is more efficient. 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

Yes, you could build a function that returns the count given a table_name. It will have dynamic sql in it (or a darned long hard-coded if-statement that would defeat the purpose of mytables.

1. I don't want to use dynamic sql

You can either hard-code it or use dynamic sql. You can use sql to write the hard-coded version, but it becomes obsolete as soon as the contents of my_tables changes.

I suggest you get over it and embrace dynamic sql. It's very easy nowadays, unlike when it first came out.

2. I don't want to analyze user_tables

You don't have to. It's a view anyway, so I'm not sure that would be useful. :) Never tried to do that!

3. I want to update myTables is one sql statment, prefer using static sql.

Possible?

Yes, you could hard-code the table names, but that would defeat the purpose of holding the table names in mytables.

If there's another way, I would ***dearly*** like to know about it. I would bow down to the sql master!

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

Received on Wed Feb 09 2005 - 20:07:13 CST