Oracle 9i, utl_file_dir and CREATE DIRECTORY - problematic
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Oracle 9i, utl_file_dir and CREATE DIRECTORY - problematic

  1. #1
    Join Date
    Feb 2002
    Posts
    14

    Oracle 9i, utl_file_dir and CREATE DIRECTORY - problematic

    Hello,

    I need to write a log file to my linux filsystem. I had this going on Oracle 8i but Oracle 9i is proving problematic...I have done the following...

    1) Tried the new way

    CREATE DIRECTORY utl_file_logs AS '/path/to/logdir'#
    GRANT READ ON DIRECTORY TO user;
    GRANT WRITE ON DIRECTORY TO user;

    Then;

    PROCEDURE debug2(p_Debug VARCHAR2) IS

    v_File UTL_FILE.FILE_TYPE;

    BEGIN

    v_File := UTL_FILE.FOPEN('utl_file_logs', 'test.txt', 'A', 2000);
    UTL_FILE.PUT_LINE(v_File, p_Debug);
    UTL_FILE.FCLOSE(v_File);

    END debug2;

    Giving...

    SQL> exec pkgname.debug2('test');
    BEGIN pkgname.debug2('test'); END;

    *
    ERROR at line 1:
    ORA-29280: invalid directory path
    ORA-06512: at "SYS.UTL_FILE", line 18
    ORA-06512: at "SYS.UTL_FILE", line 424
    ORA-06512: at "SCHEMANAME.PKGNAME", line 12
    ORA-06512: at line 1

    2) Tried the old way

    ALTER SYSTEM SET utl_file_dir = '/path/to/logdir' SCOPE=SPFILE

    rebooted instance for luck.

    Using a slightly difference debug procedure gives me;

    SQL> exec ugoLogging.debug('test');
    BEGIN pkgname.debug('test'); END;

    *
    ERROR at line 1:
    ORA-29283: invalid file operation
    ORA-06512: at "SYS.UTL_FILE", line 18
    ORA-06512: at "SYS.UTL_FILE", line 439
    ORA-29283: invalid file operation
    ORA-06512: at "SCHEMENAME.PKGNAME", line 37
    ORA-06512: at line 1

    ==================
    SO getting somewhere with the second. So I figured permissions...

    did a CHMOD 777 /path/to/logdir

    STILL no luck


    Help!?

  2. #2
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

    Re: Oracle 9i, utl_file_dir and CREATE DIRECTORY - problematic

    Look for error in your ugoLogging.debug('test') function.

    Originally posted by adcworks

    1)
    ORA-29280: invalid directory path
    ORA-06512: at "SYS.UTL_FILE", line 18
    ORA-06512: at "SYS.UTL_FILE", line 424
    ORA-06512: at "SCHEMANAME.PKGNAME", line 12
    ORA-06512: at line 1

    2) Tried the old way

    ALTER SYSTEM SET utl_file_dir = '/path/to/logdir' SCOPE=SPFILE

    rebooted instance for luck.

    Using a slightly difference debug procedure gives me;

    SQL> exec ugoLogging.debug('test');
    BEGIN pkgname.debug('test'); END;

    *
    ERROR at line 1:
    ORA-29283: invalid file operation
    ORA-06512: at "SYS.UTL_FILE", line 18
    ORA-06512: at "SYS.UTL_FILE", line 439
    ORA-29283: invalid file operation
    ORA-06512: at "SCHEMENAME.PKGNAME", line 37
    ORA-06512: at line 1

    ==================
    Best wishes!
    Dmitri

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    alter system SET utl_file_dir will be lost when you reboot your instance, if you want it to remain when you bounce our instance, will need to put it in init.ora

    also you cant to alter system set utl_file_dir .... in 8i

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by davey23uk
    [B]alter system SET utl_file_dir will be lost when you reboot your instance...
    No, it won't, since he used "... SCOPE=SPFILE" in his ALTER SYSTEM command.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Sep 2001
    Posts
    120
    alter system set utl_file_dir =* scope=spfile;

    reboot the instance;
    and try to run the procedure with full path to the log file.


    CREATE DIRECTORY utl_file_logs AS '/path/to/logdir'#
    you can't have your directory accesed.oracle docs say's
    The file location must be an accessible directory, as defined in the instance's initialization parameter UTL_FILE_DIR.
    Saurabh Garg
    OCP 9i

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