Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    41

    Unanswered: UTL_FILE - mapped netword directory

    I am using oracle 7.3 with win2k

    I am able to add utl_file_dir in init.ora file. I have
    added c:\test, d:\test in the init.ora file and i am
    able to create a xml files in both the directries(using UTL_FILE).

    How ever when i try to add g:\test (network directory,
    which is nothing but my pc. I have mapped this
    directory in the oracle server thro windows explorer)

    I can see all these files in (c:\test. d:\test,g:\test) in

    select value from v$parameter where name='utl_file_dir'

    If i try to write a file in the g:\test, thro oracle procedure i get an error saying that

    UTL_FILE.INVALID_OPERATION

    Now i want to know, how to add this network directory
    and is there anything that i have to do in ORACLE 7.3

    I have added the procedure below

    Thanks


    CREATE OR REPLACE procedure "test"
    as

    iFILE_HANDLE UTL_FILE.FILE_TYPE;
    i BINARY_INTEGER;

    begin

    i := DBMS_RANDOM.RANDOM;
    iFILE_HANDLE := UTL_FILE.fopen('g:\test','test' || TO_CHAR(SYSDATE,'DD-MM-YY-HH-MI-SS') || '.XML','W');

    UTL_FILE.PUT(iFILE_HANDLE, '<?xml version="1.0" ?>');
    UTL_FILE.PUT(iFILE_HANDLE,'<ROOT>');

    UTL_FILE.PUT(iFILE_HANDLE,'<abc>');
    UTL_FILE.put(iFile_Handle,'abc');
    UTL_FILE.PUT(iFILE_HANDLE,'</abc>');

    UTL_FILE.PUT(IFILE_HANDLE,'</ROOT>');
    UTL_FILE.FCLOSE(ifile_handle);

    EXCEPTION

    WHEN utl_file.invalid_path THEN
    DBMS_OUTPUT.PUT_LINE('invalid path');
    UTL_FILE.FCLOSE(IFILE_HANDLE);
    WHEN utl_file.invalid_mode THEN
    DBMS_OUTPUT.PUT_LINE('invalid mode');
    UTL_FILE.FCLOSE(IFILE_HANDLE);
    WHEN utl_file.invalid_filehandle THEN
    DBMS_OUTPUT.PUT_LINE('invalid filehandle');
    UTL_FILE.FCLOSE(IFILE_HANDLE);
    WHEN utl_file.invalid_operation THEN
    DBMS_OUTPUT.PUT_LINE('invalid operation');
    UTL_FILE.FCLOSE(IFILE_HANDLE);
    WHEN utl_file.read_error THEN
    DBMS_OUTPUT.PUT_LINE('read error');
    UTL_FILE.FCLOSE(IFILE_HANDLE);
    WHEN utl_file.write_error THEN
    DBMS_OUTPUT.PUT_LINE('write error');
    UTL_FILE.FCLOSE(IFILE_HANDLE);
    WHEN utl_file.internal_error THEN
    DBMS_OUTPUT.PUT_LINE('internal error');
    UTL_FILE.FCLOSE(IFILE_HANDLE);
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('other error');
    UTL_FILE.FCLOSE(IFILE_HANDLE);

    END;
    /

  2. #2
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: UTL_FILE - mapped netword directory

    One of the restriction that UTL_FILE package has is that the folder or path must me part of the same OS.
    Joel Pérez

  3. #3
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: UTL_FILE - mapped netword directory

    Here you all reference of this package:

    UTL_FILE
    With the UTL_FILE package, your PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.

    UTL_FILE I/O capabilities are similar to standard operating system stream file I/O (OPEN, GET, PUT, CLOSE) capabilities, but with some limitations. For example, you call the FOPEN function to return a file handle, which you use in subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When file I/O is done, you call FCLOSE to complete any output and free resources associated with the file.

    http://download-west.oracle.com/docs...e.htm#ARPLS069

    I guess that create directories with CREATE DIRECTORY can be an option. Really I do not know if CREATE DIRECTORY feature is available for Oracle7. Find out that information or try to create a directory.

    I have created directories in folders of the same OS and I have not had problems but I have not tested with mapped folders.
    Joel Pérez

  4. #4
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: UTL_FILE - mapped netword directory

    Examples (UNIX-Specific)
    Given the following:

    SQL> CREATE DIRECTORY log_dir AS '/appl/gl/log';
    SQL> GRANT READ ON DIRECTORY log_dir TO DBA;

    SQL> CREATE DIRECTORY out_dir AS '/appl/gl/user'';
    SQL> GRANT READ ON DIRECTORY user_dir TO PUBLIC;

    So do this:

    1.- Try to create a directory with sentences alike like appears above.
    2.- Try to run your procedure
    3.- If neither of last option functions you can set the init parameter UTL_FILE_DIR=* and test it again.
    Joel Pérez

  5. #5
    Join Date
    Feb 2004
    Location
    Buenos Aires, Argentina
    Posts
    5

    Re: UTL_FILE - mapped netword directory

    [QUOTE][SIZE=1]Originally posted by jtamil2001

    Check out W2K Service for your Oracle Instance.
    Go to Login options and see if it is using Local System logon. The local system user has no rights to any shares. So, change it to logon using your account or any account that has access to shares.

    Anoher tip: don't use logical drive letter (G, use UNC full name (like \\xxx\xx\xxx) on INITxxx.ORA and FOpen.

    good luck.

  6. #6
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    I agree with Pablo. However, must add one thing... since Windows does not actually connect to the share without a physical account logged into the local machine, Oracle won't be able to see the share either. We've found that we had to keep the console logged in, but locked in order to have the connection kept alive.

    JoeB

Posting Permissions

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