4 DBMS_AW_XML

Using DBMS_AW_XML

This section contains topics that relate to using the DBMS_AW_XML package.

• Overview

• Providing the Context for Cubes and Dimensions

• Obtaining Diagnostic Information

Overview

You can define an analytic workspace containing dimensional data objects such as cubes and dimensions using either Analytic Workspace Manager or the OLAP API. The definitions of these dimensional objects are stored in the database as an XML document. You can save the XML document as a text file. You can then load and execute the XML file to re-create the dimensional objects, such as for backup or to copy the objects to a different system. The XML file is often called an XML template, or just a template.

You can use either Analytic Workspace Manager, the OLAP API, or DBMS_AW_XML to load and execute an XML file. The results are identical.

Providing the Context for Cubes and Dimensions

You can save the XML for an entire analytic workspace, for a single cube, or for a single dimension. When re-creating just a cube or dimension, you must provide the appropriate context. This context is an analytic workspace containing all of the metadata objects.

While Analytic Workspace Manager controls the context, DBMS_AW_XML does not. You must create an analytic workspace with the metadata before loading the XML for a cube or a dimension. It is not sufficient just to attach an empty analytic workspace. You must first use the READAWMETADATA function of DMBS_AW_XML.

Obtaining Diagnostic Information

If the procedures execute successfully but do not generate an analytic workspace, the problem may be in the XML template. You should always load a template into the same version and release of Oracle Database as the one used to generate the template.

The build will also fail if objects by the same name already exist in the schema. For example, you cannot create the Global analytic workspace with a dimension named PRODUCT in a schema that contains an analytic workspace named Test with a dimension named PRODUCT.

Error messages generated during the creation of the analytic workspace are stored in OLAPSYS.XML_LOAD_LOG. This table is publicly accessible. Use a query such as the following:

SQL> SELECT xml_message FROM olapsys.xml_load_log ORDER BY xml_date;

Summary of DBMS_AW_XML Subprograms

The following table describes the subprograms provided in DBMS_AW_EXECUTE.

---

EXECUTE Function

EXECUTE creates dimensional objects from an XML document. The XML is stored in a database object.

Syntax

EXECUTE (
     xml_input           IN      CLOB )
RETURN VARCHAR2;

Parameters

Return Values

The string Success if successful

Example

The following SQL script creates a CLOB and loads into it the contents of an XML file. It then creates an analytic workspace named GLOBAL in the GLOBAL_AW schema from the XML document in the CLOB. The DBMS_OUTPUT.PUT_LINE procedure is used to provide status messages during execution of the script.

You would typically use the EXECUTEFILE function to create an analytic workspace from an XML file. This example creates a CLOB from a file just for illustrating the EXECUTE function.

DECLARE
     clb CLOB;
     infile BFILE;

BEGIN
 
     dbms_output.put_line('Create a temporary clob');
     dbms_lob.createtemporary(clb, TRUE,10);
 
     dbms_output.put_line('Create a BFILE using BFILENAME function');
     infile := bfilename('WORK_DIR', 'GLOBAL.XML');
 
     dbms_output.put_line('Open the BFILE');
     dbms_lob.fileopen(infile, dbms_lob.file_readonly);
 
     dbms_output.put_line('Load Temporary Clob from the BFILE');
     dbms_lob.loadfromfile(clb,infile,dbms_lob.lobmaxsize,1,1);
     COMMIT;
 
     dbms_output.put_line('Close the BFILE');
     dbms_lob.fileclose(infile);

     dbms_output.put_line('Create the analytic workspace');
     dbms_output.put_line(dbms_aw_xml.execute(clb));
     COMMIT;
     dbms_aw.aw_update;

dbms_output.put_line('Free the temporary clob');
dbms_lob.freetemporary(clb);

EXCEPTION
     WHEN OTHERS
     THEN
          dbms_output.put_line(SQLERRM);
END;
/

The successful execution of this script generates the following messages:

Create a temporary clob
Create a BFILE using BFILENAME function
Open the BFILE
Load Temporary Clob from the BFILE
Close the BFILE
Create the analytic workspace
Success 
Free the temporary clob

---

EXECUTEFILE Function

EXECUTEFILE creates dimensional objects from an XML document stored in a text file. This file is also called a template.

Syntax

EXECUTEFILE (
     dirname             IN      VARCHAR2,
     xml_file            IN      VARCHAR2 )
RETURN VARCHAR2;

Returns

The string Success if successful

Parameters

Example

The following EXECUTEFILE function generates an analytic workspace from the XML document stored in GLOBAL.XML, which is located in a directory identified by the WORK_DIR directory object. The DBMS_OUTPUT.PUT_LINE function displays the Success message returned by EXECUTEFILE.

SQL> SET serveroutput ON format wrapped
SQL> EXECUTE dbms_output.put_line(dbms_aw_xml.executefile('WORK_DIR', 'GLOBAL.XML'));
Success

---

READAWMETADATA Function

Loads the XML definition of an analytic workspace into database memory and transfers the definition to the client, so that it can construct the client-side model.

Syntax

READAWMETADATA (
     awname              IN      VARCHAR2,
     rights              IN      VARCHAR2 )
RETURN GENWSTRINGSEQUENCE;

Returns

An XML document that defines the analytic workspace

Parameters

Example

This example first loads the OLAP metadata into memory. It creates an analytic workspace named GLOBAL that contains this OLAP metadata but no dimensions or cubes. It then uses the EXECUTEFILE function to create the Product dimension from an XML template file.

DECLARE
     tmp  GENWSTRINGSEQUENCE;
BEGIN
     dbms_output.put_line('Read the metadata');
     tmp := dbms_aw_xml.readawmetadata('global', 'RW');
END;
/
 
DECLARE
     tmp2 VARCHAR2(100);
BEGIN
     dbms_output.put_line('Create the Global aw');
     dbms_aw.execute('AW CREATE global');
     dbms_output.put_line('Create the Product dimension');
     dbms_output.put_line(dbms_aw_xml.executefile('work_dir', 'product.xml'));
     COMMIT;
     dbms_aw.aw_update;
EXCEPTION
     WHEN OTHERS
     THEN
          dbms_output.put_line(SQLERRM);
END;
 /

The successful execution of this script generates the following messages:

Read the metadata
Create the Global aw
Create the Product dimension 
Success