When developing an Oracle PL/SQL procedure in Toad, I encounter errors like
These given line numbers and column numbers seem to have little or nothing to do with source code line numbers. Where can I find tips or tricks that will help me identify the actual source line or actual code that caused the error? This is especially important in situations like the above (which is the result of a 700+ line source) where the error message doesn't provide clues as to details of the actual problem. (By the way, line 101 in that source is the third of a series of comment lines, and is followed by a blank line.)
(Note that this is not [yet] in a stored procedure; it is being developed and executed from source at this time. Not sure if that makes a difference since at present there is no database in which we developers have authority to CREATE PROCEDURE.)
Sample code:
SET SERVEROUTPUT ON;
DECLARE
TYPE bendemo_hdr IS RECORD
(
recid CHAR(1)
, client CHAR(30)
, filedesc CHAR(30)
, seqnum CHAR(2)
, crtdate CHAR(20)
, srtdate CHAR(8)
, stpdate CHAR(8)
);
TYPE bendemo_record IS RECORD
(
recid CHAR(1)
, ssn CHAR(9)
, empnum CHAR(15)
, eeflag CHAR(1)
, titlecode CHAR(6)
, fname CHAR(30)
, mname CHAR(30)
, lname CHAR(30)
, namesuffix CHAR(6)
, prefname CHAR(30)
, dob CHAR(8)
, dod CHAR(8)
, dverdte CHAR(8)
, dauddte CHAR(8)
, ddtesrc CHAR(6)
, gender CHAR(1)
, martstat CHAR(6)
, mstateffdt CHAR(8)
, lang CHAR(1)
, citzcde CHAR(6)
, vipflag CHAR(1)
, kyeeflag CHAR(1)
, orghrdte CHAR(8)
, lathrdte CHAR(8)
, adjhrdte CHAR(8)
, jobtitle CHAR(30)
, precntmthd CHAR(6)
, mailpref CHAR(6)
, phnepref CHAR(6)
, emalpref CHAR(6)
, hmaddrefdt CHAR(8)
, hmaddr1 CHAR(60)
, hmaddr2 CHAR(60)
, hmaddr3 CHAR(60)
, hmaddr4 CHAR(60)
, hmaddcity CHAR(60)
, hmaddstate CHAR(60)
, hmaddzip CHAR(10)
, hmaddcnty CHAR(3)
, hmphcnty CHAR(6)
, hmphnbr CHAR(16)
, hmphext CHAR(4)
, mbphcnty CHAR(6)
, mbphnbr CHAR(16)
, faxcnty CHAR(6)
, faxnbr CHAR(16)
, hmemail CHAR(100)
, wkaddrefdt CHAR(8)
, wkaddr1 CHAR(60)
, wkaddr2 CHAR(60)
, wkaddr3 CHAR(60)
, wkaddr4 CHAR(60)
, wkaddcity CHAR(60)
, wkaddstate CHAR(60)
, wkaddzip CHAR(10)
, wkaddcnty CHAR(3)
, wkphcnty CHAR(6)
, wkphnbr CHAR(16)
, wkphext CHAR(4)
, wkemail CHAR(100)
, hiresrc CHAR(30)
, bgnunit CHAR(30)
, qdroflag CHAR(1)
, hiresrcdt CHAR(8)
);
TYPE bendemo_trlr IS RECORD
(
recid CHAR(1)
, client CHAR(30)
, filedesc CHAR(30)
, reccount CHAR(9)
, field1 CHAR(15)
);
demo_hdr bendemo_hdr;
demo_rec bendemo_record;
demo_trlr bendemo_trlr;
i NUMBER;
PROCEDURE dump_hdr_rec IS
BEGIN
DBMS_OUTPUT.put(bendemo_hdr.recid);
DBMS_OUTPUT.put(bendemo_hdr.client);
DBMS_OUTPUT.put(bendemo_hdr.filedesc);
DBMS_OUTPUT.put(bendemo_hdr.seqnum);
DBMS_OUTPUT.put(bendemo_hdr.crtdate);
DBMS_OUTPUT.put(bendemo_hdr.srtdate);
DBMS_OUTPUT.put(bendemo_hdr.stpdate);
DBMS_OUTPUT.put_line('<');
END dump_hdr_rec;
PROCEDURE dump_demo_rec IS
BEGIN
-- IF l_output IS NULL THEN
DBMS_OUTPUT.put(bendemo_record.recid);
DBMS_OUTPUT.put(bendemo_record.ssn);
DBMS_OUTPUT.put(bendemo_record.empnum);
DBMS_OUTPUT.put(bendemo_record.eeflag);
DBMS_OUTPUT.put(bendemo_record.titlecode);
DBMS_OUTPUT.put(bendemo_record.fname);
DBMS_OUTPUT.put(bendemo_record.mname);
DBMS_OUTPUT.put(bendemo_record.lname);
DBMS_OUTPUT.put(bendemo_record.namesuffix);
DBMS_OUTPUT.put(bendemo_record.prefname);
DBMS_OUTPUT.put(bendemo_record.dob);
DBMS_OUTPUT.put(bendemo_record.dod);
DBMS_OUTPUT.put(bendemo_record.dverdte);
DBMS_OUTPUT.put(bendemo_record.dauddte);
DBMS_OUTPUT.put(bendemo_record.ddtesrc);
DBMS_OUTPUT.put(bendemo_record.gender);
DBMS_OUTPUT.put(bendemo_record.martstat);
DBMS_OUTPUT.put(bendemo_record.mstateffdt);
DBMS_OUTPUT.put(bendemo_record.lang);
DBMS_OUTPUT.put(bendemo_record.citzcde);
DBMS_OUTPUT.put(bendemo_record.vipflag);
DBMS_OUTPUT.put(bendemo_record.kyeeflag);
DBMS_OUTPUT.put(bendemo_record.orghrdte);
DBMS_OUTPUT.put(bendemo_record.lathrdte);
DBMS_OUTPUT.put(bendemo_record.adjhrdte);
DBMS_OUTPUT.put(bendemo_record.jobtitle);
DBMS_OUTPUT.put(bendemo_record.precntmthd);
DBMS_OUTPUT.put(bendemo_record.mailpref);
DBMS_OUTPUT.put(bendemo_record.phnepref);
DBMS_OUTPUT.put(bendemo_record.emalpref);
DBMS_OUTPUT.put(bendemo_record.hmaddrefdt);
DBMS_OUTPUT.put(bendemo_record.hmaddr1);
DBMS_OUTPUT.put(bendemo_record.hmaddr2);
DBMS_OUTPUT.put(bendemo_record.hmaddr3);
DBMS_OUTPUT.put(bendemo_record.hmaddr4);
DBMS_OUTPUT.put(bendemo_record.hmaddcity);
DBMS_OUTPUT.put(bendemo_record.hmaddstate);
DBMS_OUTPUT.put(bendemo_record.hmaddzip);
DBMS_OUTPUT.put(bendemo_record.hmaddcnty);
DBMS_OUTPUT.put(bendemo_record.hmphcnty);
DBMS_OUTPUT.put(bendemo_record.hmphnbr);
DBMS_OUTPUT.put(bendemo_record.hmphext);
DBMS_OUTPUT.put(bendemo_record.mbphcnty);
DBMS_OUTPUT.put(bendemo_record.mbphnbr);
DBMS_OUTPUT.put(bendemo_record.faxcnty);
DBMS_OUTPUT.put(bendemo_record.faxnbr);
DBMS_OUTPUT.put(bendemo_record.hmemail);
DBMS_OUTPUT.put(bendemo_record.wkaddrefdt);
DBMS_OUTPUT.put(bendemo_record.wkaddr1);
DBMS_OUTPUT.put(bendemo_record.wkaddr2);
DBMS_OUTPUT.put(bendemo_record.wkaddr3);
DBMS_OUTPUT.put(bendemo_record.wkaddr4);
DBMS_OUTPUT.put(bendemo_record.wkaddcity);
DBMS_OUTPUT.put(bendemo_record.wkaddstate);
DBMS_OUTPUT.put(bendemo_record.wkaddzip);
DBMS_OUTPUT.put(bendemo_record.wkaddcnty);
DBMS_OUTPUT.put(bendemo_record.wkphcnty);
DBMS_OUTPUT.put(bendemo_record.wkphnbr);
DBMS_OUTPUT.put(bendemo_record.wkphext);
DBMS_OUTPUT.put(bendemo_record.wkemail);
DBMS_OUTPUT.put(bendemo_record.hiresrc);
DBMS_OUTPUT.put(bendemo_record.bgnunit);
DBMS_OUTPUT.put(bendemo_record.qdroflag);
DBMS_OUTPUT.put(bendemo_record.hiresrcdt);
DBMS_OUTPUT.put_line('<');
END dump_demo_rec;
PROCEDURE dump_hdr_trlr IS
BEGIN
DBMS_OUTPUT.put(bendemo_trlr.recid);
DBMS_OUTPUT.put(bendemo_trlr.client);
DBMS_OUTPUT.put(bendemo_trlr.filedesc);
DBMS_OUTPUT.put(bendemo_trlr.reccount);
DBMS_OUTPUT.put(bendemo_trlr.field1);
DBMS_OUTPUT.put_line('<');
END dump_hdr_trlr;
BEGIN
demo_bendemo_hdr.recid := 'x';
demo_bendemo_hdr.client := 'Client';
demo_bendemo_hdr.filedesc := 'Descr';
demo_bendemo_hdr.seqnum := 'a';
demo_bendemo_hdr.crtdate := 'Created';
demo_bendemo_hdr.srtdate := 'Sorted';
demo_bendemo_hdr.stpdate := 'stop';
dump_demo_hdr(l_output, demo_hdr);
END;
Without seeing an example of the sort of code you are running it is hard to be sure if this answer is relevant, but the line number specified in the message relates to the overall block of PL/SQL being compiled, and ignores any blank lines or comments before that line. For example if your script says (with line numbers added for clarity only):
1
2 -- test script
3
4
5 begin
6
7 rubbish;
8
9 end;
Then when you try to run it you will get the error:
ORA-06550: line 3, column 3:
PLS-00201: identifier 'RUBBISH' must be declared ...
It says line 3 not line 7 because "rubbish;" is the 3rd line of the PL/SQL being compiled. The blank lines and comments above that do not count. Blank lines and comments within the block do count however.
So, applying my rule to the code sample you posted we can ignore the 2 lines above DECLARE and so "line 97" is the 99th line:
DBMS_OUTPUT.put(bendemo_hdr.recid);
But bendemo_hdr is a TYPE not a variable, and so it makes no sense in this statement, hence the error. It is analagous to writing:
DBMS_OUTPUT.put(CHAR(1)); --!!!
What you probably meant was:
DBMS_OUTPUT.put(demo_hdr.recid);