From: fbortel@nescape.net   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)