It is undocumented. At least I couldn’t find references. In the XMLDB Protocol Server there is an extra service called orarep, and of course, everything which is undocumented interests me to pieces.
The xdbconfig.xml XMLDB Protocol Server configuration file has the following content ( “<>” has been replaced by “[ ]”):
[servlet-mapping]
[servlet-pattern]/orarep/* [/servlet-pattern]
[servlet-name] ReportFmwkServlet [/servlet-name]
[/servlet-mapping]
If you try something like http://localhost:8080/orarep/ something like the following will show up.
ORA-13971: Component "" unknown
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REPORT", line 154
ORA-06512: at "SYS.DBMS_REPORT", line 600
ORA-06512: at "SYS.DBMS_REPORT", line 665
ORA-06512: at line 1
Google or the Oracle Docs, return zip. Peeking into the dictionary, a describe of DBMS_REPORT returns the following:
SQL> describe SYS.DBMS_REPORT
FUNCTION BUILD_GENERIC_TAG RETURNS XMLTYPE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TAG_NAME VARCHAR2 IN
TAG_INPUTS UNDEFINED IN
FUNCTION BUILD_REPORT_REFERENCE_STRUCT RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPONENT_NAME VARCHAR2 IN
REPORT_NAME VARCHAR2 IN
ID_PARAM_VAL TABLE OF VARCHAR2(32767) IN
FUNCTION BUILD_REPORT_REFERENCE_VARG RETURNS VARCHAR2
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPONENT_NAME VARCHAR2 IN
REPORT_NAME VARCHAR2 IN
ID_PARAM_VAL UNDEFINED IN
PROCEDURE CLEAR_FRAMEWORK
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPONENT_NAME VARCHAR2 IN DEFAULT
PROCEDURE CREATE_SHARED_DIRECTORY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DIRNAME VARCHAR2 IN
PROCEDURE DROP_SHARED_DIRECTORY
FUNCTION FORMAT_REPORT RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
REPORT XMLTYPE IN
FORMAT_NAME VARCHAR2 IN
FUNCTION FORMAT_REPORT RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
REPORT XMLTYPE IN
FORMAT_NAME VARCHAR2 IN
FORMAT_CONTENT_TYPE NUMBER OUT
FUNCTION GET_REPORT RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
REPORT_REFERENCE VARCHAR2 IN
FUNCTION GET_REPORT RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
REPORT_REFERENCE VARCHAR2 IN
CONTENT_TYPE NUMBER OUT
PROCEDURE PARSE_REPORT_REFERENCE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
REPORT_REFERENCE VARCHAR2 IN
COMPONENT_NAME VARCHAR2 OUT
REPORT_NAME VARCHAR2 OUT
ID_PARAM_VAL TABLE OF VARCHAR2(32767) OUT
PROCEDURE REGISTER_COMPONENT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPONENT_NAME VARCHAR2 IN
COMPONENT_DESC VARCHAR2 IN
COMPONENT_OBJECT WRI$_REPT_ABSTRACT_T IN
PROCEDURE REGISTER_CUSTOM_FORMAT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPONENT_NAME VARCHAR2 IN
REPORT_NAME VARCHAR2 IN
FORMAT_NAME VARCHAR2 IN
FORMAT_DESC VARCHAR2 IN
FORMAT_CONTENT_TYPE NUMBER IN
PROCEDURE REGISTER_REPORT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPONENT_NAME VARCHAR2 IN
REPORT_NAME VARCHAR2 IN
REPORT_DESC VARCHAR2 IN
SCHEMA_ID NUMBER IN
PROCEDURE REGISTER_TEXT_FORMAT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPONENT_NAME VARCHAR2 IN
REPORT_NAME VARCHAR2 IN
FORMAT_NAME VARCHAR2 IN
FORMAT_DESC VARCHAR2 IN
HTML_STYLESHEET_ID NUMBER IN
TEXT_MAX_LINESIZE NUMBER IN DEFAULT
PROCEDURE REGISTER_XSLT_FORMAT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPONENT_NAME VARCHAR2 IN
REPORT_NAME VARCHAR2 IN
FORMAT_NAME VARCHAR2 IN
FORMAT_DESC VARCHAR2 IN
FORMAT_CONTENT_TYPE NUMBER IN DEFAULT
STYLESHEET_ID NUMBER IN
FUNCTION STORE_FILE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
COMPONENT_NAME VARCHAR2 IN
FILENAME VARCHAR2 IN
DIRECTORY VARCHAR2 IN DEFAULT
FUNCTION TRANSFORM_HTML_TO_TEXT RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
DOCUMENT XMLTYPE IN
MAX_LINESIZE BINARY_INTEGER IN
PROCEDURE VALIDATE_REPORT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
REPORT XMLTYPE IN
Apparently this is not an internal package, because fiddling around in $ORACLE_HOME\rdbms\admin, searching for DBMS_REPORT, lists the file dbmsrep.sql.
In this file, only the following last procedures and functions described in this dbmsrep.sql file, are stated as “INTERNAL USE ONLY”. The package body is wrapped though…
--===============================================--
-- UNDOCUMENTED FUNCTIONS --
-- ** INTERNAL USE ONLY ** --
--===============================================--
PROCEDURE clear_framework(component_name IN VARCHAR2 := NULL);
FUNCTION build_generic_tag(tag_name IN VARCHAR2,
tag_inputs ...)
RETURN XMLTYPE;
FUNCTION get_report(report_reference IN VARCHAR2,
content_type OUT NUMBER)
RETURN CLOB;
FUNCTION format_report(report IN XMLTYPE,
format_name IN VARCHAR2,
format_content_type OUT NUMBER)
RETURN CLOB;
FUNCTION transform_html_to_text(document IN XMLTYPE,
max_linesize IN POSITIVE)
RETURN CLOB;
end;
/
The dbmsrep.sql script also hints to a “shared directory”. After searching a little bit it looks like the directory $ORACLE_HOME/rdbms/xml hooks into this orarep XMLDB Protocol Server extension.
[oracle@homework xml]$ pwd
/oracle/product/11.1.0/db_1/rdbms/xml
[oracle@homework xml]$ ls -ltra
total 40
-rw-r--r-- 1 oracle oinstall 12945 Oct 1 2006 xdbconfig.xml.11.0
-rw-r--r-- 1 oracle oinstall 910 Nov 13 2006 bootacl.xml.11.0
drwxr-xr-x 2 oracle oinstall 4096 Aug 13 16:39 xsl
drwxr-xr-x 2 oracle oinstall 4096 Aug 13 16:39 schema
drwxr-xr-x 8 oracle oinstall 4096 Aug 13 16:39 orarep
drwxr-xr-x 13 oracle oinstall 4096 Aug 13 16:45 ..
drwxr-xr-x 5 oracle oinstall 4096 Sep 3 15:33 .
Listed via my “grep -i “DBMS_REPORT” *” in $ORACLE_HOME\rdbms\admin is also the file e1002000.sql. This script downgrades the database from the current version 11.1.0 to 10.2.0.
Rem NAME
Rem e1002000.sql - downgrade Oracle RDBMS from current release to 10.2.0
Rem
Rem **DO NOT ADD DOWNGRADE ACTIONS THAT CALL PL/SQL PACKAGES HERE
Rem **THOSE ACTIONS NOW BELONG IN f1002000.sql.
Rem
Rem DESCRIPTION
Rem
Rem
Rem This script performs the downgrade in the following stages:
Rem STAGE 1: downgrade from the current release to 11g;
Rem this stage is a no-op for 11g since the current release
Rem is 11.
Rem STAGE 2: downgrade base data dictionary objects from current
Rem release to 10.2
Rem a. remove new current release system/object privileges
Rem b. remove new current release catalog views/synonyms
Rem (previous release views will be recreated after)
Rem c. remove program units referring to new current
Rem release fixed views or non-compiling in 10.2
Rem d. update new current release columns to NULL or
Rem other values,delete rows from new current release
Rem tables, and drop new current release type attributes,
Rem methods, etc.
Rem e. downgrade system types from current release to 10.2
Rem
Rem NOTES
Rem * This script needs to be run in the current release environment
Rem (before installing the release to which you want to downgrade).
Rem * This script must be run using SQL*PLUS.
Rem * You must be connected AS SYSDBA to run this script.
Rem
Apparently this scripts removes the DBMS_REPORT items via the following changes.
Rem ============================
Rem Begin dbms_report changes
Rem ============================
DROP VIEW report_components;
DROP VIEW report_files;
DROP VIEW report_formats;
DROP VIEW "_REPORT_COMPONENT_OBJECTS";
DROP VIEW "_REPORT_FORMATS";
--
-- We have to drop the components table
-- rather than truncating it to avoid issues
-- on re-upgrade (e.g., the drop type force
-- below of the abstract object causes the
-- "object" column of this table to disappear
-- and it is not re-added during upgrade)
--
DROP TABLE wri$_rept_components;
TRUNCATE TABLE wri$_rept_reports;
TRUNCATE TABLE wri$_rept_files;
TRUNCATE TABLE wri$_rept_formats;
DROP SEQUENCE wri$_rept_comp_id_seq;
DROP SEQUENCE wri$_rept_rept_id_seq;
DROP SEQUENCE wri$_rept_file_id_seq;
DROP SEQUENCE wri$_rept_format_id_seq;
Rem =========================
Rem End dbms_report changes
Rem =========================
While querying, for instance view report_components the following output is produced.
SQL> desc report_components
Name Null? Type
----------------------------------------- -------- --------
COMPONENT_ID NOT NULL NUMBER
COMPONENT_NAME NOT NULL VARCHAR2(30)
COMPONENT_DESCRIPTION VARCHAR2(256)
REPORT_ID NOT NULL NUMBER
REPORT_NAME NOT NULL VARCHAR2(30)
REPORT_DESCRIPTION VARCHAR2(256)
SCHEMA_FILENAME VARCHAR2(500)
SCHEMA_DATA XMLTYPE
SQL> select COMPONENT_NAME, COMPONENT_DESCRIPTION, REPORT_NAME
2 from report_components
3*
COMPONENT_NAME COMPONENT_DESCRIPTION REPORT_NAME
-------------- ------------------------------- ------------
sqlpa SQL Performance Analyzer Report all
sqltune SQL Tuning Advisor auto_summary
sqltune SQL Tuning Advisor summary
xplan explain plan report typical
dbreplay Database replay report all
sqlmonitor SQL monitor report list
sqlmonitor SQL monitor report main
7 rows selected.
So this looks like the orarep XMLDB Protocol Server hooks into the local DB Console. On my database, I installed db console, but I didn’t use the features yet, as described above. Anyway, I tried the following via a web browser (based on the information gathered).
-- http://localhost:8080/orarep/sqltune/summary
ORA-13616: The current user XDB has not
been granted the ADVISOR privilege#
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_ADVISOR", line 919
ORA-06512: at "SYS.WRI#_REPT_SQLT", line 46
ORA-06512: at "SYS.DBMS_REPORT", line 694
ORA-06512: at line 1
I used the XDB schema account so I wouldn’t get into trouble regarding ACL privileges or other role shortages, despite the fact that in Oracle 11g, the XDB account only has been limited privileges and roles granted compared to Oracle 10.2.
So I granted the “ADVISOR” role to XDB via the SYS account. Now the following error is generated via calling it via a web browser…
SQL> conn / as sysdba
Connected.
SQL> grant advisor to xdb;
Grant succeeded.
-- http://localhost:8080/orarep/sqltune/summary
ORA-13666: Task ID does not exist#
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ADVISOR", line 5348
ORA-06512: at "SYS.WRI#_REPT_SQLT", line 110
ORA-06512: at "SYS.DBMS_REPORT", line 694
ORA-06512: at line 1
As said, I didn’t do anything with db console yet, so probably the following is caused because I didn’t generate reports or any other thing yet….because it looks like it is hooking into package PRVT_ADVISOR (also not documented, at least in the 11g docs), I tried the following…
-- http://localhost:8080/orarep/sqltune/summary?task_id=1
ORA-13631: The most recent execution of task
SYS#AUTO#SQL#TUNING#TASK contains no results#
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ADVISOR", line 6184
ORA-06512: at "SYS.WRI#_REPT_SQLT", line 113
ORA-06512: at "SYS.DBMS_REPORT", line 694
ORA-06512: at line 1
So all in all this looks promising. Almost there to putting it all together. I “guess’ (I know wrong word) this functionality is been used by the DB Console or who knows the first sign of an architecture where the complete “db console” will be replaced by an architecture that looks like APEX (that is inside the database).
Probably it won’t though. I guess DB Console, as a sort of “spin off” of OEM Grid Control, would take to much effort to rebuild it in the database using the same framework APEX is build on. Also this would split up development into to separated architectures (PL/SQL contra Java driven). Or would someone from Oracle now be triggered by the idea to…
Although short on time (regarding this small adventure), I wanted to share this…
(…this is fun…)
To be continued…
🙂
Marco,
I’ve been wondering about that too.
I hadn’t figured out any more than you had.
It would be nice to have a report transform engine in the database.
Doug
Despite the fact that I am curieus; I wanted to know what is does and what it security wise could mean / what impact it could have…
…but regarding the “report transform engine”, you already have one via the oradb and dburi stuff…
…but (thinking about that sentence) I guess, there is a reason why there is an new entry point…