Rem Rem dbmsrep.sql Rem Rem Copyright (c) 2005, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem dbmsrep.sql - DBMS_REPORT package spec Rem Rem DESCRIPTION Rem This file serves as the package specification for the DBMS_REPORT Rem package, a framework for helping server components build XML from Rem within the kernel. Rem Rem Implementation of this package is in svrman/report/prvtrep.sql Rem Rem MODIFIED (MM/DD/YY) Rem pbelknap 06/16/09 - remove urls Rem shjoshi 03/26/09 - Add setup_report_env and restore_report_env Rem bdagevil 11/12/08 - register common em xslt Rem pbelknap 07/13/06 - allow clearing of single component only Rem kyagoub 07/08/06 - switch arguments of store_file Rem pbelknap 04/24/06 - add drop shared directory Rem pbelknap 02/08/05 - Created Rem ------------------------------------------------------------------------------- -- DBMS_REPORT FUNCTION DESCRIPTIONS -- ------------------------------------------------------------------------------- -- Component Mapping Service functions ---------------------------------------- -- register_component: register a new component with callback to framework -- register_report: register a new report (view of component data), same -- callback as passed to register_component -- -- get_report: fetch a report from a framework component -- --------------------------------------------------- -- Transformation and Validation Engine functions --------------------------------------------------- -- create_shared_directory: setup the directory object before storing files -- drop_shared_directory: drop the directory object after storing files -- store_file: keep a file in the reporting framework -- register_XXX_format: create an XSLT, text, or custom output format -- format_report: transform an XML document to a registered format -- validate_report: apply an XML schema to XML data, check for -- validity -- --------------------------------------------------- -- General Utility functions --------------------------------------------------- -- build_report_reference_xxx: build a report_ref string helper -- parse_report_reference: parse a report_ref string passed to the report ------------------------------------------------------------------------------- CREATE OR REPLACE PACKAGE dbms_report AUTHID CURRENT_USER IS --=========================================================================-- -- GLOBAL CONSTANTS -- --=========================================================================-- -- Content type constants (used by servlet, stored in wri$_rept_formats) CONTENT_TYPE_XML CONSTANT NUMBER := 1; CONTENT_TYPE_HTML CONSTANT NUMBER := 2; CONTENT_TYPE_TEXT CONSTANT NUMBER := 3; CONTENT_TYPE_BINARY CONSTANT NUMBER := 4; -- Directory name that clients use passing for their files SHARED_DIRECTORY_OBJECT CONSTANT VARCHAR2(64) := 'ORAREP_DIR'; --=========================================================================-- -- TYPES -- --=========================================================================-- TYPE ref_string_idspec IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(32767); -- record for storing canonical values of certain parameters we wish to -- set before any report is generated. These parameters influence the way -- data is formatted in the reports and the canonical values will ensure -- consistent formatting irrespective of other umbrella parameter changes -- We set the following parameters: -- NLS_NUMERIC_CHARACTERS - to control how numbers are formatted (decimal -- and group separator characters) -- NLS_DATE_FORMAT - to control date format -- NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT - to control timestamp format TYPE format_param_value IS RECORD ( param_num NUMBER, param_value VARCHAR2(32767) ); TYPE format_param_values IS TABLE OF format_param_value; --=========================================================================-- -- COMPONENT MAPPING SERVICE FUNCTIONS -- --=========================================================================-- ------------------------------ register_component --------------------------- -- NAME: -- register_component -- -- DESCRIPTION: -- This procedure registers a new component with the XML reporting -- framework. It should be called at database startup from within the -- dbms_report_registry package. -- -- PARAMETERS: -- component_name (IN) - name of the component to register -- (converted to lower case) -- component_desc (IN) - description of the component to register -- component_object (IN) - object to store for this component, used for -- requesting reports -- -- RETURN: -- VOID ----------------------------------------------------------------------------- PROCEDURE register_component( component_name IN VARCHAR2, component_desc IN VARCHAR2, component_object IN wri$_rept_abstract_t); -------------------------------- register_report ---------------------------- -- NAME: -- register_report -- -- DESCRIPTION: -- This procedure registers a report with the framework. One components -- can have multiple reports but must have at least 1. Having multiple -- reports is the best way for components to generate XML documents that -- link to each other through the mechanism. -- -- PARAMETERS: -- component_name (IN) - name of the component to register -- report_name (IN) - name of the report to register -- (converted to lower case) -- report_desc (IN) - description of the report to register -- schema_id (IN) - file id of schema for this report, can be NULL -- (returned from store_file) -- -- RETURN: -- VOID ----------------------------------------------------------------------------- PROCEDURE register_report( component_name IN VARCHAR2, report_name IN VARCHAR2, report_desc IN VARCHAR2, schema_id IN NUMBER); ------------- build_report_reference - vararg and structure versions -------- -- NAME: -- build_report_reference _varg/_struct - vararg and structure versions -- -- DESCRIPTION: -- This function builds a report ref string given the necessary inputs. -- The report_id is given as a variable-argument list of name/value pairs, -- or as an instance of the ref_string_idspec type. -- -- For example, to generate the reference for the string -- /orarep/cname/rname?foo=1AMPbar=2 -- (substituting AMP for the ampersand in the ref string) -- call this function as -- -- build_report_reference_varg('cname','rname','foo','1','bar','2'); -- -- or as -- -- build_report_reference_struct('cname','rname',params) where params -- has been initialized to hold 'foo' and 'bar'. -- -- Parameter names and values are case-sensitive -- -- NOTES: -- build_report_reference_vararg cannot be called from SQL due to a known -- limitation in the PL/SQL vararg implementation. Clients can, however, -- create a PL/SQL non-vararg wrapper around it and call that in SQL if -- they have the need. -- -- The framework reserves some parameter names for internal use. See -- dbms_report.get_report. -- -- PARAMETERS: -- component_name (IN) - name of the component for ref string -- report_name (IN) - name of the report for ref string -- id_param_val (IN) - list of parameter names and values for -- the report_id portion of the string -- -- RETURN: -- report reference string, as VARCHAR2 ----------------------------------------------------------------------------- FUNCTION build_report_reference_varg( component_name IN VARCHAR2, report_name IN VARCHAR2, id_param_val ...) RETURN VARCHAR2; FUNCTION build_report_reference_struct( component_name IN VARCHAR2, report_name IN VARCHAR2, id_param_val IN ref_string_idspec) RETURN VARCHAR2; ----------------------------- parse_report_reference ------------------------ -- NAME: -- parse_report_reference -- -- DESCRIPTION: -- This function parses a report reference to reveal its constituent -- parts. Each one is returned as an OUT parameter, converted to lower -- case. Parameter names and values are case-sensitive. -- -- PARAMETERS: -- report_reference (IN) - report ref string to parse -- component_name (OUT) - name of the component for ref string -- report_name (OUT) - name of the report for ref string -- id_param_val (OUT) - parameter names and values for ref string -- -- RETURN: -- report reference string, as VARCHAR2 ----------------------------------------------------------------------------- PROCEDURE parse_report_reference( report_reference IN VARCHAR2, component_name OUT VARCHAR2, report_name OUT VARCHAR2, id_param_val OUT ref_string_idspec); ----------------------------------- get_report ------------------------------ -- NAME: -- get_report -- -- DESCRIPTION: -- This procedure fetches a report from its component. -- -- PARAMETERS: -- report_reference (IN) - report_ref string to use for fetching this -- report, of the form -- /orarep/component/report_name?. -- -- Components can build a report reference by -- calling build_report_reference, or parse one -- by calling parse_report_reference. -- -- The following parameter names are reserved and -- interpreted by this function. They will be -- removed from the reference string before -- dispatching the get_report call, and applied -- to the XML returned by the component. Add -- them to your ref strings to get the related -- functionality. -- -- + format: maps to format name. When -- specified, we will apply the format before -- returning the report -- + validate: y/n according to whether -- framework should validate the xml report. -- -- RETURN: -- report -- -- NOTES: -- See build_report_reference comments for sample ref strings. ----------------------------------------------------------------------------- FUNCTION get_report(report_reference IN VARCHAR2) RETURN CLOB; --=========================================================================-- -- TRANSFORMATION AND VALIDATION FUNCTIONS -- --=========================================================================-- --------------------------- create_shared_directory ------------------------- -- NAME: -- create_shared_directory -- -- DESCRIPTION: -- This procedure changes the location of the directory object used for -- loading files into the framework. See SHARED_DIRECTORY_OBJECT constant -- above. This function should be called once per directory whenever -- store_file will be used with the SHARED_DIRECTORY_OBJECT. -- -- PARAMETERS: -- dirname (IN) - directory name, under 'ORACLE_HOME/rdbms/xml/orarep/'. -- Pass NULL for the parent 'orarep' directory holding -- the common schemas, xslts -- RETURN: -- VOID ----------------------------------------------------------------------------- PROCEDURE create_shared_directory(dirname IN VARCHAR2); ---------------------------- drop_shared_directory -------------------------- -- NAME: -- drop_shared_directory -- -- DESCRIPTION: -- This procedure drops the directory object used by the framework to -- find xslts and schemas on disk and load them into the database. See -- the SHARED_DIRECTORY_OBJECT constant. This function should be called -- whenever clients are done reading from the directory. -- -- PARAMETERS: -- None. -- -- RETURN: -- VOID ----------------------------------------------------------------------------- PROCEDURE drop_shared_directory; ----------------------------------- store_file ------------------------------ -- NAME: -- store_file -- -- DESCRIPTION: -- This function stores a file in the framework. It should be called by -- the dbms_report_registry package during database creation. File names -- are unique by component. -- -- PARAMETERS: -- component_name (IN) - name of component that this file belongs to -- NULL for framework-level data -- filename (IN) - name of file on disk. -- directory (IN) - directory object corresponding to file location, -- defaults to CLIENT_DIRNAME which is shared by -- all report clients -- -- RETURN: -- File ID generated by framework ----------------------------------------------------------------------------- FUNCTION store_file( component_name IN VARCHAR2, filename IN VARCHAR2, directory IN VARCHAR2 := SHARED_DIRECTORY_OBJECT) RETURN NUMBER; ------------------------------ register_xslt_format ------------------------- -- NAME: -- register_xslt_format -- -- DESCRIPTION: -- This function registers a format mapping for a report via XSLT. Prior -- to calling this function the XSLT should have been stored in XDB by -- calling STORE_FILE. After a format has been registered it can be -- used by calling format_report. -- -- PARAMETERS: -- component_name (IN) - name of component that this format -- belongs to -- report_name (IN) - name of report that this format belongs to -- format_name (IN) - format name (names are unique by report) -- note: the name 'em' is reserved -- format_desc (IN) - format description -- format_content_type (IN) - content type of format output, one of -- + CONTENT_TYPE_TEXT: plain text -- + CONTENT_TYPE_XML: xml -- + CONTENT_TYPE_HTML: html -- + CONTENT_TYPE_BINARY: other -- stylesheet_id (IN) - File ID for the XSLT -- (returned by store_file) -- ----------------------------------------------------------------------------- PROCEDURE 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); ------------------------------ register_text_format ------------------------- -- NAME: -- register_text_format -- -- DESCRIPTION: -- This function registers a format mapping for a text report. Text -- reports are created by first transforming an XML document to HTML -- using an XSLT provided by the component, and then turning the HTML to -- formatted text using the framework's own internal engine. Prior -- to calling this function the XSLT should have been stored in XDB by -- calling STORE_FILE. After a format has been registered it can be -- used by calling format_report. -- -- PARAMETERS: -- component_name (IN) - name of component for this format -- report_name (IN) - name of report for this format -- format_name (IN) - format name (names are unique by report) -- note: the name 'em' is reserved -- format_desc (IN) - format description -- html_stylesheet_id (IN) - file id to the stylesheet that transforms -- from XML to HTML (returned by store_file) -- text_max_linesize (IN) - maximum linesize for text report -- ----------------------------------------------------------------------------- PROCEDURE 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); ----------------------------- register_custom_format ------------------------ -- NAME: -- register_custom_format -- -- DESCRIPTION: -- This function registers a custom format for an XML document. It allows -- components to format their document for viewing manually,by performing -- any kind of programmatic manipulation of the XML tree and outputting -- CLOB. -- -- To apply custom formats, the framework will call the custom_format() -- member function in the object type for the component. -- -- PARAMETERS: -- component_name (IN) - name of component for this format -- report_name (IN) - name of report for this format -- format_name (IN) - format name (names are unique by report) -- note: the name 'em' is reserved -- format_desc (IN) - format description -- format_content_type (IN) - content type of format output, one of -- + CONTENT_TYPE_TEXT: plain text -- + CONTENT_TYPE_XML: xml -- + CONTENT_TYPE_HTML: html -- + CONTENT_TYPE_BINARY: other -- ----------------------------------------------------------------------------- PROCEDURE register_custom_format( component_name IN VARCHAR2, report_name IN VARCHAR2, format_name IN VARCHAR2, format_desc IN VARCHAR2, format_content_type IN NUMBER); --------------------------------- format_report ----------------------------- -- NAME: -- format_report -- -- DESCRIPTION: -- This function transforms an XML document into another format, as -- declared through one of the register_xxx_format calls above. -- -- PARAMETERS: -- report (IN) - document to format -- format_name (IN) - format name to apply -- ----------------------------------------------------------------------------- FUNCTION format_report( report IN XMLTYPE, format_name IN VARCHAR2) RETURN CLOB; ------------------------------- validate_report ----------------------------- -- NAME: -- validate_report -- -- DESCRIPTION: -- This procedure applies the XML schema registered with the framework -- corresponding to the report specified to verify that it was built -- correctly. -- -- PARAMETERS: -- report (IN) - report to validate -- -- RETURN: -- None -- -- ERRORS: -- Raises error 31011 if document is not valid ----------------------------------------------------------------------------- PROCEDURE validate_report(report IN XMLTYPE); --=========================================================================-- -- 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; ------------------------------- setup_report_env ---------------------------- -- NAME: -- setup_report_env -- -- DESCRIPTION: -- This function sets canonical values for a few session parameters and -- also returns their original values as a record type. -- -- PARAMETERS: -- -- RETURN: -- record containing original values of parameters ----------------------------------------------------------------------------- FUNCTION setup_report_env( orig_env IN OUT NOCOPY format_param_values) RETURN BOOLEAN; ----------------------------- restore_report_env ---------------------------- -- NAME: -- restore_report_env -- -- DESCRIPTION: -- This procedure reverts back the values of some session parameters based -- on the input value. -- -- PARAMETERS: -- orig_env (IN) names and values of session parameters -- -- RETURN: -- void ----------------------------------------------------------------------------- PROCEDURE restore_report_env( orig_env IN format_param_values); end; / show errors / CREATE OR REPLACE PUBLIC SYNONYM DBMS_REPORT FOR DBMS_REPORT / GRANT EXECUTE ON DBMS_REPORT TO PUBLIC / CREATE OR REPLACE LIBRARY DBMS_REPORT_LIB trusted as static /