Oracle DBMS_REPORT
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose A framework for helping server components build XML from within the kernel
AUTHID CURRENT_USER
Constants
Name Data Type Value
CONTENT_TYPE_XML NUMBER 1
CONTENT_TYPE_HTML NUMBER 2
CONTENT_TYPE_TEXT NUMBER 3
CONTENT_TYPE_BINARY NUMBER 4
DATE_FMT VARCHAR2(21) 'mm/dd/yyyy hh24:mi:ss'
DATE_FMT_MOD VARCHAR2(21) 'mm:dd:yyyy hh24:mi:ss'
SHARED_DIRECTORY_OBJECT VARCHAR2(64) 'ORAREP_DIR'
Data Types TYPE format_param_value IS RECORD (
param_num   NUMBER,
param_value VARCHAR2(32767));

TYPE format_param_values IS TABLE OF format_param_value;

TYPE ref_string_idspec IS TABLE OF VARCHAR2(32767)
INDEX BY VARCHAR2(32767);

TYPE string_number_map IS TABLE OF NUMBER
INDEX BY VARCHAR2(32767);
Dependencies
DBMS_ADDM PRVTEMX_ADMIN WRI$_REPT_FORMATS
DBMS_ASH_INTERNAL PRVTEMX_DBHOME WRI$_REPT_FORMAT_ID_SEQ
DBMS_ASSERT PRVTEMX_MEMORY WRI$_REPT_MEMORY
DBMS_AUTO_REPORT PRVTEMX_PERF WRI$_REPT_OPTSTATS
DBMS_AUTO_REPORT_INTERNAL PRVT_AWRV_METADATA WRI$_REPT_PERF
DBMS_PERF PRVT_AWR_VIEWER WRI$_REPT_PLAN_DIFF
DBMS_REPORT_LIB PRVT_EMX WRI$_REPT_REPORTS
DBMS_SQLTCB_INTERNAL PRVT_HDM WRI$_REPT_REPT_ID_SEQ
DBMS_SQLTUNE PRVT_REPORT_REGISTRY WRI$_REPT_RTADDM
DBMS_SQLTUNE_INTERNAL PRVT_REPORT_TAGS WRI$_REPT_SECURITY
DBMS_SQL_MONITOR REPORT_COMPONENTS WRI$_REPT_SESSION
DBMS_STANDARD UTL_LMS WRI$_REPT_SPMEVOLVE
DBMS_STATS WRI$_REPT_ABSTRACT_T WRI$_REPT_SQ:DETAIL
DBMS_SYS_ERROR WRI$_REPT_ADDM WRI$_REPT_SQLMONITOR
DBMS_UTILITY WRI$_REPT_ARC WRI$_REPT_SQLPI
DBMS_WORKLOAD_CAPTURE WRI$_REPT_ASH WRI$_REPT_SQLT
DBMS_WORKLOAD_REPLAY WRI$_REPT_AWRV WRI$_REPT_STORAGE
DBMS_WORKLOAD_REPOSITORY WRI$_REPT_COMPONENTS WRI$_REPT_XPLAN
DBMS_WRR_INTERNAL WRI$_REPT_COMP_ID_SEQ XMLSEQUENCE
DBMS_XPLAN WRI$_REPT_CONFIG XMLSEQUENCETYPE
DUAL WRI$_REPT_CPADDM XMLTYPE
NLS_SESSION_PARAMETERS WRI$_REPT_DBHOME _REPORT_COMPONENT_OBJECTS
PLITBLM WRI$_REPT_EMX_PERF _REPORT_FORMATS
Documented No
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsrep.sql
Subprograms
 
BUILD_GENERIC_TAG
Undocumented: For internal use only dbms_report.build_generic_tag(
tag_name   IN VARCHAR2,
tag_inputs IN UNDEFINED)
RETURN XMLTYPE;
TBD
 
BUILD_REPORT_REFERENCE_STRUCT
Builds a report ref string given the necessary inputs dbms_report.build_report_reference_struct(
component_name IN VARCHAR2,
report_name    IN VARCHAR2,
id_param_val   IN ref_string_idspec)
RETURN VARCHAR2;
TBD
 
BUILD_REPORT_REFERENCE_VARG
Builds a report ref string given the necessary inputs dbms_report.build_report_reference_varg(
component_name IN VARCHAR2,
report_name    IN VARCHAR2,
id_param_val   IN UNDEFINED)
RETURN VARCHAR2;
exec build_report_reference_varg('cname','rname','foo','1','bar','2');
 
