Re: PL/SQL Query problems.

From: Ashish <srivastava.ashish_at_gmail.com>
Date: Wed, 09 Mar 2005 10:57:05 +0530
Message-ID: <b0wXd.36$iu2.138@news.oracle.com>

Maybe try this:

EXECUTE IMMEDIATE 'SELECT COUNT(1) INTO USER_COUNT FROM '||TB_NAME||' WHERE ...'; HTH
Ashish

Craig & Co. wrote:

> Hi,
>
> Tried the following:
> EXECUTE IMMEDIATE 'SELECT count(1)
> INTO User_Count
> FROM :tab_name
> WHERE TLAST_CHANGED_UID = ''CRB'''
> USING 'TB_NAME';
>
> And it compiles, but when I execute it I get
> ERROR at line 1:
> ORA-00905: missing keyword
> ORA-06512: at "CHANGE_CRB", line 21
> ORA-06512: at line 1
>
> Something to do with the BIND variable?
>
> Cheers
> Craig.
>
> "Ashish" <srivastava.ashish_at_gmail.com> wrote in message
> news:ZnvXd.34$iu2.20_at_news.oracle.com...
>
>

>>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 - 23:27:05 CST