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';
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;
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;
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;
/
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);
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;
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);
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);
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);
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);
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;
/