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.
复制