Re: EXCEPTION block called erroneously - help

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;

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