1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

write into alert.log using UTL_FILE

Discussion in 'Security, Backup and Recovery' started by Sanjay MD, Jul 14, 2009.

  1. Sanjay MD

    Sanjay MD Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Mysore
    Hi All,

    Can you please guide me if its possible to wirte into alert.log file using utl_file package.

    I have tried UTL_FILE package for read/write operation of other text files present outside the the database directory.

    I get the following error

    SQL> DECLARE
    2 v_file_handle UTL_FILE.FILE_TYPE;
    3 BEGIN
    4 v_file_handle:=UTL_FILE.FOPEN('D:\oracle\admin\oracl\bdump','alert_oracl.log', 'A');
    5 UTL_FILE.PUT_LINE (v_file_handle, 'Test Data');
    6 UTL_FILE.FCLOSE (v_file_handle);
    7
    8 END;
    9 /
    DECLARE
    *
    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 line 4
     
  2. Kirti

    Kirti Forum Advisor

    Messages:
    46
    Likes Received:
    12
    Trophy Points:
    130
    You have a space in ora cl. Check.
     
  3. Sanjay MD

    Sanjay MD Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Mysore
    That just what is happening when I am pasting the code here. :)

    And now I figured out one more problem.

    When I say SHOW PARAMETERS utl_file,
    I am getting a constant value for the utl_file_dir parameter
    SQL> show parameter utl_file

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    utl_file_dir string D:\MD


    Now "MD" is the folder which I was using some months back to maintain some log. I have deleted this folder long back.
    When I try to change spfile, the parameter doesnt changes. And when I try to change pfile, I get error message.

    I think the things should work fine if i am able to change the value of "utl_file_dir" parameter

    Suggestion are most welcome
     
  4. Sanjay MD

    Sanjay MD Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Mysore
    Hi Friends,

    After brooding over problem for hrs I got the solution.

    The catch is that,

    Whenever I use the ALTER SYSTEM statement with SCOPE=SPFILE, the changes are not reflected on that moment. Hence the pfile still contains the old directory path, which might have been used earlier, in the utl_file_dir parameter.

    To make the changes reflect I have to restart that oracle DB service and then continue with the remaining steps for file procesing.

    Thanks!!!
     
  5. Kirti

    Kirti Forum Advisor

    Messages:
    46
    Likes Received:
    12
    Trophy Points:
    130
    Oh.. actually from your first post, there was nothing i could tell. I thought because of the space you are getting invalid path error. Good that you solved it.
     
  6. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Why do you want to write specifically to alert log ?
    Anyways, a more safer and easier way is :

     
    Sanjay MD likes this.