Using the Special Features of an OCI Connection

Overview: Using the special features of an OCI connection

When you have a direct connection to an Oracle9i database, you can use some of its special features.

FOR UPDATE clause

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.

WHERE CURRENT OF cursor clause

The WHERE CURRENT OF cursor clause is supported in UPDATE and DELETE statements for interactive modifications to a table.

Savepoints

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.

Requirements for stored procedures and triggers

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.

Example: Creating a stored procedure

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~

Executing a stored procedure

You use a PROCEDURE statement to run a stored procedure, using the following syntax.

SQL PROCEDURE procedure-name (arg1, arg2, arg3, . . .)

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.

Examples: Running the sample procedure

Providing literal values for arguments

The following command uses the NEW_PRODUCTS procedure to insert a single row in the Products table.

sql procedure new_products -
   ('P81', '8mm Camcorder', 279.58)

Using OLAP DML data for arguments

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.

call add_prods('last 5')