Re: PL/SQL Query problems.

From: Craig & Co. <crb_at_amsa.gov.au>
Date: Wed, 9 Mar 2005 15:54:18 +1100
Message-ID: <422e8174$0$73686$c30e37c6@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.
> >
> >
>
Received on Tue Mar 08 2005 - 22:54:18 CST