Supported SQL Commands

What SQL statements can OLAP DML evaluate?

The OLAP DML evaluates the following SQL statements, either in whole or in part, before sending them to the relational database.

EXECUTE	DECLARE	FETCH
OPEN	CLOSE	PREPARE

Commands and options for general SQL use

The following OLAP DML options are available.

Option	Description
SQL	A command that issues SQL commands.
SQLCODE	An integer option that holds the returned value from the most recently attempted SQL operation.
SQLERRM	A text option that contains an explanatory message after an error has occurred.
SQLMESSAGES	A Boolean option that controls whether SQL error messages are written to the current outfile.

SQLBLOCKMAX option

The SQLBLOCKMAX option is an integer option that controls the maximum number of records OLAP Services retrieves from an Oracle database at one time. This option provides a means of fine-tuning the performance of data fetches.

Unsupported SELECT statement

When using SQL interactively, you would typically execute a SELECT command to produce a table of data. However, most host programming languages, including OLAP DML, must receive the data one row at a time instead of an entire table at once. For this reason, you cannot issue a simple SQL SELECT statement interactively in OLAP DML. Instead, you must define a cursor within an OLAP DML program.

Defining Transactions

SQL supports transaction processing in an OCI connection to Oracle9i. This support allows you to save or abort a series of statements that you have defined as a logical unit of work. This method of processing prevents partial updates; either all statements or none within the unit of work are executed.

A COMMIT statement saves changes made in the current transaction, and a ROLLBACK statement discards them. In your programs, you will want to write the logic so that the program uses COMMIT when it runs successfully and ROLLBACK when it ends in an error.

The OLAP DML accepts the following commands for transaction processing:

SQL COMMIT [WORK]
SQL ROLLBACK [WORK]