Problem using UTL_FILE (difference script/procedure)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Problem using UTL_FILE (difference script/procedure)

  1. #1
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    Problem using UTL_FILE (difference script/procedure)

    Hello.
    I have a problem with the following script:

    DECLARE
    fid UTL_FILE.FILE_TYPE;
    v VARCHAR2(32767);

    PROCEDURE recNgo (str IN VARCHAR2)
    IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE ('UTL_FILE error ' || str);

    UTL_FILE.FCLOSE (fid);
    END;
    BEGIN
    /* Change the directory name to one to which you at least
    || THINK you have read/write access.
    */
    fid := UTL_FILE.FOPEN ('c:\temp', 'source.txt', 'R');
    UTL_FILE.GET_LINE (fid, v);
    dbms_output.put_line (v);
    UTL_FILE.FCLOSE (fid);

    fid := UTL_FILE.FOPEN ('c:\temp', 'destin.txt', 'W');
    UTL_FILE.PUT_LINE (fid, v);
    UTL_FILE.FCLOSE (fid);
    EXCEPTION
    WHEN UTL_FILE.INVALID_PATH
    THEN recNgo ('invalid_path');
    WHEN UTL_FILE.INVALID_MODE
    THEN recNgo ('invalid_mode');
    WHEN UTL_FILE.INVALID_FILEHANDLE
    THEN recNgo ('invalid_filehandle');
    WHEN UTL_FILE.INVALID_OPERATION
    THEN recNgo ('invalid_operation');
    WHEN UTL_FILE.READ_ERROR
    THEN recNgo ('read_error');
    WHEN UTL_FILE.WRITE_ERROR
    THEN recNgo ('write_error');
    WHEN UTL_FILE.INTERNAL_ERROR
    THEN recNgo ('internal_error');
    END;
    /

    (it copies the data from an existing source.txt to destin.txt in the c:\temp directory on the server)

    Problem: If I save this script into a file ('utltest.sql') and I run it from SQLPLUS: @utltest; then the script runs without errors and does what it suppose to. GREAT!
    BUT: if I transform this script into a stored procedure and I start it, it generates the exception UTL_FILE.INVALID_PATH. I trace it via TOAD.

    Oracle version: 8.1.7.1.1
    The init.ora contains the UTL_FILE_DIR-parameter with the single directory: c:\temp (no quotes)


    The procedure:
    CREATE OR REPLACE PROCEDURE copy_server_file
    IS
    --DECLARE
    fid SYS.UTL_FILE.FILE_TYPE;
    v VARCHAR2(32767);

    PROCEDURE recNgo (str IN VARCHAR2)
    IS
    BEGIN
    DBMS_OUTPUT.PUT_LINE ('UTL_FILE error ' || str);

    UTL_FILE.FCLOSE (fid);
    END;
    BEGIN
    /* Change the directory name to one to which you at least
    || THINK you have read/write access.
    */
    fid := UTL_FILE.FOPEN ('c:\temp', 'source.txt', 'R');
    UTL_FILE.GET_LINE (fid, v);
    dbms_output.put_line (v);
    UTL_FILE.FCLOSE (fid);

    fid := UTL_FILE.FOPEN ('c:\temp', 'destin.txt', 'W');
    UTL_FILE.PUT_LINE (fid, v);
    UTL_FILE.FCLOSE (fid);
    EXCEPTION
    WHEN UTL_FILE.INVALID_PATH
    THEN recNgo ('invalid_path');
    WHEN UTL_FILE.INVALID_MODE
    THEN recNgo ('invalid_mode');
    WHEN UTL_FILE.INVALID_FILEHANDLE
    THEN recNgo ('invalid_filehandle');
    WHEN UTL_FILE.INVALID_OPERATION
    THEN recNgo ('invalid_operation');
    WHEN UTL_FILE.READ_ERROR
    THEN recNgo ('read_error');
    WHEN UTL_FILE.WRITE_ERROR
    THEN recNgo ('write_error');
    WHEN UTL_FILE.INTERNAL_ERROR
    THEN recNgo ('internal_error');
    END;
    /

    Thanks in advance!

  2. #2
    Join Date
    Dec 2000
    Posts
    138
    UTL_FILE.FOPEN ('c:\temp', 'source.txt', 'R');
    change the 'c:\temp' to 'c:\temp\'

    -dharma

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