CLEAR_FRAMEWORK
Undocumented function for internal use only dbms_report.clear_framework(component_name IN VARCHAR2 := NULL);
TBD
 
FORMAT_MESSAGE (new 12.1)
Formats an Oracle message, for example an error message dbms_report.format_message(
message_number IN PLS_INTEGER,
message_facility IN VARCHAR2 DEFAULT 'ora',
language         IN VARCHAR2 DEFAULT NULL,
arg1             IN VARCHAR2 DEFAULT NULL,
arg2             IN VARCHAR2 DEFAULT NULL,
arg3             IN VARCHAR2 DEFAULT NULL,
arg4             IN VARCHAR2 DEFAULT NULL,
arg5             IN VARCHAR2 DEFAULT NULL,
arg6             IN VARCHAR2 DEFAULT NULL,
arg7             IN VARCHAR2 DEFAULT NULL,
arg8             IN VARCHAR2 DEFAULT NULL,
arg9             IN VARCHAR2 DEFAULT NULL,
arg10            IN VARCHAR2 DEFAULT NULL,
arg11            IN VARCHAR2 DEFAULT NULL,
arg12            IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
SELECT dbms_report.format_message(600)
FROM dual;
 
FORMAT_REPORT (new 12.1 overload)
Transforms an XML document into another format, as declared through one of the register_xxx_format calls above

Overload 1
dbms_report.format_report(
report       IN  XMLTYPE,
format_name  IN  VARCHAR2,
compress_xml IN BINARY_INTEGER := 0)
RETURN CLOB;
TBD
Overload 2 dbms_report.format_report(
report              IN  XMLTYPE,
format_name         IN  VARCHAR2,
format_content_type OUT NUMBER,
compress_xml        IN  BINARY_INTEGER := 0)
RETURN CLOB;
TBD
 
GET_PARAM (new 12.1)
Gets a parameter from parsed report reference dbms_report.get_param(
param_val     IN ref_string_idspec,
param_name    IN VARCHAR2,
mandatory     IN BOOLEAN := FALSE,
default_value IN CLOB := NULL,
nullable      IN BOOLEAN := FALSE)
RETURN CLOB;
TBD
 
GET_REPORT (new 12.1 overload)
Undocumented: For internal use only dbms_report.get_report(
report_reference IN VARCHAR2,
compress_xml     IN BINARY_INTEGER := 0)
RETURN CLOB;
TBD
Overload 2 dbms_report.get_report(
report_reference IN  VARCHAR2,
content_type     OUT NUMBER,
compress_xml     IN  BINARY_INTEGER := 0)
RETURN CLOB;
TBD
 
GET_REPORT_WITH_SUMMARY (new 12.1)
Fetches a report from its component dbms_report.get_report_with_summary(report_reference IN VARCHAR2)
RETURN CLOB;
TBD
 
GET_TIMING_INFO (new 12.1)
Allows one to get elapsed and CPU timing information for a section of PL/SQL code dbms_report.get_timing_info(
phase   IN     BINARY_INTEGER, -- 0 = start, 1 = end
elapsed IN OUT NUMBER,
cpu     IN OUT NUMBER);
DECLARE
 val1 NUMBER := 0;
 val2 NUMBER := 0;
 x   NUMBER;
BEGIN
  dbms_report.get_timing_info(0, val1, val2);

  SELECT COUNT(*)
  INTO x
  FROM cdb_source
  WHERE LOWER(text) LIKE '%q%';

  dbms_report.get_timing_info(1, val1, val2);
  dbms_output.put_line('Elapsed: ' || TO_CHAR(val1));
  dbms_output.put_line('CPU: ' || TO_CHAR(val2));
END;
/
 
GZIP_REPORT_XML (new 12.1)
Turns an XML formatted CLOB into a zipped file output as a BLOB dbms_report.gzip_report_xml(report IN CLOB) RETURN BLOB;
DECLARE
 cVar CLOB := '<mytag>This is exactly 92 bytes in size size size size size size size size size size</mytag>';
 bVar BLOB;
BEGIN
  dbms_output.put_line(LENGTHB(cVar));
  bVar := dbms_report.gzip_report_xml(cVar);
  dbms_output.put_line(LENGTHB(bVar));
END;
/
 
LOOKUP_COMPONENT_ID (new 12.1)
Fetches a component id and returns it. If the component does not exist, it signals ERR_UNKNOWN_OBJECT. dbms_report.lookup_component_id(component_name IN VARCHAR2) RETURN NUMBER;
TBD
 
LOOKUP_REPORT_ID (new 12.1)
Fetches a report id and returns it. If the report does not exist, it signals ERR_UNKNOWN_OBJECT. dbms_report.lookup_report_id(
component_name IN VARCHAR2,
report_name    IN VARCHAR2)
RETURN NUMBER;
TBD
 
PARSE_REPORT_REFERENCE
Parses a report reference to reveal its constituent parts. Each one is returned as an OUT parameter, converted to lower case. dbms_report.parse_report_reference(
report_reference IN  VARCHAR2,
component_name   OUT VARCHAR2,
report_name      OUT VARCHAR2,
id_param_val     OUT ref_string_idspec);
TBD
 
REGISTER_COMPONENT
Registers a new component with the XML reporting framework. Called at startup by& dbms_report_registry. dbms_report.register_component(
component_name   IN VARCHAR2,
component_desc   IN VARCHAR2,
component_object IN wri$_rept_abstract_t);
TBD
 
REGISTER_CUSTOM_FORMAT
Registers a custom format for an XML document dbms_report.register_custom_format(
component_name      IN VARCHAR2,
report_name         IN VARCHAR2,
format_name         IN VARCHAR2,
format_desc         IN VARCHAR2,
format_content_type IN NUMBER);
TBD
 
REGISTER_REPORT
Registers a report with the framework dbms_report.register_report(
component_name IN VARCHAR2,
report_name    IN VARCHAR2,
report_desc    IN VARCHAR2,
schema_id      IN NUMBER);
TBD
 
REGISTER_SWF (new 12.1)
Registers a swf file for a report. Each report corresponds to one swf file. The swf file displays the report in flash UI. dbms_report.register_swf(
component_name IN VARCHAR2,
report_name    IN VARCHAR2,
swf_id         IN NUMBER);
TBD
 
REGISTER_TEXT_FORMAT
Registers a format mapping for a text report dbms_report.register_text_format(
component_name     IN VARCHAR2,
report_name        IN VARCHAR2,
format_name        IN VARCHAR2,
format_desc        IN VARCHAR2,
html_stylesheet_id IN NUMBER,
text_max_linesize  IN NUMBER := 80);
TBD
 
REGISTER_XSLT_FORMAT
Registers a format mapping for a report via XSLT. Prior to calling this function the XSLT should have been stored in XDB with  STORE_FILE. After registration it can be used by calling FORMAT_REPORT. dbms_report.register_xslt_format(
component_name      IN VARCHAR2,
report_name         IN VARCHAR2,
format_name         IN VARCHAR2,
format_desc         IN VARCHAR2,
format_content_type IN NUMBER := CONTENT_TYPE_HTML,
stylesheet_id       IN NUMBER);
TBD
 
RESTORE REPORT_ENV
Reverts back the values of some session parameters based on the input value dbms_report.restore_report_env(orig_env IN format_param_values);
TBD
 
SETUP_REPORT_ENV
Sets canonical values for a few session parameters and returns their original values as a record type dbms_report.setup_report_env(orig_env IN OUT NOCOPY format_param_values)
RETURN BOOLEAN;
TBD
 
TRANSFORM_HTML_TO_TEXT
Undocumented: For internal use only says Oracle but this is both easy and valuable as it does what appears to be a good validation. Write bad HTML and you'll see how well it works. dbms_report.transform_html_to_text(
document     IN XMLTYPE,
max_linesize IN POSITIVE)
RETURN CLOB;
DECLARE
 x XMLTYPE;
 c CLOB;
BEGIN
  x := XMLTYPE('<html>
                  <head>
                    <title>Oracle 12c DBMS_REPORT Demos</title>
                  </head>
                  <body>
                    this is a test
                  </body>
                </html>');

  c := dbms_report.transform_html_to_text(x, 13);
  dbms_output.put_line(c);
END;
/
 
TRANSFORM_REPORT_XML (new 12.1)
Undocumented dbms_report.transform_report_xml(
report_xml  IN XMLTYPE,
zlib2base64 IN BINARY_INTEGER := 1)
RETURN XMLTYPE;
TBD
 
VALIDATE_REPORT
Applies the XML schema registered with the framework corresponding to the report specified to verify a correct build dbms_report.validate_report(report IN XMLTYPE);
TBD
 
ZLIB2BASE64_REPORT_XML (new 12.1)
Undocumented dbms_report.zlib2base64_report_xml(report_xml IN XMLTYPE) RETURN XMLTYPE;
TBD

Related Topics
Packages
PRVT_AWR_VIEWER
PRVT_REPORT_REGISTRY

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved