Previous
Previous
 
Next
Next


SQL

The SQL command passes instructions written in Structured Query Language (SQL) to the relational manager from Oracle OLAP. Using the SQL command, you can insert and update data in relational tables, retrieve data from relational tables into analytic workspace objects, and execute stored procedures.

To use the SQL command, you must be familiar with SQL syntax and with the data structures in your relational database, and have the appropriate access rights to the relational tables that you want to use.

This entry describes the OLAP DML SQL command in general, and subsequent entries discuss the use of the OLAP DML SQL command for specific SQL statements:

Syntax

SQL sql-statement

Parameters

sql-statement

For sql-statement you can specify most SQL statements that can be executed dynamically and also several associated non-dynamic statements. You can also specify PROCEDURE for a stored procedure as described in SQL PROCEDURE. A SQL statement cannot exceed 128K bytes including the values of all non-textual OLAP DML input expressions.

You cannot specify the following SQL statements for sql-statement:

  • COMMIT -- To commit your changes, issue the OLAP DML COMMIT statement.

  • ROLLBACK -- You cannot rollback using the OLAP DML. When you specify SQL ROLLBACK, you receive an error message stating that ROLLBACK is not supported as an argument to an OLAP DML SQL statement.


    Important:

    When you use an OLAP DML SQL statement to request a rollback in some other fashion (for example, using SQL EXECUTE), Oracle OLAP issues a system error message, abnormally terminates the OLAP DML program that issued the statement. Oracle OLAP also detaches, in an indeterminate state, the analytic workspace that contains the OLAP DML program that made the rollback request and any other attached analytic workspaces with uncommitted updates.

Oracle OLAP evaluates some SQL statements before sending them to the relational manager. For example, Oracle OLAP evaluates SQL PREPARE and SQL EXECUTE, and SQL statements that copy data from relational tables into analytic workspace objects (See "Copying Relational Data into Analytic Workspace Objects" for a list of these statements).

Usage Notes

Copying Relational Data into Analytic Workspace Objects

You can copy relational data into analytic workspace objects using either an implicit cursor or an explicit cursor:

Oracle OLAP evaluates all of these statements before sending them to the relational manager.

For the syntax of these statements, see the individual topics. For the syntax of other SQL statements, refer to Oracle Database SQL Language Reference.

Inserting Data into a Relational Table

You can insert analytic workspace data into a relational table using SQL PREPARE statement for a SQL INSERT statement (typically with DIRECT= YES), and then executing the statement using SQL EXECUTE.

Options Related to the OLAP DML SQL Statements

Several options are available to you when embedding SQL into the OLAP DML. These options are listed in "SQL Embed Options".

Using OLAP DML Expressions in OLAP DML SQL Statements

You can use OLAP DML expressions (for example, OLAP DML variables) as arguments in many OLAP DML SQL statements. OLAP DML input expressions are values supplied by Oracle OLAP as parameters to a SQL statement. They specify the data to be selected or provide values for data that is being modified. You can use OLAP DML input expressions in SQL WHERE clauses, parameter list for procedures, UPDATE statements, and the value clause of INSERT.

Keep the following points in mind when using an OLAP DML expression in an OLAP DML SQL statement:

Error Checking

Oracle OLAP can detect some syntax errors in the arguments to the SQL statement, but most errors are detected by the Oracle RDBMS. Error codes and messages are returned to Oracle OLAP. Check the value of SQLCODE after each SQL statement to determine when it resulted in an error. When it does cause an error (that is when SQLCODE EQ -1), check the value of SQLERRM for information about the cause of the error.

Converting Oracle RDBMS Data Types into Oracle OLAP DML Data Types

Table: RDBMS Data Type Conversion to OLAP DML Data Types shows which Oracle RDBMS data types can be automatically converted into Oracle OLAP DML data types. You must explicitly convert or cast other data types in the SELECT statement within a SQL DECLARE CURSOR statement.

RDBMS Data Type Conversion to OLAP DML Data Types

Oracle RDBMS Data Type OLAP DML Dimension Type OLAP DML Variable Data Type

CHAR, NCHAR, NVARCHAR2, VARCHAR2

TEXT [WIDTH n], ID, NTEXT

TEXT, NTEXT

NUMBER

NUMBER, INTEGER, SHORTINTEGER, LONGINTEGER

NUMBER, INTEGER, BOOLEAN, SHORTINTEGER, LONGINTEGER, DECIMAL, SHORTDECIMAL

CLOB (only within SQL FETCH and SQL SELECT statements)

TEXT

TEXT

NCLOB (only within SQL FETCH and SQL SELECT statements)

NTEXT

NTEXT

DATE

-

DATE, DATETIME


SQL UPDATE Statements

SQL UPDATE statements can contain a WHERE clause, which specifies a particular search condition. In addition to the search conditions typically used in SQL, the phrase WHERE CURRENT OF cursor is supported for single tables and views that include columns from only one table. The cursor must have been defined with the FOR UPDATE clause as described in SQL DECLARE CURSOR.

Examples

Inserting Data in a Table

You can use SQL statements such as the following to create a table and add rows to that table. The SQL INSERT statement adds a row to the sales table using values from the dimension salesperson and the variable dollars. It adds one row using the first value of salesperson that is in status.

SQL CREATE TABLE sales (name CHAR(12), dollars INTEGER)
SQL INSERT INTO sales VALUES (:salesperson, :dollars)