Re: Help: Error Invalid Number at line 22

From: <fitzjarrell_at_cox.net>
Date: 22 Sep 2006 11:39:57 -0700
Message-ID: <1158950396.925421.158380@b28g2000cwb.googlegroups.com>

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 Received on Fri Sep 22 2006 - 13:39:57 CDT