![]() Previous |
![]() Next |
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
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:
To copy data from relational tables into analytic workspace objects using an implicit cursor, use a SQL SELECT statement. You can use this OLAP DML statement interactively in the OLAP Worksheet or within an OLAP DML program.
To copy data from relational tables into analytic workspace objects using an explicit cursor, use the following commands within an OLAP DML program in the order indicated:
SQL DECLARE CURSOR to define a SQL cursor by associating it with a SELECT statement or procedure.
SQL OPEN to activate a SQL cursor.
SQL FETCH or SQL IMPORT to retrieve and process data specified by a cursor.
Tip: SQL FETCH offers the most functionality; while SQL IMPORT offers improved performance when copying large amounts of data from relational tables into analytic workspace object. |
SQL CLOSE to close a SQL cursor.
SQL CLEANUP to cancel all SQL cursor declarations and free the memory resources of an SQL 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:
OLAP DML expressions must be preceded by a colon (for example, :myvar
).
When you specify a dimension or a dimensioned variable as an OLAP DML input expression, the first value in status is used; no implicit looping occurs, although you can use a FOR or an ACROSS statement to loop through all of the values. An OLAP DML input expression can be any expression with an appropriate data type. The value of an OLAP DML input expression is taken when a cursor is opened, not when it is declared. See Example: Inserting Data in a Table.
To update or insert data in a relational table that has either the CLOB and NCLOB data type, you use WIDE
in the OLAP DML input expression as described in "Inserting Large Text Values into a CLOB or NCLOB Column" .
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 |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
- |
|
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)