PL/SQL Query problems.

From: Craig & Co. <crb_at_amsa.gov.au>
Date: Wed, 9 Mar 2005 15:05:06 +1100
Message-ID: <422e75f5$0$73686$c30e37c6@ken-reader.news.telstra.net>

Hi,

Trying to get all the tables with the field 'TLAST_CHANGED_UID, then using that list
get the number of records in each table where the field above equals a specific user.

I have the following:
CREATE OR REPLACE PROCEDURE CHANGE_CRB IS BEGIN
   DECLARE

      CURSOR C1_Table IS
         SELECT TABLE_NAME
         FROM USER_TAB_COLUMNS
         WHERE COLUMN_NAME = 'TLAST_CHANGED_UID';
      User_Count NUMBER(10);
      TABLE_REC C1_Table%ROWTYPE;
      TB_NAME VARCHAR2(30);

BEGIN
   OPEN C1_Table;
   <<master>>
   LOOP

      FETCH C1_Table INTO TABLE_REC;
      EXIT master WHEN C1_Table%NOTFOUND;
      TB_NAME := TABLE_REC.TABLE_NAME;
      dbms_output.put_line ('TABLE '||TB_NAME);
      <<inner>>
      LOOP
         SELECT count(1)
         INTO User_Count
         FROM TB_NAME
         WHERE tlast_changed_uid = 'CRB';
            if ( User_Count > 0 ) then
               dbms_output.put_line ('TABLE '||TB_NAME||'- Count:
'||User_Count);
            end if;
      END LOOP inner;

   END LOOP;
   CLOSE C1_Table;
END;
END;
/

When trying to compile the procedure I get the following error. 21/10 PL/SQL: SQL Statement ignored
23/15 PLS-00201: identifier 'TB_NAME' must be declared

If I -- the <<inner>> LOOP out, the procedure compiles and executes with out a problem.
As soon as I attempt to use the TB_NAME variable I get problems. I am guessing that because TB_NAME is not a real table, that is triggering the error, but how
do I use the list that I get from the CURSOR to test for the number of records a user has updated?

Craig. Received on Tue Mar 08 2005 - 22:05:06 CST