Re: Problems to create text files

From: Sean M. Dillon <sdillon_at_remove.this.us.oracle.com>
Date: Fri, 19 Nov 1999 07:40:37 -0500
Message-ID: <YD81OAKA6BffXWXf9dK5UYJXN7sa@4ax.com>

<DESC UTL_FILE SNIP>
FUNCTION FOPEN RETURNS RECORD

Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ 
 ID                             BINARY_INTEGER          OUT
 LOCATION                       VARCHAR2                IN
 FILENAME                       VARCHAR2                IN
 OPEN_MODE                      VARCHAR2                IN

</DESC UTL_FILE SNIP>

Valid OPEN_MODES are as follows:

'r' read text (GET_LINE)
'w' write text (PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH)
'a' append text (PUT, PUT_LINE, NEW_LINE, PUTF, FFLUSH)

Try using 'w' instead of 'r'. Also, there's a multitude of exceptions that are thrown by UTL_FILE. If you're trying to debug a piece of UTL_FILE code, try checking for all of them. It might get you to a resolution a little quicker...

declare these:

g_invalid_path_msg constant varchar2(255) default 'INVALID_PATH: File location or filename was invalid.'; g_invalid_mode_msg constant varchar2(255) default 'INVALID_MODE: The open_mode parameter in FOPEN was invalid.'; g_invalid_filehandle_msg constant varchar2(255) default 'INVALID_FILEHANDLE: The file handle was invalid.'; g_invalid_operation_msg constant varchar2(255) default 'INVALID_OPERATION: The file could not be opened or operated on as requested.';
g_read_error_msg constant varchar2(255) default 'READ_ERROR: An operating system error occurred during the read operation.';
g_write_error_msg constant varchar2(255) default 'WRITE_ERROR: An operating system error occurred during the write operation.';
g_internal_error_msg constant varchar2(255) default 'INTERNAL_ERROR: An unspecified error in PL/SQL.';

and use this exception block:

exception

    when utl_file.invalid_path then
        raise_application_error(-20001,g_invalid_path_msg);
    when utl_file.invalid_mode then
        raise_application_error(-20002,g_invalid_mode_msg);
    when utl_file.invalid_filehandle then
        raise_application_error(-20002,g_invalid_filehandle_msg);
    when utl_file.invalid_operation then
        raise_application_error(-20003,g_invalid_operation_msg);
    when utl_file.read_error then
        raise_application_error(-20004,g_read_error_msg);
    when utl_file.write_error then
        raise_application_error(-20005,g_write_error_msg);
    when utl_file.internal_error then
        raise_application_error(-20006,g_internal_error_msg);

Hope this helps!

_smd_

Best rgds,

---

ORACLE                         Sean Dillon
                               Technologist
                               Oracle Service Industries

Oracle Corporation             Phone: 703.364.3119

1910 Oracle Way                Cell: 727.515.0701 Reston, VA 20190               Email: sdillon_at_us.oracle.com <mailto:sdillon_at_us.oracle.com>

---

On Fri, 19 Nov 1999 12:32:27 +0100, "Jean-Christophe MARTIN" <dr_jcm_at_hotmail.com> wrote:

>Hello!
>
>We've got here a little problem in creating text files within an Oracle
>procedure using the fOpen command.
>
>Running the following simple code, the answer is always 'invalid path'.
>
>CREATE OR REPLACE PROCEDURE testp is
>fileid utl_file.file_type ;
>begin
>fileid := utl_file.fopen('C:\Temp', 'test.sql', 'r') ;
>utl_file.put_line(fileid,'on est la') ;
>utl_file.fclose(fileid) ;
>
>exception
>when utl_file.invalid_path then
>DBMS_OUTPUT.put_line('invalid path') ;
>Raise ;
>End;
>
>We have made sure than the parameter 'utl_file_dir=*' is present in the
>'initorcl.ora' file (with the assistance of the Oracle HotLine)
>The problem is present on the Oracle server (PC) itself, so it cannot be an
>user authorization or net default.
>
>If you have answers, please mail them to dr_jcm_at_hotmail.com.
>
>Thanks a lot
>
Received on Fri Nov 19 1999 - 06:40:37 CST