DBMS_AW , 3 of 7

EXECUTE Procedure

This procedure executes one or more OLAP DML commands (input as a VARCHAR2 string) and prints the output of the OLAP DML commands (if any) using the DBMS_OUTPUT package.

See Also:

For the syntax of individual OLAP DML commands, see Oracle9i OLAP DML Reference help. For a information on analytic workspace objects, see Oracle9i OLAP Developer's Guide to the OLAP DML.

Syntax

The syntax for the DBMS_AW.EXECUTE procedure is shown below.

DBMS_AW.EXECUTE ( 
    olap-commands     IN VARCHAR2);

Parameters

Table 10-2 EXECUTE Procedure Parameters

Parameter	Description
`olap-commands`	One or more OLAP DML commands separated by semi-colons.

Usage Notes

Guidelines for Using Quotation Marks in OLAP DML Commands

The SQL processor evaluates the OLAP DML commands, either in whole or in part, before sending them to Oracle OLAP for processing. Follow these guidelines when formatting the OLAP DML commands in the olap-commands parameter:

Wherever you would normally use single quote (') in an OLAP DML command, use two single quotes (''). The SQL processor strips one of the single quotes before it sends the OLAP DML command to Oracle OLAP.
In the OLAP DML, a double quote (") indicates the beginning of a comment.

Executing Large Numbers of OLAP DML Commands

Since the olap-commands parameter of EXECUTE procedure is of type VARCHAR2, you are limited to 4,000 bytes for OLAP DML commands. For larger values, use the "INTERPCLOB Function" which allows you to input the OLAP DML commands as a CLOB.

Effect of the OUTFILE Command

This procedure does not print the output of the DML commands when you have redirected the output by using the OLAP DML OUTFILE command.

Example

Executing Multiple OLAP DML Commands

You can specify a number of OLAP DML commands in a single EXECUTE procedure. In this case, you separate each OLAP DML command with a semicolon (;). For example, the following EXECUTE procedure contains multiple lines of code prepares an analytic workspace for access by the OLAP_TABLE function with a complete limit map. It creates a grouping id variable for the Standard hierarchy of geography, execute the OLAP DML code shown below.

DBMS_AW.EXECUTE ('AW DETACH xademo;
AW ATTACH xademo ro;
PUSH OKNULLSTATUS;
OKNULLSTATUS = TRUE;
"Create variable for the grouping id;
DEFINE geog.gid INTEGER VARIABLE <geography>;
" Create new parent-child relations for only a single hierarchy;
" of each multidimensional hierarchical dimension;
DEFINE g0.newparent RELATION geography <geography>;
g0.newparent = g0.parent(g0.hierdim 1);
" Populate the groupid variables;
GROUPINGID g0.newparent INTO geog.gid;
" Save changes to analytic workspace;
POP OKNULLSTATUS;
ALLSTAT;
LIMIT g0.hierdim TO 1;
UPDATE;
COMMIT;')

Executing a Predefined OLAP DML Program

Assume that you have defined an OLAP DML program named makegroupid that performs the functionality shown in "Executing Multiple OLAP DML Commands". The following code illustrates using the EXECUTE procedure to execute the makegroupid program.

DBMS_AW.EXECUTE ( 
    'AW DETACH  xademo;
    AW ATTACH  xademo ro;
    CALL makegroupid;
    IN VARCHAR');

Executing OLAP DML Commands Using an Infile

You can create a text file that consists only of the OLAP DML commands that you want executed. For example, you could create a text file named makegroupid.inf that had the following lines of code.

AW DETACH  xademo
AW ATTACH  xademo ro
CALL makegroupid

Assume that you have saved makegroupid.inf into the /users/oracle/sql directory. The following code illustrates using the EXECUTE procedure to execute the OLAP DML commands in xademoprep.inf.

-- Attach and prepare xademo analytic workspace
EXECUTE DBMS_AW.EXECUTE('INFILE /users/oracle/sql/makegroupidp.inf')