EXCEPTION block called erroneously - help
Date: 19 Dec 2003 14:31:49 -0800
Message-ID: <6a4d6da2.0312191431.77c589f7_at_posting.google.com>
I am running a PL/SQL module in which I do the following....
BEGIN
DBMS_OUTPUT.enable(1000000);
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!!! Received on Fri Dec 19 2003 - 23:31:49 CET