ANBOB™

数据库创建的JOB通常是在调用的对象如存储过程中编写SQL insert记录日志表来跟踪运行结果，但是同样需要考虑一个问题就是写日志的事务提交是否影响了存储过程中的逻辑结构，甚至我喜欢在PROCEDURE中增加一些DBMS_OUTPUT输出调试过程，但是只有在控制台运行才可以看到输出， 在11g及之前的版本中使用DBMS_SCHEDLER创建的JOB已经增加了DBA_SCHEDULER_JOB_RUN_DETAILS 视图可以记录一些运行的日志信息如error#，在12C的版本中再增强，同样记录了procedure中使用的DBMS_OUTPUT的输出和ERROR的具体文本， 这里记录一个简单的例子.

[oracle@anbob ~]$ sqlplus anbob/anbob 

SQL*Plus: Release 12.2.0.0.0 Beta on Thu Sep 29 14:22:44 2016
Copyright (c) 1982, 2015, Oracle.  All rights reserved.
Last Successful login time: Thu Sep 29 2016 14:20:55 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.0.1 - 64bit Beta
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

anbob@pdborcl:orcl> show con_name
CON_NAME
------------------------------
PDBORCL

anbob@pdborcl:orcl> create or replace procedure p_do is
  2   begin
  3     dbms_output.put_line('step 1');
  4     dbms_lock.sleep(5);
  5     dbms_output.put_line('step 2');
  6     dbms_lock.sleep(5);
  7     dbms_output.put_line('step 3');
  8   end;
  9   /
Procedure created.

anbob@pdborcl:orcl> set serveroutput on
anbob@pdborcl:orcl> exec p_do;
step 1
step 2
step 3
PL/SQL procedure successfully completed.

anbob@pdborcl:orcl>  begin
  2        dbms_scheduler.create_job (
  3           job_name           =>  'pdo',
  4           job_type           =>  'STORED_PROCEDURE',
  5           job_action         =>  'p_do',
  6           start_date         =>  sysdate,
  7           enabled            =>  true,
  8           comments           =>  'test output in proc');
  9    end;
 10    /

PL/SQL procedure successfully completed.

Note:
上面创建了简单的存储过程使用DBMS_OUTPUT输出几行记录，并创建了一次性的JOB。oracle 创建只运行一次的job 自动删除

sys@cdb$root:orcl> @desc DBA_SCHEDULER_JOB_RUN_DETAILS
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      LOG_ID                          NOT NULL NUMBER
    2      LOG_DATE                                 TIMESTAMP(6) WITH TIME ZONE
    3      OWNER                                    VARCHAR2(128)
    4      JOB_NAME                                 VARCHAR2(261)
    5      JOB_SUBNAME                              VARCHAR2(261)
    6      STATUS                                   VARCHAR2(30)
    7      ERROR#                                   NUMBER
    8      REQ_START_DATE                           TIMESTAMP(6) WITH TIME ZONE
    9      ACTUAL_START_DATE                        TIMESTAMP(6) WITH TIME ZONE
   10      RUN_DURATION                             INTERVAL DAY(3) TO SECOND(0)
   11      INSTANCE_ID                              NUMBER
   12      SESSION_ID                               VARCHAR2(128)
   13      SLAVE_PID                                VARCHAR2(30)
   14      CPU_USED                                 INTERVAL DAY(3) TO SECOND(2)
   15      CREDENTIAL_OWNER                         VARCHAR2(261)
   16      CREDENTIAL_NAME                          VARCHAR2(261)
   17      DESTINATION_OWNER                        VARCHAR2(261)
   18      DESTINATION                              VARCHAR2(261)
   19      ADDITIONAL_INFO                          VARCHAR2(4000)
   20      ERRORS                                   VARCHAR2(4000)
   21      OUTPUT                                   VARCHAR2(4000)
   22      BINARY_ERRORS                            BLOB
   23      BINARY_OUTPUT                            BLOB
   
   
# 11g release 2
SQL> @desc DBA_SCHEDULER_JOB_RUN_DETAILS
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      LOG_ID                                   NUMBER
    2      LOG_DATE                                 TIMESTAMP(6) WITH TIME ZONE
    3      OWNER                                    VARCHAR2(30)
    4      JOB_NAME                                 VARCHAR2(65)
    5      JOB_SUBNAME                              VARCHAR2(65)
    6      STATUS                                   VARCHAR2(30)
    7      ERROR#                                   NUMBER
    8      REQ_START_DATE                           TIMESTAMP(6) WITH TIME ZONE
    9      ACTUAL_START_DATE                        TIMESTAMP(6) WITH TIME ZONE
   10      RUN_DURATION                             INTERVAL DAY(3) TO SECOND(0)
   11      INSTANCE_ID                              NUMBER
   12      SESSION_ID                               VARCHAR2(30)
   13      SLAVE_PID                                VARCHAR2(30)
   14      CPU_USED                                 INTERVAL DAY(3) TO SECOND(2)
   15      CREDENTIAL_OWNER                         VARCHAR2(65)
   16      CREDENTIAL_NAME                          VARCHAR2(65)
   17      DESTINATION_OWNER                        VARCHAR2(128)
   18      DESTINATION                              VARCHAR2(128)
   19      ADDITIONAL_INFO                          VARCHAR2(4000)

NOTE: 12C DBA_SCHEDULER_JOB_RUN_DETAILS的视图中比11G 增加了4列，2种信息，OUTPUT 和ERROR文本，和V$SQL视图一样，同样一种是VARCHAR2 4000的列，和对应的一列BLOB列。

anbob@pdborcl:orcl> COL OUTPUT FOR A30
anbob@pdborcl:orcl> COL OWNER FOR A30

anbob@pdborcl:orcl> select OWNER,JOB_NAME,OUTPUT
  2      from DBA_SCHEDULER_JOB_RUN_DETAILS
  3          where job_name = 'PDO';


OWNER                          JOB_NAME             OUTPUT
------------------------------ -------------------- ------------------------------
ANBOB                          PDO                  step 1
                                                    step 2
                                                    step 3



anbob@pdborcl:orcl> col outblob for a100
anbob@pdborcl:orcl>select utl_raw.cast_to_varchar2( dbms_lob.substr( BINARY_OUTPUT, 32000, 1 ) ) outblob,BINARY_OUTPUT
  2       from DBA_SCHEDULER_JOB_RUN_DETAILS
  3*     where job_name = 'PDO'
anbob@pdborcl:orcl> /

OUTBLOB                        BINARY_OUTPUT
------------------------------ ----------------------------------------------------------------------------------------------------
step 1                         7374657020310A7374657020320A737465702033
step 2
step 3

step 1                         7374657020310A7374657020320A737465702033
step 2
step 3

NOTE:
对于OUTPUT和ERROR文本超过4000会自动截断，从BINARY_OUTPUT列取出全文本。 ERROR不再演示。