Fred Wilson wrote:
> Fred Wilson wrote:
> > If anyone can enlighten me?
> >
> > The error is reporting for the EXECUTE IMMEDIATE LINE below
> >
> > I am going to post another message with a error from this same code but
> > gives me Error Missing Keyword in line 15.
> >
> > Thank you.
> >
> > CURSOR CUR_LOC IS
> >
> > SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='EVT_CD';
> >
> > OLD_EV_CD VARCHAR(20) :='136';
> >
> > T_NAME VARCHAR(40);
> >
> > num_of_rec number;
> >
> > v_IM_EX varchar(2000);
> >
> >
> >
> > BEGIN
> >
> >
> >
> > DBMS_OUTPUT.ENABLE (200000);
> >
> > DBMS_OUTPUT.PUT_LINE ('EVENT CODE IS: ' || OLD_EV_CD);
> >
> > OPEN CUR_LOC;
> >
> > LOOP
> >
> > FETCH CUR_LOC INTO T_NAME;
> >
> >
> >
> > v_IM_EX := '(SELECT COUNT(*) FROM ' || T_NAME || ' WHERE EVT_CD = '
> > || OLD_EV_CD || ')';
> >
> >
> >
> > EXIT WHEN CUR_LOC%NOTFOUND;
> >
> >
> >
> > DBMS_OUTPUT.PUT_LINE ('+++++++++++++++++++++++ ' || T_NAME);
> >
> > EXECUTE IMMEDIATE v_IM_EX into NUM_OF_REC;
> >
> > DBMS_OUTPUT.PUT_LINE ('NUMBER OF RECORDS IN THIS TABLE: ' ||
> > NUM_OF_REC);
> >
> > END LOOP;
> >
> > CLOSE CUR_LOC;
> >
> > END;
>
>
> Okay, the intent of this procedure is to go through a database that was
> acquired, find all the tables that have a column_name of "EVT_CD". As
> the script comes along I want to go through all the records in each of
> the tables and change Event codes 136 to 194.
>
> I wanted a procedure so it could be dynamic so at a later date I could
> do something like SQL> exec CHG_EVT ('xxx','yyy')
Hi Fred,
the following line
v_IM_EX := '(SELECT COUNT(*) FROM ' || T_NAME || ' WHERE EVT_CD = '
|| OLD_EV_CD || ')';
I would replace with
v_IM_EX := '(SELECT COUNT(*) FROM ' || T_NAME || ' WHERE EVT_CD = '''
|| OLD_EV_CD || '')';
Regards
Sebastian
Received on Sat Sep 23 2006 - 16:12:40 CDT
