Re: Help: Error Invalid Number at line 22

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') Received on Fri Sep 22 2006 - 16:22:41 CDT