Sorry.. even the below wont work:
Use this :
declare
tab_name varchar2(30) := 'EMP';
cnt number ;
sql_stmt varchar2(2000) ;
begin
>> sql_stmt := 'select count(1) from '||tab_name||' where sal > 2500';
>> EXECUTE IMMEDIATE sql_stmt INTO cnt;
dbms_output.put_line('Rows fetched = '||cnt);
end;
Ashish wrote:
> 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:43:01 CST
