blog.gralike.com About Oracle, XMLDB and other interests

3Sep/07Off

Oracle 11g: Peeking into Protocol Server functionality – orarep and DBMS_REPORT

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&gt; 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&gt; 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&gt; 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&gt; conn / AS sysdba
Connected.
 
SQL&gt; 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...

:-)

 

  • Digg
  • DZone
  • Reddit
  • Slashdot
  • StumbleUpon
  • Technorati
  • Twitter
  • del.icio.us
  • Hyves
  • Facebook
  • RSS
Filed under: 11gR1, 11gR2, APEX, General, Oracle, RDBMS, SQL*Net, Protocol Server, SQL, XML/SQL, XPath, XQuery, XMLDB Comments Off
Comments (2) Trackbacks (1)
  1. Doug Case
    September 4th, 2007 - 02:13

    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

  2. Marco Gralike
    September 4th, 2007 - 06:58

    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…


Leave a comment


Trackbacks are disabled.

Part of the Puzzle: Oracle XMLDB NFS Functionality « AMIS Technology blog
« XML Data – To be stored or not to be stored, and beyond… Old Love Never Dies… »

AMIS Company Profile

Vakatures bij AMIS Click here for more info

Upcoming Appearances

Twitter Updates

Recent Comments

Categories

Popular Posts

Archives

Blogroll

Meta

Spam Blocked