|Oracle9i OLAP User's Guide
Release 2 (9.2)
Part Number A95295-01
DBMS_AW , 3 of 7
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
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.
The syntax for the
DBMS_AW.EXECUTE procedure is shown below.
One or more OLAP DML commands separated by semi-colons.
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
') 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.
") indicates the beginning of a comment.
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
This procedure does not print the output of the DML commands when you have redirected the output by using the OLAP DML
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;')
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
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.
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