Re: PL/SQL and writing files

Looks like the error occurs inside the utl_file.put_line:
> UTL_FILE.PUT_LINE(file_handle, time_stamp || ' [Procedure: write_log]
> --> This is a test to log info to a file.');
where you can't debug it directly (since the package body of util_file is hidden). I glean that your procedure is called WWW_USER.WRITE_LOG. Your exception handler doesn't handle all of the exceptions that put_line might return, namely:

• invalid_filehandle - not a valid file handle
• invalid_operation - file is not open for writing/appending
• write_error - OS error occured during write operation Try coding a WHEN OTHERS THEN clause in your exception handler (you should always have one of these anyway, unless you really want the exception to bubble up to the calling context). Display the SQLCODE value (trap it first in a NUMBER variable, then use the variable in SQLERRM). You won't know until you find out, but my guess is a write_error. -djc

Bryan J. Gentile <bgentile_at_bdsinc.com> wrote in article <3456896C.19F680D2_at_bdsinc.com>...
> I am having trouble writing a log file to a server using PL/SQL. The
> following is my code for my stored procedure:
> IS
> file_handle UTL_FILE.FILE_TYPE;
> time_stamp varchar2(20);
> BEGIN
> DBMS_OUTPUT.PUT_LINE('About to open log file...');
> file_handle := UTL_FILE.FOPEN('/log/', 'SPErrors.log', 'A');
> SELECT TO_CHAR(sysdate, 'MMDDYY HH:MI')
> INTO time_stamp
> FROM dual;
> UTL_FILE.PUT_LINE(file_handle, time_stamp || ' [Procedure: write_log]
> --> This is a test to log info to a file.');
> UTL_FILE.NEW_LINE(file_handle);
> DBMS_OUTPUT.PUT_LINE('Message logged...');
> UTL_FILE.FCLOSE(file_handle);
> DBMS_OUTPUT.PUT_LINE('Closing LogFile...');
> EXCEPTION
> WHEN UTL_FILE.INVALID_OPERATION THEN
> DBMS_OUTPUT.PUT_LINE('Error with UTL_FILE');
> UTL_FILE.FCLOSE(file_handle);
> END;
> Everytime I run it in SQL-PLUS, I get the following error:
> ERROR at line 1:
> ORA-06510: PL/SQL: unhandled user-defined exception
> ORA-06512: at "SYS.UTL_FILE", line 82
> ORA-06512: at "SYS.UTL_FILE", line 120
> ORA-06512: at "WWW_USER.WRITE_LOG", line 8
> ORA-06512: at line 1
> What does this mean, and how can I fix this?
Received on Wed Oct 29 1997 - 00:00:00 CST