EXCEPTION block called erroneously - help

From: Scott J. Chlebove <chlebsco_at_enter.net>
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

Original text of this message