Oracle DBMS_REPORT
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.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.

Do you remember when mainframes were replaced by client-server? When client-server was replaced by n-tier architecture? The "Cloud" is a distraction ... DevOps is not. Prepare for the future.
Purpose Supports building 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 SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_REPORT'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_REPORT';


-- query returns 92 objects
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 ...)
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 ...)
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
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
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_AWR_CONTEXT
Returns the AWR begin/end snapshot for the report dbms_report.get_awr_context(
p_start_time IN     DATE,
p_end_time   IN     DATE,
p_dbid       IN OUT NUMBER,
p_begin_snap IN OUT NUMBER,
p_end_snap   IN OUT NUMBER);
TBD
 
GET_AWR_DIFF_CONTEXT (new 12.2)
Returns the AWR begin/end snapshot for base and comparison period for the difference report dbms_report.get_awr_diff_context(
p_begin_snap1 IN OUT NUMBER,
p_end_snap1   IN OUT NUMBER,
p_dbid1       IN     NUMBER,
p_begin_snap2 IN OUT NUMBER,
p_end_snap2   IN OUT NUMBER,
p_dbid2       IN     NUMBER);
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time) INST_START,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT, s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

SQL> SELECT dbid FROM v$database;

      DBID
----------
 549318987

DECLARE
 bsnap1 NUMBER := 1283;
 esnap1 NUMBER := 1284;
 bsnap2 NUMBER := 1308;
 esnap2 NUMBER := 1309;
BEGIN
  dbms_report.get_awr_diff_context(bsnap1, esnap1, 549318987, bsnap2, esnap2, 549318987);
  dbms_output.put_line('comparing: ' || bsnap1 || ' - ' || esnap1 || ' with ' || bsnap2 || ' - ' || esnap2);
END;
/
 
GET_IMPORTED_REPORT_ATTRS
Returns the database attributes from imported AWR data dbms_report.get_imported_report_attrs(
p_dbid            IN  NUMBER,
p_inst_count      OUT NUMBER,
p_cpu_cores       OUT NUMBER,
p_hyperthreaded   OUT NUMBER,
p_con_id          OUT NUMBER,
p_con_name        OUT VARCHAR2,
p_is_exa          OUT NUMBER,
p_timezone_offset OUT NUMBER,
p_packs           OUT NUMBER);
TBD
 
GET_PARAM
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
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
Fetches a report from its component dbms_report.get_report_with_summary(report_reference IN VARCHAR2)
RETURN CLOB;
TBD
 
GET_SNAP_ID
Finds the closest snap_id to specified time dbms_report.get_snap_id(p_time IN date, p_dbid IN NUMBER)
RETURN NUMBER;
SELECT dbid
FROM v$database;

SELECT dbms_report.get_snap_id(SYSDATE-1, 1929260586)
FROM dual;
 
GET_TIMING_INFO
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
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
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
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
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
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_CLOB
Undocumented dbms_report.zlib2base64_clob(report IN CLOB) RETURN CLOB;
TBD
 
ZLIB2BASE64_REPORT_XML
Undocumented dbms_report.zlib2base64_report_xml(report_xml IN XMLTYPE) RETURN XMLTYPE;
TBD

Related Topics
Built-in Functions
Built-in Packages
PRVT_AWR_VIEWER
PRVT_REPORT_REGISTRY
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved