Re: Help: Error Invalid Number at line 22

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 21 Sep 2006 18:33:33 -0700
Message-ID: <1158888810.499597@bubbleator.drizzle.com>

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.

-- 
Puget Sound Oracle Users Group

Received on Thu Sep 21 2006 - 20:33:33 CDT