"Craig & Co." <crb_at_amsa.gov.au> wrote in message
news:422e8174$0$73686$c30e37c6_at_ken-reader.news.telstra.net...
> 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.
> > >
> > >
> >
>
>
No you can't use a table name as a bind variable.(or any variable) You
would have to use dbms_sql or execute immediate.
Jim
Received on Tue Mar 08 2005 - 23:02:35 CST
