|Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)
Part Number A86720-01
Using Embedded SQL, 12 of 15
You write data stored in OLAP DML variables into a relational table by using those variables as input host variables in your SQL statements. When writing multiple records, you should use the PREPARE and EXECUTE statements so that the same INSERT statement does not have to be recompiled for each row of data being sent to the table.
The syntax of the PREPARE and EXECUTE statements is shown below.
PREPARE and EXECUTE must appear in the same program and cannot include ampersand (&) substitution. When an input host variable is a dimension, you must use a FOR command to loop over its values; otherwise, only the first value in status is sent.
Suppose that you have been using the OLAP DML to plan the introduction of a new product line, and now you want to add these new products to your relational database. You could copy this information from your analytic workspace into the appropriate relational table.
The following are the OLAP DML objects used to store the data.
DEFINE PROD DIMENSION TEXT DEFINE LABELS.P VARIABLE TEXT <PROD> DEFINE SUGGEST.P VARIABLE SHORT <PROD>
The following program fragment shows how you would use a FOR loop so that all product values currently in status are copied to a table named Products.
for prod do sql insert into products - values(:prod, :labels.p, :suggest.p) if sqlcode ne 0 then break doend
The previous example will run much more efficiently when the INSERT statement is compiled with the PREPARE statement. The next example shows the PREPARE statement being used to compile the INSERT statement with a name of WRITE_PRODUCTS, which is then run by an EXECUTE statement within the FOR loop.
sql prepare write_products from - insert into products - values(:prod, :labels.p, :suggest.p) . . . for prod do sql execute write_products if sqlcode ne 0 then break doend
You can also use the values of a multidimensional OLAP DML variable to update the values in a relational table. Using a FOR loop, your OLAP DML program steps through the specified dimension value by value and uses a WHERE clause to point to the corresponding row in the relational table.
The following program fragment updates only those rows in the Products table where the values in the PROD_ID column match the PROD dimension values currently in status.