EXCEPTION block called erroneously - help

[Scott J. Chlebove]

I am running a PL/SQL module in which I do the following....

BEGIN
DBMS_OUTPUT.enable(1000000);

-- Open file for writing.
OutfileHandler1 := UTL_FILE.FOPEN(wc_file_path, wc_output_file1,
'W'); -- output report
-- Get the CUSTOMER records by reading the CUSTOMER table.

ws_error_loc := 1005;
OPEN cust_cur;
-- Get a customer record that is among the qualifying types.
LOOP -- cust_cur looping
ws_error_loc := 1010;
FETCH cust_cur INTO cust_rec;
EXIT WHEN cust_cur%NOTFOUND;
ws_ct_cust:=ws_ct_cust+1;
....
Do a bunch of processing (i.e.: creating a report)
....

END LOOP;
CLOSE cust_cur;

UTL_FILE.FCLOSE(OutfileHandler1);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('***** TAX EXEMPTION INFORMATION
*****');
DBMS_OUTPUT.PUT_LINE('ERROR : ' || TO_CHAR(SQLCODE) || ' -
');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(' at location : ' || ws_error_loc );
DBMS_OUTPUT.PUT_LINE(' CUST-NO............' ||
cust_rec.ky_cust_no);
DBMS_OUTPUT.PUT_LINE(' KY-BA..............' ||
ba_rec.ky_ba);
DBMS_OUTPUT.PUT_LINE(' CD_BUS.............' || te_rec.cd_bus
);
DBMS_OUTPUT.PUT_LINE(' KY-PROD-ORDNO......' ||
te_rec.ky_prod_ordno );
DBMS_OUTPUT.PUT_LINE(' CD-TAX-TYPE........' ||
te_rec.cd_tax_type );
DBMS_OUTPUT.PUT_LINE(' PC-TAX-EXCEP.......' ||
te_rec.pc_tax_excep );
ROLLBACK;

END;
/
exit;

The "cust_cur" looks like this, from my DECLARATION section...

-- CUSTOMER table dursor
CURSOR cust_cur
IS
SELECT ky_cust_no,
nm_cust_1,
cd_cust_type,
cd_co,
nm_cust_2
FROM customer
WHERE cd_cust_type NOT IN
('1' , -- VS-NATIONAL
'F' , -- VS-GOVT-FEDERAL
'I' , -- VS-INDIVIDUAL
'M' , -- VS-GOVT-MUNICIPAL
'S' , -- VS-GOVT-ST
'T' , -- VS-GOVT-AUTH
'Y' , -- VS-GOVT-COUNTY
'E' ) -- VS-COMPANY-USE
ORDER BY ky_cust_no;
cust_rec cust_cur%ROWTYPE;
(Note tht I had some diagnostic PUT_LINE statements that I'd edited
out, for clarity). After the last qualifying "cust_rec" is processed,
upon the next "FETCH" when the "EXIT" should execute, this does NOT
appear to be happening, rather, control is passed to the EXCEPTION
block. The SQLERRM that is written is "User-Defined Exception", while
the SQLCODE is "1" (which only occurs upon update or insert - "Unique
constraint violation" - so this is not even valid in this situation,
since I'm just "SELECT"ing).

I have no idea why the loop is not exited!!!
There is only one CUSTOMER record with this particular ky_cust_no.
I even went so far in my testing as to determine the maximum
ky_cust_no, then before performing the FETCH, attempting to EXIT WHEN
this value is the same as that which is already contained in
"cust_rec.ky_cust_no" (from the prior FETCH), - I STILL get the
execution of the EXCEPTION block.

When I comment out the EXCEPTION block, I get ...
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 103
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "SYS.UTL_FILE", line 218
ORA-06512: at line 471
in which, these line numbers make absolutely no sense.
Any suggestion would be greatly appreciated - I'm gettin'
frustrated!!!

[Frank]

Scott J. Chlebove wrote:

I am running a PL/SQL module in which I do the following....

BEGIN
DBMS_OUTPUT.enable(1000000);

-- Open file for writing.
OutfileHandler1 := UTL_FILE.FOPEN(wc_file_path, wc_output_file1,
'W'); -- output report
-- Get the CUSTOMER records by reading the CUSTOMER table.

ws_error_loc := 1005;
OPEN cust_cur;
-- Get a customer record that is among the qualifying types.
LOOP -- cust_cur looping
ws_error_loc := 1010;
FETCH cust_cur INTO cust_rec;
EXIT WHEN cust_cur%NOTFOUND;
ws_ct_cust:=ws_ct_cust+1;
...
Do a bunch of processing (i.e.: creating a report)
...

END LOOP;
CLOSE cust_cur;

UTL_FILE.FCLOSE(OutfileHandler1);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('***** TAX EXEMPTION INFORMATION
*****');
DBMS_OUTPUT.PUT_LINE('ERROR : ' || TO_CHAR(SQLCODE) || ' -
');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(' at location : ' || ws_error_loc );
DBMS_OUTPUT.PUT_LINE(' CUST-NO............' ||
cust_rec.ky_cust_no);
DBMS_OUTPUT.PUT_LINE(' KY-BA..............' ||
ba_rec.ky_ba);
DBMS_OUTPUT.PUT_LINE(' CD_BUS.............' || te_rec.cd_bus
);
DBMS_OUTPUT.PUT_LINE(' KY-PROD-ORDNO......' ||
te_rec.ky_prod_ordno );
DBMS_OUTPUT.PUT_LINE(' CD-TAX-TYPE........' ||
te_rec.cd_tax_type );
DBMS_OUTPUT.PUT_LINE(' PC-TAX-EXCEP.......' ||
te_rec.pc_tax_excep );
ROLLBACK;

END;
/
exit;

The "cust_cur" looks like this, from my DECLARATION section...

-- CUSTOMER table dursor
CURSOR cust_cur
IS
SELECT ky_cust_no,
nm_cust_1,
cd_cust_type,
cd_co,
nm_cust_2
FROM customer
WHERE cd_cust_type NOT IN
('1' , -- VS-NATIONAL
'F' , -- VS-GOVT-FEDERAL
'I' , -- VS-INDIVIDUAL
'M' , -- VS-GOVT-MUNICIPAL
'S' , -- VS-GOVT-ST
'T' , -- VS-GOVT-AUTH
'Y' , -- VS-GOVT-COUNTY
'E' ) -- VS-COMPANY-USE
ORDER BY ky_cust_no;
cust_rec cust_cur%ROWTYPE;
(Note tht I had some diagnostic PUT_LINE statements that I'd edited
out, for clarity). After the last qualifying "cust_rec" is processed,
upon the next "FETCH" when the "EXIT" should execute, this does NOT
appear to be happening, rather, control is passed to the EXCEPTION
block. The SQLERRM that is written is "User-Defined Exception", while
the SQLCODE is "1" (which only occurs upon update or insert - "Unique
constraint violation" - so this is not even valid in this situation,
since I'm just "SELECT"ing).

I have no idea why the loop is not exited!!!
There is only one CUSTOMER record with this particular ky_cust_no.
I even went so far in my testing as to determine the maximum
ky_cust_no, then before performing the FETCH, attempting to EXIT WHEN
this value is the same as that which is already contained in
"cust_rec.ky_cust_no" (from the prior FETCH), - I STILL get the
execution of the EXCEPTION block.

When I comment out the EXCEPTION block, I get ...
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 103
ORA-06512: at "SYS.UTL_FILE", line 120
ORA-06512: at "SYS.UTL_FILE", line 218
ORA-06512: at line 471
in which, these line numbers make absolutely no sense.
Any suggestion would be greatly appreciated - I'm gettin'
frustrated!!!

The "usual" way of processing is:
- open cursor
- fetch
- loop (or: while %FOUND)
- exit when %NOTFOUND (not if while %FOUND is used)
- fetch
- end loop.

#2 is missing (as well as info re Oracle version...)
what about:

for x in ( SELECT ky_cust_no,
nm_cust_1,
cd_cust_type,
cd_co,
nm_cust_2
FROM customer
WHERE cd_cust_type NOT IN
('1' , -- VS-NATIONAL
'F' , -- VS-GOVT-FEDERAL
'I' , -- VS-INDIVIDUAL
'M' , -- VS-GOVT-MUNICIPAL
'S' , -- VS-GOVT-ST
'T' , -- VS-GOVT-AUTH
'Y' , -- VS-GOVT-COUNTY
'E' ) -- VS-COMPANY-USE
ORDER BY ky_cust_no) loop
dbms_output.put_line('Cust_no '||to_char(x.ky_cust_no));
end loop;
etc.

Re the exception, and line#: those lines refer to UTL_FILE, not your
proc. The error occurs in line 471 of your proc (which must be doing
something with the file, as UTL_FILE is called)
--
Merry Christmas and a Happy New Year,
Frank van Bortel