watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=


Q          题目如下所示:

在Oracle中,如何在Oracle中写操作系统文件,如写日志?


     
A          答案如下所示:          


可以利用UTL_FILE包,但是,在此之前,要注意设置好UTL_FILE_DIR初始化参数。

CREATE OR REPLACE PROCEDURE SP_WRITE_LOG(TEXT_CONTEXT VARCHAR2) IS

  FILE_HANDLE     UTL_FILE.FILE_TYPE;

  WRITE_CONTENT   VARCHAR2(1024);

  WRITE_FILE_NAME VARCHAR2(50);

BEGIN

  --open file

  WRITE_FILE_NAME := 'db_alert.log';

  FILE_HANDLE     := UTL_FILE.FOPEN('/u01/logs', WRITE_FILE_NAME, 'a');

  WRITE_CONTENT   := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') || '||' ||TEXT_CONTEXT;

  --write file

  IF UTL_FILE.IS_OPEN(FILE_HANDLE) THEN

    UTL_FILE.PUT_LINE(FILE_HANDLE, WRITE_CONTENT);

  END IF;

  --close file

  UTL_FILE.FCLOSE(FILE_HANDLE);

EXCEPTION

  WHEN OTHERS THEN

    BEGIN

      IF UTL_FILE.IS_OPEN(FILE_HANDLE) THEN

        UTL_FILE.FCLOSE(FILE_HANDLE);

      END IF;

    EXCEPTION

      WHEN OTHERS THEN

        NULL;

    END;

END SP_WRITE_LOG;