Re: PL/SQL Query problems.

Hi,

TB_NAME is a variable. You cannot use it directly in a DML statement. In your case, try using Dynamic PL/SQL
i.e.
EXECUTE IMMEDIATE 'SELECT count(1) into user_count from :tab_name where .... ' USING 'TB_NAME'; HTH
Ashish

Craig & Co. wrote:

> 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:44:06 CST