Thanks Ashish - That's what I was looking for.
Cheers
Craig.
"Ashish" <srivastava.ashish_at_gmail.com> wrote in message
news:7fwXd.37$iu2.31_at_news.oracle.com...
> 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 Wed Mar 09 2005 - 15:49:06 CST
