Re: is it possible in pl/sql?

  • From: david wendelken <davewendelken@xxxxxxxxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 9 Feb 2005 17:04:24 -0800 (PST)

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:

  CURSOR c_user_tables  IS
  SELECT table_name FROM mytables ;
  stmnt VARCHAR2(4000);
  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 || '''';

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@xxxxxx>
Sent: Feb 9, 2005 1:16 PM
To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
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 ;

FOR x  IN c_user_tables  LOOP
             -- 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

>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 
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.


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!


Other related posts: