edreyes77_at_hotmail.com (Ed) wrote in message news:<5bb04c8c.0305131325.3ea6f59a_at_posting.google.com>...
> Can someone explain why I am getting ORA-06510 errors from this code
> snippet? I have the appropriate privileges to read from this directory
> (I think).
>
> Thanks In Advance,
> Ed
>
> SQL> list
> 1 create or replace procedure tp2UpdateCodes
> 2 is
> 3 v_line varchar2(128);
> 4 c_location constant varchar2(80) := '/tmp/';
> 5 c_filename constant varchar2(80) := 'tpcodes.txt';
> 6 v_handle Utl_File.File_Type := NULL;
> 7 begin
> 8 v_handle := UTL_FILE.FOPEN ( c_location,
> 9 c_filename,
> 10 'r',
> 11 32767 );
> 12 loop
> 13 Utl_File.Get_Line ( file => v_handle,
> 14 buffer => v_line );
> 15 Dbms_Output.Put_Line ( v_line || '--' );
> 16 end loop;
> 17 Utl_File.Fclose ( file => v_handle );
> 18 exception
> 19 when no_data_found then null;
> 20* end tp2UpdateCodes;
> SQL> execute tp2UpdateCodes
> BEGIN tp2UpdateCodes; END;
>
> *
> ERROR at line 1:
> ORA-06510: PL/SQL: unhandled user-defined exception
> ORA-06512: at "SYS.UTL_FILE", line 120
> ORA-06512: at "SYS.UTL_FILE", line 293
> ORA-06512: at "ICCONTST2.TP2UPDATECODES", line 8
> ORA-06512: at line 1
Few things,
- If you are using dbms_output.put_line in a procedure, then call
dbms_output.enable(1000000) before your loop starts. Max is 1000000.
By default it is set to minimum 2000.
- Increase the size of v_line buffer in get_line from 128 to some
higher value. It may not be large enough to hold output data.
- Check permissions on directory. Make sure oracle has at least read
access to it.
- Make sure c_location value is initialized in utl_file_dir parameter
in init.ora file.
- If you are only catching one exception... no_data_found in your
case, then assume that other exceptions will be raised. Either handle
other exceptions or use when others then.
- It is always a good idea to explicitly check for and close file
handle in an exception clause. Use IF (UTL_FILE.IS_OPEN(v_handle))
THEN ...
Regards
/Rauf Sarwar
Received on Tue May 13 2003 - 20:44:32 CDT
