Oracle | DBMS

Oracle DBMS_REPORT Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Supports building XML from within the kernel.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Directory Name
SHARED_DIRECTORY_OBJECT	VARCHAR2(64)	'ORAREP_DIR'
Formats
DATE_FMT	VARCHAR2(21)	'mm/dd/yyyy hh24:mi:ss'
DATE_FMT_MOD	VARCHAR2(21)	'mm:dd:yyyy hh24:mi:ss'
Content Types
CONTENT_TYPE_XML	NUMBER	1
CONTENT_TYPE_HTML	NUMBER	2
CONTENT_TYPE_TEXT	NUMBER	3
CONTENT_TYPE_BINARY	NUMBER	4

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 97 objects

Documented No

First Available 12.1

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsrep.sql

Subprograms

BUILD_GENERIC_TAG	GET_REPORT_WITH_SUMMARY	REGISTER_SWF
BUILD_REPORT_REFERENCE_STRUCT	GET_SNAP_ID	REGISTER_TEXT_FORMAT
BUILD_REPORT_REFERENCE_VARG	GET_TIMING_INFO	REGISTER_XSLT_FORMAT
CLEAR_FRAMEWORK	GZIP_REPORT_XML	RESTORE_REPORT_ENV
FORMAT_MESSAGE	LOOKUP_COMPONENT_ID	SETUP_REPORT_ENV
FORMAT_REPORT	LOOKUP_REPORT_ID	TRANSFORM_HTML_TO_TEXT
GET_AWR_CONTEXT	PARSE_REPORT_REFERENCE	TRANSFORM_REPORT_XML
GET_AWR_DIFF_CONTEXT	REGISTER_COMPONENT	VALIDATE_REPORT
GET_IMPORTED_REPORT_ATTRS	REGISTER_CUSTOM_FORMAT	ZLIB2BASE64_CLOB
GET_PARAM	REGISTER_REPORT	ZLIB2BASE64_REPORT_XML
GET_REPORT

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

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