11.2.0.3更改AWR報告底層TYPE型別

在11.2.0.2中讀取AWR報告的過程在11.2.0.3中報錯。

由於過程太長，將關鍵部分簡化，分別在10.2和11.2.0.3中執行：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 – Production

SQL> DECLARE
  2     V_DBID NUMBER;
  3     V_BEGIN NUMBER;
  4     V_END NUMBER;
  5     TYPE T_VARCHAR IS TABLE OF VARCHAR2(1500 CHAR) INDEX BY BINARY_INTEGER;
  6     V_REPORT T_VARCHAR;
  7  BEGIN
  8     SELECT A.DBID, MAX(SNAP_ID - 1), MAX(SNAP_ID)
  9     INTO V_DBID, V_BEGIN, V_END
 10     FROM DBA_HIST_SNAPSHOT A, V$DATABASE B
 11     WHERE A.DBID = B.DBID
 12     GROUP BY A.DBID;
 13     SELECT OUTPUT
 14     BULK COLLECT INTO V_REPORT
 15     FROM TABLE(
 16             DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
 17                     V_DBID,
 18                     1,
 19                     V_BEGIN,
 20                     V_END,
 21                     0));
 22  END;
 23  /

PL/SQL procedure successfully completed.

10204上執行沒有任何問題，但是在11.2.0.3中：

SQL> SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 – Production

SQL> DECLARE
  2     V_DBID NUMBER;
  3     V_BEGIN NUMBER;
  4     V_END NUMBER;
  5     TYPE T_VARCHAR IS TABLE OF VARCHAR2(1500 CHAR) INDEX BY BINARY_INTEGER;
  6     V_REPORT T_VARCHAR;
  7  BEGIN
  8     SELECT A.DBID, MAX(SNAP_ID - 1), MAX(SNAP_ID)
  9     INTO V_DBID, V_BEGIN, V_END
 10     FROM DBA_HIST_SNAPSHOT A, V$DATABASE B
 11     WHERE A.DBID = B.DBID
 12     GROUP BY A.DBID;
 13     SELECT OUTPUT
 14     BULK COLLECT INTO V_REPORT
 15     FROM TABLE(
 16             DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
 17                     V_DBID,
 18                     1,
 19                     V_BEGIN,
 20                     V_END,
 21                     0));
 22  END;
 23  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 919
ORA-06512: at line 1
ORA-06512: at line 13

同樣的程式碼在不同的版本中表現不同，很顯然是Oracle的實現發生的變化。

從錯誤資訊看，報錯出現在BULK COLLECT INTO上，那麼導致問題的應該是函式的返回值。

SQL> DESC DBMS_WORKLOAD_REPOSITORY
FUNCTION ASH_REPORT_HTML RETURNS AWRRPT_HTML_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 L_DBID                         NUMBER                  IN
 L_INST_NUM                     NUMBER                  IN
 L_BTIME                        DATE                    IN
 L_ETIME                        DATE                    IN
 L_OPTIONS                      NUMBER                  IN     DEFAULT
 L_SLOT_WIDTH                   NUMBER                  IN     DEFAULT
 L_SID                          NUMBER                  IN     DEFAULT
 L_SQL_ID                       VARCHAR2                IN     DEFAULT
 L_WAIT_CLASS                   VARCHAR2                IN     DEFAULT
 L_SERVICE_HASH                 NUMBER                  IN     DEFAULT
 L_MODULE                       VARCHAR2                IN     DEFAULT
 L_ACTION                       VARCHAR2                IN     DEFAULT
 L_CLIENT_ID                    VARCHAR2                IN     DEFAULT
 L_PLSQL_ENTRY                  VARCHAR2                IN     DEFAULT
.
.
.
FUNCTION AWR_REPORT_HTML RETURNS AWRRPT_HTML_TYPE_TABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 L_DBID                         NUMBER                  IN
 L_INST_NUM                     NUMBER                  IN
 L_BID                          NUMBER                  IN
 L_EID                          NUMBER                  IN
 L_OPTIONS                      NUMBER                  IN     DEFAULT
.
.
.
PROCEDURE MODIFY_SNAPSHOT_SETTINGS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RETENTION                      NUMBER                  IN     DEFAULT
 INTERVAL                       NUMBER                  IN     DEFAULT
 TOPNSQL                        VARCHAR2                IN
 DBID                           NUMBER                  IN     DEFAULT

返回結果為AWRRPT_HTML_TYPE_TABLE型別，查詢TYPE型別獲取詳細資訊：

SQL> SET LONG 10000
SQL> SELECT DBMS_METADATA.GET_DDL('TYPE', 'AWRRPT_HTML_TYPE_TABLE') FROM DUAL;

DBMS_METADATA.GET_DDL('TYPE','AWRRPT_HTML_TYPE_TABLE')
--------------------------------------------------------------------------------

  CREATE OR REPLACE TYPE "SYS"."AWRRPT_HTML_TYPE_TABLE"
  as table of AWRRPT_HTML_TYPE

SQL> SELECT DBMS_METADATA.GET_DDL('TYPE', 'AWRRPT_HTML_TYPE') FROM DUAL;

DBMS_METADATA.GET_DDL('TYPE','AWRRPT_HTML_TYPE')
--------------------------------------------------------------------------------

  CREATE OR REPLACE TYPE "SYS"."AWRRPT_HTML_TYPE"
  as object (output varchar2(1500 CHAR))

可以看到10.2.0.4中，或者說在11.2.0.3以前的版本，TYPE的定義長度是1500 CHAR，而在11.2.0.3中定義變成：

SQL> SET LONG 10000
SQL> SELECT DBMS_METADATA.GET_DDL('TYPE', 'AWRRPT_HTML_TYPE') FROM DUAL;

DBMS_METADATA.GET_DDL('TYPE','AWRRPT_HTML_TYPE')
--------------------------------------------------------------------------------

  CREATE OR REPLACE TYPE "SYS"."AWRRPT_HTML_TYPE"
  as object (output varchar2(8000 CHAR))

顯然RETURN型別的長度變化導致了這個問題，根據Oracle定義的變化簡單修改程式碼，可以避免11.2.0.3上錯誤的產生：

SQL> DECLARE
  2     V_DBID NUMBER;
  3     V_BEGIN NUMBER;
  4     V_END NUMBER;
  5     TYPE T_VARCHAR IS TABLE OF VARCHAR2(8000 CHAR) INDEX BY BINARY_INTEGER;
  6     V_REPORT T_VARCHAR;
  7  BEGIN
  8     SELECT A.DBID, MAX(SNAP_ID - 1), MAX(SNAP_ID)
  9     INTO V_DBID, V_BEGIN, V_END
 10     FROM DBA_HIST_SNAPSHOT A, V$DATABASE B
 11     WHERE A.DBID = B.DBID
 12     GROUP BY A.DBID;
 13     SELECT OUTPUT
 14     BULK COLLECT INTO V_REPORT
 15     FROM TABLE(
 16             DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
 17                     V_DBID,
 18                     1,
 19                     V_BEGIN,
 20                     V_END,
 21                     0));
 22  END;
 23  /

PL/SQL procedure successfully completed.