fitzjarrell_at_cox.net wrote:
> Fred Wilson wrote:
>> 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
>
> I, too, am wondering how you're seeing such an error. Creating a table
> to return more output than Daniel provided I still have no problems
> (syntactically) with your code:
>
> SQL> create table snarfengarfer
> 2 as select object_name, object_type, 136 evt_cd
> 3 from all_objects;
>
> Table created.
>
> SQL>
> SQL> set serveroutput on size 1000000
> SQL>
> SQL> 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
> +++++++++++++++++++++++ SNARFENGARFER
> NUMBER OF RECORDS IN THIS TABLE: 38332
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> Maybe it's in the water ...
>
>
> David Fitzjarrell
>
This is actually part of a stored procedure.
Received on Fri Sep 22 2006 - 15:59:54 CDT
