Error running procedure using SYS.UTIL_FILE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Error running procedure using SYS.UTIL_FILE

  1. #1
    Join Date
    May 2003
    Posts
    4

    Angry Error running procedure using SYS.UTIL_FILE

    Hi guys,

    I have a stored procedure which copies a table from a remote database to a local one. If an error occurs during the copy it's supposed to write the error to a log file. However, it errors when writing to the error file.

    9i on W2K SP 2

    code follows:

    procedure COPY_WORKSITE
    IS

    fileHandler UTL_FILE.FILE_TYPE;
    v_from_table_name VARCHAR2( 30) := 'PWKS_WORKSITE';
    v_to_table_name VARCHAR2( 30) := 'QWPP_PPS_WORKSITE';
    v_backup_table_name VARCHAR2( 30) := 'BWPP_PPS_BACKUP';
    v_dblink VARCHAR2(30) := 'get_pwks';

    BEGIN


    snip

    EXCEPTION
    WHEN OTHERS THEN
    -- some kind of error,
    -- output error to log file
    fileHandler := UTL_FILE.FOPEN('LOG_FOLDER','oracle_error.log', 'W');
    UTL_FILE.PUTF(fileHandler, TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') ||'Error: '||sqlcode||' '||sqlerrm||'\n');
    UTL_FILE.FCLOSE(fileHandler);

    snip

    If the dblink is dropped to force an error the following errors occur:
    ERROR at line 1:
    ORA-29283: invalid file operation
    ORA-06512: at "SYS.UTL_FILE", line 449
    ORA-29283: invalid file operation
    ORA-06512: at "PPS_OWNER.COPY_WORKSITE", line 166
    ORA-02019: connection description for remote database not found
    ORA-06512: at line 1

    line 166 is the UTL_FILE.PUTF in my code above.
    Clearly the last error I would expect but not the previous ones.

    FYI the directory object LOG_FOLDER exists and points to a share on the server

    Any help in fixing this would be very much appreciated.

    Thanks
    Dave

  2. #2
    Join Date
    Aug 2003
    Posts
    16
    Make sure the Oracle User has privileges on the LOG_FOLDER -> Grant READ and WRITE access to LOG_FOLDER directory.

    2) Define an User defined exceptions to handle the scenario when the remote db link is not available or db is not up.
    Open file_handler to write the log file in this exception .

    When using UTL_FILE package, Handle all the in-built exceptions provided ..

    when utl_file.invalid_path then
    Log error;
    utl_file.fclose_all;
    when utl_file.invalid_mode then
    utl_file.fclose_all;
    Log error;
    when utl_file.invalid_filehandle then
    Log error;
    utl_file.fclose_all;
    when utl_file.invalid_operation then
    Log error;
    utl_file.fclose_all;
    when utl_file.read_error then
    Log error;
    utl_file.fclose_all;
    when utl_file.write_error then
    Log error;
    utl_file.fclose_all;
    when utl_file.internal_error then
    Log error;
    utl_file.fclose_all;
    when others then
    Log error;
    end;

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Error running procedure using SYS.UTIL_FILE

    Originally posted by pcfltd
    9i on W2K SP 2

    FYI the directory object LOG_FOLDER exists and points to a share on the server
    Under NT the Oracle services run by default under the NT user SYSTEM - SYSTEM can not use network devices. Is this still a problem with Win2K?

  4. #4
    Join Date
    May 2003
    Posts
    4
    The directory has full control for everyone so I'm fairly sure that's not the problem.

    DaPi - Sorry, didn't make that clear enuff. The share the directory object points to is a directory on the Oracle machine. So it should be a local directory for the database to write to.

    Thanks for the input guys, but I think I've already covered those bases. Any more ideas ?

    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width