|Oracle9i OLAP Services Developer's Guide to the OLAP DML
Release 1 (9.0.1)
Part Number A86720-01
Using Embedded SQL, 14 of 15
When you have a direct connection to an Oracle9i database, you can use some of its special features.
Oracle9i has an additional FOR UPDATE clause in the SELECT statement. This syntax is supported in a cursor declaration so that you can update or delete data associated with the cursor.
The WHERE CURRENT OF cursor clause is supported in UPDATE and DELETE statements for interactive modifications to a table.
Savepoints are ignored. If your program sets a savepoint and issues a ROLLBACK TO savepoint command, then all SQL commands are rolled back in the current transaction, and the transaction is ended.
Support is provided for Oracle9i stored procedures and triggers. They cannot contain SELECT statements. An OLAP DML stored procedure cannot contain output variables or transactions, nor can it call another procedure.
OLAP DML syntax differs slightly from the standard Oracle9i syntax. A tilde (~) is required instead of a semicolon as a terminator, and two colons are required instead of one in an assignment statement.
You can create a stored procedure or trigger in an OLAP DML program.
The following example shows the OLAP DML syntax for creating a procedure named NEW_PRODUCTS.
sql create procedure new_products - (id char, name char, cost number) is - price number~ - begin - price ::= cost * 2.5~ - insert into products - values(id, name, price)~ - end~
You use a PROCEDURE statement to run a stored procedure, using the following syntax.
The arguments can be literal text or input host variables. When you use input host variables, be sure to use a colon before the variable name. Also be sure to use the same number of arguments with appropriate data types for the parameters defined in the procedure.
The following command uses the NEW_PRODUCTS procedure to insert a single row in the Products table.
The ADD_PRODS program runs the same procedure but inserts data stored in OLAP DML dimensions and variables into the Products table. A FOR loop is required to loop over all of the values in status.
DEFINE ADD_PRODS PROGRAM LD Add new products using stored procedure NEW_PRODUCTS PROGRAM arg newprods text trap on error push prod limit prod to &newprods " Loop over PROD to insert the data for prod do sql procedure new_products(:prod, :labels.p,- :cost.p) if sqlcode ne 0 then break doend if sqlcode ne 0 then signal err1 'Insert data into table failed.' " Save new data in Products table sql commit show 'New products added to products table' goto cleanup ERROR: " Discard changes to Products table sql rollback show 'No new products added to Products table.' CLEANUP: pop prod END
To call ADD_PRODS, you issue a command like the following to set the status of PROD to include only the values you wish to update.