tech_memo / ORACLE / PLSQL

tech_memo/ORACLE

ログ出力 †

• あらかじめDIRECTORYオブジェクトを作成しておく

  CREATE DIRECTORY HOGE_LOG_DIR AS '/var/log/oracle';
  GRANT READ ON DIRECTORY HOGE_LOG_DIR TO user_name ;
  GRANT WRITE ON DIRECTORY HOGE_LOG_DIR TO user_name ;
  -- 確認
  SELECT * FROM ALL_DIRECTORIES ;

• Procedure

  CREATE OR REPLACE
  PACKAGE BODY TEST_LOGGER
  AS
  PROCEDURE INFO (MSG VARCHAR2)
  IS
     FILE_HANDLE  UTL_FILE.FILE_TYPE;
  BEGIN
             FILE_HANDLE := UTL_FILE.FOPEN(
                                       'TEST_LOG_DIR'
                                     , 'hoge.log'
                                     , 'A'  
                                  , 32767
                          );
          
          UTL_FILE.PUT_LINE(
                    FILE_HANDLE
                  , to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS')
                  || '   ' || 'INFO'
                  || '   ' || MSG
              );
  
     UTL_FILE.FCLOSE(FILE_HANDLE);
  
  EXCEPTION
     WHEN OTHERS THEN
         UTL_FILE.FCLOSE_ALL;
         RAISE;
  END INFO;
  
  END TEST_LOGGER;
  /

• 参考
  • http://d.hatena.ne.jp/hisaboh/20080327/p1