Re: Help: Error Invalid Number at line 22

From: Fred Wilson <faNOWilsonSPAM_at_comcast.net>
Date: Fri, 22 Sep 2006 06:52:07 -0400
Message-ID: <6NqdnagaG8CzXY7YnZ2dnUVZ_v2dnZ2d@comcast.com>

DA Morgan 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;

> 
> SQL> ed
> Wrote file afiedt.buf
> 
>   1  declare
>   2  CURSOR CUR_LOC IS
>   3     SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME='EVT_CD';
>   4  OLD_EV_CD VARCHAR(20) :='136';
>   5  T_NAME VARCHAR(40);
>   6  num_of_rec number;
>   7  v_IM_EX varchar(2000);
>   8  BEGIN
>   9  DBMS_OUTPUT.ENABLE (200000);
>  10  DBMS_OUTPUT.PUT_LINE ('EVENT CODE IS: ' || OLD_EV_CD);
>  11  OPEN CUR_LOC;
>  12  LOOP
>  13    FETCH CUR_LOC INTO T_NAME;
>  14    v_IM_EX := '(SELECT COUNT(*) FROM ' || T_NAME || ' WHERE EVT_CD = 
> ' || OLD_EV_CD || ')';
>  15    EXIT WHEN CUR_LOC%NOTFOUND;
>  16    DBMS_OUTPUT.PUT_LINE ('+++++++++++++++++++++++  ' || T_NAME);
>  17    EXECUTE IMMEDIATE v_IM_EX into NUM_OF_REC;
>  18    DBMS_OUTPUT.PUT_LINE ('NUMBER OF RECORDS IN THIS TABLE: ' || 
> NUM_OF_REC);
>  19  END LOOP;
>  20  CLOSE CUR_LOC;
>  21* END;
>  22  /
> EVENT CODE IS: 136
> 
> PL/SQL procedure successfully completed.
> 
> SQL>
> 
> 
> What problem?
> 
> What bothers me is that this is an inappropriate use of a cursor
> Inappropriate use of a loop
> Inappropriate use of native dynamic SQL
> And the NDS is not using the USING clause (bind variables)
> 
> Syntactically I don't see the issue.

I am not sure what this issue is either but I get the errors as indicated in the subject line. I appreciate your comments. I will have to look up what you are talking about. This is my first crack at teaching myself Oracle stuff. I normally use MS Access.

Thanks,
Fred Received on Fri Sep 22 2006 - 05:52:07 CDT