EXECUTE Procedure

The EXECUTE procedure executes one or more OLAP DML commands and directs the output to a printer buffer. It is typically used to manipulate analytic workspace data within an interactive SQL session.

When you are using SQL*Plus, you can direct the printer buffer to the screen by issuing the following command:

SET SERVEROUT ON

If you are using a different program, refer to its documentation for the equivalent setting.

Input and output is limited to 4K. For larger values, refer to the INTERP and INTERPCLOB functions in this package.

Syntax

DBMS_AW.EXECUTE ( 
    olap_commands     IN   VARCHAR2
    text              OUT  VARCHAR2);

Parameters

Table 11-2  EXECUTE Procedure Parameters

Parameter	Description
olap-commands	One or more OLAP DML commands separated by semicolons.
text	Output from the OLAP engine in response to the OLAP commands.

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.

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

The following sample SQL*Plus session attaches an analytic workspace named XADEMO, creates a formula named COST_PP in XADEMO, and displays the new formula definition.

SQL> SET SERVEROUT ON

SQL> EXECUTE DBMS_AW.EXECUTE('AW ATTACH xademo RW; DEFINE cost_pp FORMULA 
LAG(analytic_cube_f.costs, 1, time, LEVELREL time_levelrel)');

PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_AW.EXECUTE('DESCRIBE cost_pp');

DEFINE COST_PP FORMULA DECIMAL <CHANNEL GEOGRAPHY PRODUCT TIME>
EQ lag(analytic_cube_f.costs, 1, time, levelrel time.levelrel)

PL/SQL procedure successfully completed.