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

Arguments

sql-statement

For sql-statement you can specify most SQL statements that can be executed dynamically, as well as 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).

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:

    1. SQL DECLARE CURSOR to define a SQL cursor by associating it with a SELECT statement or procedure.

    2. SQL OPEN to activate a SQL cursor.

    3. 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.
    4. SQL CLOSE to close a SQL cursor.

    5. 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

A number of 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 10-130, "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. You should 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 10-10, "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.

Table 10-10 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

Example 10-130 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)

SQL CLEANUP

The SQL CLEANUP command cancels all SQL cursor declarations and frees the memory resources for all SQL cursors. You use the SQL CLEANUP command in combination with other SQL commands to copy data from relational tables into analytic workspace objects as outlined in "Copying Relational Data into Analytic Workspace Objects"

Syntax

SQL CLEANUP

Examples

For an example of the use of SQL CLEANUP, see Example 10-140, "Fetching Data into a Concat Dimension".


SQL CLOSE

The SQL CLOSE command closes a SQL cursor. You use the SQL OPEN command in combination with other SQL commands to copy data from relational tables into analytic workspace objects as outlined in "Copying Relational Data into Analytic Workspace Objects".

Syntax

SQL CLOSE cursor

Arguments

cursor

The name of a cursor previously opened with a SQL OPEN statement.

Notes

Redefining the Result Set

You can change the result set associated with a cursor by closing the cursor, setting the value of an OLAP DML input expression, and issuing a new SQL OPEN statement. You do not have to free the cursor and redeclare it.


SQL DECLARE CURSOR

The SQL DECLARE CURSOR command defines an explicit SQL cursor by associating it with a SELECT statement or procedure. The SELECT statement specifies the scope of the data (the rows and columns) selected by the cursor. You use the SQL DECLARE CURSOR command in combination with other SQL commands to use an explicit cursor to copy data from relational tables into analytic workspace objects as outlined in "Copying Relational Data into Analytic Workspace Objects".

Two pseudo procedures, SQLTABLES and SQLCOLUMNS, allow you to obtain information about tables and columns.

Syntax

SQL DECLARE cursor CURSOR FOR {select-statement [FOR UPDATE]|table-info}

where table-info is one of the following:

PROCEDURE SQLTABLES [ownertable]
PROCEDURE SQLCOLUMNS [ownertablecolumn]

Note:

You specify table-info only to declare a cursor when select-statement is a SQL FETCH statement.

Arguments

cursor

The name of the cursor you are defining. Cursor names can consist of 1 to 18 alphanumeric characters or the symbols @, _, $, or #. A name that contains symbols @, $, or # must be enclosed in single quotes. The first character cannot be a number or an underscore. Cursor names are internal to Oracle OLAP. Unless you have issued a SQL CLEANUP statement, when you try to declare a cursor with the same name as a previously declared cursor, but with a different SQL SELECT statement, an error is signaled.

select-statement

A SQL SELECT statement that identifies the data you want to associate with the cursor. For the syntax of an SQL SELECT statement, refer to Oracle Database SQL Language Reference.

Tip:

Since both OLAP DML syntax and SQL syntax allow you to use AND and OR, you should construct the clause clearly so that Oracle OLAP can identify the end of an OLAP DML input expression. Use parenthesis to clarify the syntax in these situations and when using a SQL operator that is unknown in Oracle OLAP.
FOR UPDATE

Indicates that SQL FETCH will be used to write data to the table. This clause is required when the cursor will be used in an UPDATE statement with a WHERE CURRENT OF cursor clause. The names of the columns to be updated can be listed in an OF clause (for example, FOR UPDATE OF COL1, COL2, COL3).

Note:

The FOR UPDATE clause is ignored by SQL IMPORT and SQL SELECT.
PROCEDURE SQLTABLES

When declaring a cursor for use by SQL FETCH, calls the pseudo procedure SQLTABLES, which returns the following values for each table that matches the search criterion as illustrated in Example 10-132, "Discovering Information About Relational Tables":

  • tableowner -- A text value identifying the owner of the table.

  • tablename -- A text value identifying the name of the table.

  • tabletype -- A text value identifying the type of table using one of the following: TABLE, VIEW, SYSTEM TABLE, ALIAS, SYNONYM, LOCAL TEMPORARY, GLOBAL TEMPORARY, or NA (indicating an unrecognized type).

When declaring a cursor for use by SQL IMPORT, you cannot use this clause.

PROCEDURE SQLCOLUMNS

When declaring a cursor for use by SQL FETCH, calls the pseudo procedure SQLCOLUMNS, which returns the following values for each column that matches the search criterion as illustrated in Example 10-133, "Discovering Information About the Columns of a Relational Table":

  • tableowner -- A text value identifying the owner of the table.

  • tablename -- A text value identifying the name of the table.

  • colname -- A text value identifying the name of the column.

  • coltype -- A text value identifying the data type of the column.

  • olaptype -- A text value identifying the data type that most closely matches coltype.

  • length -- An INTEGER value identifying the length of column values.

  • precision -- An INTEGER value identifying the precision of numeric column values.

  • scale -- An INTEGER value identifying the scale of column values.

  • nullable -- A text value of Y or N indicating whether the column can contain null values.

When declaring a cursor for use by SQL IMPORT, you cannot use the PROCEDURE SQLCOLUMNS clause.

owner

Literal text or the name of an OLAP DML variable whose value specifies one or more owners. This expression acts as a filter to limit the results to only tables belonging to the specified owners. The keyword NULL or an OLAP DML variable with an NA value causes all table owners to be included in the results.

The expression can be specific, such as 'SCOTT', or it can contain wildcard characters such as 'S%T' (all owners whose name begins with S and ends with T). The value retains its case when it is passed to the database, so be sure to enter the value with the appropriate use of upper- and lowercase letters. For example, Oracle relational databases by default store all values in uppercase and will not match 'scott' or 'Scott' with 'SCOTT'.

table

Literal text or the name of an OLAP DML variable whose value specifies one or more tables. This expression acts as a filter to limit the results to only tables with the specified names. The keyword NULL or an OLAP DML variable with an NA value causes all tables to be included in the results.

The expression can be specific, such as 'PAYROLL', or it can contain wildcard characters such as '%ROLL' (all tables whose name ends with ROLL). The value retains its case when it is passed to the database, so be sure to enter the value with the appropriate use of upper- and lowercase letters. For example, Oracle relational databases by default store all values in uppercase and will not match 'payroll' or 'Payroll' with 'PAYROLL'.

column

Literal text or the name of an OLAP DML variable whose value specifies one or more columns. This expression acts as a filter to limit the results to only columns with the specified names. The keyword NULL or an OLAP DML variable with an NA value causes all tables to be included in the results.

The expression can be specific, such as 'SALARY', or it can contain wildcard characters such as 'SAL%' (all columns whose name begins with SAL). The value retains its case when it is passed to the database, so be sure to enter the value with the appropriate use of upper- and lowercase letters. For example, Oracle relational databases by default store all values in uppercase and will not match 'salary' or 'Salary' with 'SALARY'.

Notes

General Restrictions that Apply to SQL DECLARE CURSOR

The following restrictions apply to the SQL DECLARE CURSOR command:

  • You can use it only in a program.

  • It cannot contain ampersand substitution.

Restrictions when Declaring a Cursor for Use by SQL IMPORT

When declaring a cursor to be used by a SQL IMPORT statement, you can only use the following simplified syntax.

SQL DECLARE cursor CURSOR FOR select-statement

where select-statement is a SQL SELECT statement that identifies the data you want to associate with the cursor. You cannot use the FOR UPDATE clause or the table-info clause.

Cursor's Result Set

A cursor's result set is determined at the time it is opened, and it is not updated later. Therefore, when you change the value of an OLAP DML input expression after you open its cursor, the change does not affect the cursor's result set. A cursor remains open until a SQL CLOSE statement is executed for that cursor or until a SQL CLEANUP statement closes all cursors. A cursor is not automatically closed at the termination of the program in which it was opened.

Optimizing Fetches

When fetching values into a multidimensional input variable, list the columns that correspond to the dimensions in an ORDER BY clause in the select-statement argument of a SQL DECLARE CURSOR statement, with the slowest-varying dimension first. This will optimize performance.

Examples

Example 10-131 Testing for the Value of SQLCODE

Cursor c1 is declared for three columns in the table mkt, which is owned by user sqldba. Values from the three columns are fetched into three analytic workspace objects. The first OLAP DML object is the market dimension, which is temporarily limited to the retrieved value. Because of the temporary status of market, the other column values are assigned to the appropriate cells of the other OLAP DML objects.

This example tests the value of SQLCODE in two places. A more complete program would do more error checking.

DEFINE market DIMENSION TEXT
DEFINE mkt.desc TEXT <market>
DEFINE mkt.abbrev ID <market>
DEFINE sql.market PROGRAM
PROGRAM
TRAP ON ERROR
SQL DECLARE c1 cursor FOR -
   SELECT mktcode, mktabbrev, mktdesc FROM sqldba.mkt
SQL OPEN c1
IF SQLCODE NE 0
   THEN SIGNAL SQLERR 'open cursor failed.'
WHILE SQLCODE EQ 0
   SQL FETCH c1 INTO :APPEND market, :mkt.abbrev, :mkt.desc
SQL CLOSE c1
   ...
RETURN
error:
   ...
END

Example 10-132 Discovering Information About Relational Tables

The following program fetches information about all tables owned by Scott. Notice that the value of the ownername variable is set after the SQL DECLARE cursor statement; it can be set any time before the SQL OPEN statement. The tablename variable is not set, but is initialized automatically to NA, which is passed as a null value.

DEFINE ownername TEXT     "Search criteria
DEFINE tablename TEXT     "Search criteria
DEFINE tblowner TEXT      "Search results
DEFINE tblname TEXT       "Search results
DEFINE tbltype TEXT       "Search results
 
SQL DECLARE c1 CURSOR FOR PROCEDURE sqltables(:ownername, :tablename)
ownername = 'Scott'
SQL OPEN c1
WHILE SQLCODE EQ 0
   DO
   SQL FETCH c1 INTO :tblowner, :tblname, :tbltype
      ...     "Process fetched values
   DOEND

Example 10-133 Discovering Information About the Columns of a Relational Table

The following program fetches information about all columns in the employee table owned by Scott. Notice that NULL (and not NA) is used for the value of the third argument to SQLCOLUMNS since it is processed by the relational manager, not Oracle OLAP.

DEFINE tblname TEXT             "Search results
DEFINE tblowner TEXT            "Search results
DEFINE colname TEXT             "Search results
DEFINE coltype TEXT             "Search results
DEFINE olaptype TEXT            "Search results
DEFINE length INTEGER           "Search results
DEFINE precision INTEGER        "Search results
DEFINE scale INTEGER            "Search results
DEFINE nullable TEXT            "Search results
 
SQL DECLARE c1 CURSOR FOR PROCEDURE sqlcolumns('Scott', -
   'Employee', NULL)
SQL OPEN c1
WHILE SQLCODE EQ 0
   DO
   SQL FETCH c1 INTO :tblowner, :tblname, :colname, :coltype, -
   :olaptype, :length, :precision, :scale, :nullable
      ...  "Process fetched values
    DOEND 

SQL EXECUTE

The SQL EXECUTE command executes SQL statements that have been compiled using SQL PREPARE. Typically, the SQL statements that you precompile are statements that will be executed repeatedly, particularly those involving OLAP DML input expressions, such as INSERT, UPDATE, and DELETE.

Note:

The SQL PREPARE and SQL EXECUTE commands can only be used within the same DML program.

Syntax

SQL EXECUTE statement-name

Arguments

statement-name

The name that you assigned to the executable code when you prepared it using SQL PREPARE.

Examples

Example 10-134 Updating a Relational Table Using Analytic Workspace Data

The next example shows a simple update of a table using data stored in Oracle OLAP. The market dimension is limited to one value at a time in the FOR loop. The SQL phrase WHERE s.market=:market specifies that the sales value in the row for that market is the value that is changed.

FOR market
   SQL UPDATE mkt SET sales=:mkt.sales WHERE s.market=:market

An UPDATE statement should be used in a SQL PREPARE statement and executed in a FOR loop.

SQL PREPARE s2 FROM UPDATE mkt -
   SET sales=:mkt.sales WHERE s.market=:market
FOR market
   DO
      SQL EXECUTE s2
      IF SQLCODE NE 0
      THEN BREAK
   DOEND

SQL FETCH

The SQL FETCH command retrieves and processes data specified by a named SQL cursor. SQL FETCH assigns the retrieved data to OLAP objects. You use the SQL FETCH command in combination with other SQL commands to copy data from relational tables into analytic workspace objects as outlined in "Copying Relational Data into Analytic Workspace Objects".

Syntax

SQL FETCH cursor [LOOP [loopcount]] -

   INTO :targets... [THEN action-statements...]

where:

  • targets is one or more of the following:

    [MATCH] dimension|surrogate
    APPEND [positiondimension
    ASSIGN surrogate
    variable | qualified data reference | relation | composite
  • position is one of the following:

    AFTER dimension-value
    BEFORE dimension-value
    FIRST
    LAST

Arguments

cursor

The name of a declared and opened cursor.

LOOP

Specifies that Oracle OLAP should implicitly loop over the rows obtained from a relational table. For each row, Oracle OLAP copies the data in individual fields to objects specified as target analytic workspace objects. When you include a LOOP clause, SQL FETCH will continue processing rows until it reaches the end of the active set specified by the cursor, or an error occurs, or loopcount is satisfied. In most cases, you should use the LOOP clause to improve the performance of SQL FETCH.

When you do not specify a LOOP clause and the cursor contains more than one row in its active set, you must code the SQL FETCH statement within a WHILE loop. This loop must be based on the value of the SQLCODE option, which returns a nonzero value to indicate the end of the data or an error.

loopcount

Optional INTEGER argument to the LOOP keyword. Loopcount controls how SQL FETCH will loop over the rows from a relational table. Loopcount can be a literal value, an OLAP DML variable, or NA. When loopcount is less than or equal to zero, no looping occurs and no data is fetched.

When you specify a LOOP clause without a value for loopcount, SQL FETCH will continue reading rows and copying their contents to target analytic workspace objects until there are no more rows or an error occurs. Internally, each row is processed until SQLCODE is nonzero.

When you specify a literal value for loopcount, SQL FETCH will process the number of rows specified by loopcount or until SQLCODE is nonzero.

When you specify a variable for loopcount, it must be in the form of an OLAP DML variable (preceded by a colon). This variable acts as both an input and an output variable. The initial value of loopcount specifies the number of rows that SQL FETCH will attempt to process. Upon completion of the SQL FETCH, loopcount contains the number of rows actually processed.

When you specify NA for loopcount, SQL FETCH will process rows until SQLCODE is nonzero. However, upon completion of the SQL FETCH, loopcount will contain the number of rows actually processed.

targets

Identifies the analytic workspace objects in which you want to store data that is retrieved from a relational table. This list of target analytic workspace objects must correspond in number and data type with the list of table columns specified in the select-statement argument of the SQL DECLARE CURSOR command that declared cursor. A target can be a variable, a qualified data reference, a relation, a dimension, a composite, or a conjoint.

Important:

The order in which you specify the target analytic workspace objects affects dimension status. For each dimension value, Oracle OLAP temporarily limits the status of the dimension to the fetched value. Values are assigned to subsequent analytic workspace objects according to this temporary status. See "Conjoints as Target Analytic Workspace Objects" and "Composites as Target Analytic Workspace Objects".

A target must be preceded by a colon. When the target is a dimension, it can include the MATCH and APPEND keywords to specify dimension handling; in this case, the colon precedes the keywords.

[MATCH] {dimension|surrogate}

(Default) Oracle OLAP does not perform dimension maintenance on the target dimension or surrogate. It uses the incoming values to align data that is being fetched into dimensioned objects. When a value from the relational database does not match any value in the dimension or surrogate, an error is signaled.

APPEND [position] dimension

Oracle OLAP performs dimension maintenance on the target dimension, adding new values to the dimension. It uses both old and new dimension values to align data being fetched into dimensioned objects. By default, new values are added to the end of a dimension or surrogate.

position is one of the following:

  • AFTER dimension-value

    Any new values are added after dimension-value in the status list.

  • BEFORE dimension-value

    Any new values are added immediately before dimension-value in the status list.

  • FIRST

    Any new values are added to the beginning of the status list.

  • LAST

    Any new values are added to the end of the status list.

The position can also be used to control how dimension values are processed in action statements.

ASSIGN surrogate

Assigns the values to the specified surrogate.

THEN action-statements...

Specifies any number of action-statements to be performed each time a row of data is fetched and assigned to target analytic workspace objects. An action-statement can be one of the following:

assignment-statement

IF statement

SELECT-statement

ACROSS statement: action-statement

<action-statement-group>

Refer to the SQL IMPORT command for a complete description of the syntax of action-statements.

Notes

Effect of Order SQL FETCH Targets on Dimension Status

For each dimension value, Oracle OLAP temporarily limits the status of the dimension to the fetched value. Values are assigned to subsequent analytic workspace objects according to this temporary status.

Conjoints as Target Analytic Workspace Objects

You can use a conjoint dimension as a target analytic workspace object, but you must ensure that you select the same number of columns from the relational table as there are simple base dimensions. When Oracle OLAP executes a SQL FETCH statement for a target that is a conjoint dimension, it uses the dimension order that was specified when the conjoint was defined.

Composites as Target Analytic Workspace Objects

You can specify analytic workspace objects for composites just as you would for dimensioned variables. For example, to fetch data into a variable var1 dimensioned by dim1 and dim2, you would specify the following list of target analytic workspace objects.

:dim1 :dim2 :var1

To fetch data into a variable var2 dimensioned by a composite whose dimensions are dim1 and dim2, you would specify the following list of target analytic workspace objects.

:dim1 :dim2 :var2

Null Values

A null value in SQL is equivalent to an NA value in Oracle OLAP, so null values fetched into target analytic workspace objects are given NA values. Since Oracle OLAP handles null values in this way, the SQL command does not support INDICATOR variables in the INTO clause of a SQL FETCH statement. When fetching null values into a dimension, however, Oracle OLAP discards the values for the entire row.

Working with Boolean Variables as Input and Target Objects

You can use Boolean variables as input and target analytic workspace objects for OLAP SQL commands. In OLAP DML input expressions, Oracle OLAP treats Boolean values as INTEGER values with a value of 1 (TRUE) or 0 (FALSE).

As target analytic workspace objects, Boolean variables can receive values from any numeric (or bit) column in a relational table.

Fetching Text Data into DATE Variables

When fetching text data into a DATE variable, the current setting of the DATEORDER option is used to interpret the value. For example, a text value of 12-08-96 could be interpreted as December 8, 1996, or August 12, 1996, depending on the setting of DATEORDER.

Untransferable Data Types

You cannot transfer data with the following data types: LONG RAW, ROWID, UROWID, BLOB, and BFILE.

Examples

Example 10-135 Fetching Data From Relational Tables -- A Simple SQL FETCH

he following program fragment shows the basic steps of declaring and opening a cursor, and fetching the data. Relational data from the Prod_ID and Prod_Name columns of the Products table are fetched into the prod dimension and prod_label variable. The variable prod_label is dimensioned by prod. Notice that the SQL FETCH statement in this example does not include a LOOP clause; it therefore retrieves a single row of data each time it is called.

VARIABLE set_price SHORT
set_price = 20
     ...
SQL DECLARE highprice CURSOR FOR SELECT Prod_ID, Prod_Name -
   FROM Products WHERE suggested_price > :set_price
SQL OPEN highprice
WHILE SQLCODE EQ 0
     SQL FETCH highprice INTO :prod, :prod_label

Example 10-136 Fetching Data From Relational Tables with a THEN Clause

The following program fragment shows the SQL FETCH statement from the previous example with the addition of the LOOP keyword and a THEN clause. Because of the LOOP keyword, this SQL FETCH statement does not need to run within a WHILE loop. The action statement following the THEN keyword copies any product names stored in prod_label that start with the letter A into a multiline text variable called a_product.

SQL FETCH highprice LOOP INTO :prod, :prod_label -
   THEN IF UPCASE(EXTCHARS(prod_label, 1, 1)) EQ 'a' -
      THEN a_product = JOINLINES(a_product prod_label)

Example 10-137 Populating with Relational Data While Maintaining a Conjoint Dimension

In this example, a conjoint dimension (named mpt) is used as a target analytic workspace object. To populate a conjoint dimension, you must select values from the relational database for each of its base dimensions. Here, the three base dimensions are market, product, and time. Therefore, the SELECT statement specifies the three corresponding columns (Mktcode, Prdcode, and Percode). The program assumes that the market, product, and time dimensions are already populated with up-to-date values; Oracle OLAP does not update the base dimensions unless you explicitly specify them as target analytic workspace objects.

DEFINE mpt DIMENSION <market product time>
DEFINE sql.mpt PROGRAM
PROGRAM
   ...
SQL DECLARE c1 CURSOR FOR -
   SELECT Mktcode, Prdcode, Percode FROM Sqldba.Data
IF SQLCODE NE 0
   THEN SIGNAL sqlerrm
SQL OPEN c1
SQL FETCH c1 LOOP INTO :append mpt
SQL CLOSE c1
   ...
END

Example 10-138 Populating Data While Maintaining Base and Conjoint Dimensions

To retrieve current values for the base and conjoint dimensions, or to retrieve the values for the first time, you can fetch the values for the base dimensions immediately before you fetch the values for the conjoint dimension. In the following example, the SQL DECLARE CURSOR and SQL FETCH commands have been edited to fetch both base and conjoint dimension values. Notice that the number of columns selected from the relational table must match the number of base dimensions fetched. There are six column specifications in the SELECT statement. The first three match the three base dimensions, and the last three match the conjoint dimension itself.

SQL DECLARE c1 CURSOR FOR -
   SELECT Mktcode, Prdcode, Percode, Mktcode,  -
      Prdcode, Percode FROM Sqldba.Data
   ...
  SQL FETCH c1 LOOP INTO :APPEND market, :APPEND product, -
   :APPEND time, :APPEND mpt

Example 10-139 Populating Variables with Relational Table Data while Maintaining Dimensions

In the next example, variable dollars.mpt is dimensioned by the conjoint mpt, and its values are populated in the same SQL FETCH statement with the dimension values. The SQL DECLARE CURSOR and SQL FETCH commands have been edited again with the new column and target analytic workspace object added.

DEFINE dollars.mpt DECIMAL <mpt>
SQL DECLARE c1 CURSOR FOR -
   SELECT Mktcode, Prdcode, Percode, Mktcode, Prdcode, -
      Percode, Dollars FROM Sqldba.Data
   ...
SQL FETCH c1 LOOP INTO :APPEND market, :APPEND product, -
   :APPEND time, :APPEND mpt, :DOLLARS.mpt 

Example 10-140 Fetching Data into a Concat Dimension

Assume that a relational table has four columns of product data and that you decide to create a Product hierarchy with four levels in your analytic workspace to hold this data. The levels in the hierarchy (prod_id, prod_subcategory, prod_category, and products_all) map to columns in the products tables. The lowest level of the hierarchy is prod_id and the highest level is products_all. There is also a column with supplier information in the table.

To hold the data in the analytic workspace you define a dimension was defined for each level of the Product hierarchy, a concat dimension for the hierarchy itself, and a child-parent relation between the values in the hierarchy. You also define a dimension for the supplier data and a relation that holds the relationship between suppliers and products with the following definitions.

DEFINE aw_prod_id DIMENSION NUMBER (6)
DEFINE aw_prod_subcategory DIMENSION TEXT
DEFINE aw_prod_category DIMENSION TEXT
DEFINE aw_products_all DIMENSION TEXT
DEFINE aw_products DIMENSION CONCAT (aw_products_all -
                                  aw_prod_category -
                                  aw_prod_subcategory -
                                  aw_prod_id)
DEFINE aw_products.parents RELATION aw_products <aw_products>
DEFINE aw_supplier_id DIMENSION NUMBER (6)
DEFINE aw_prod_id.aw_supplier_id RELATION aw_supplier_id <aw_prod_id>

Assume that you write a program named get_products_hier that consists of the following code.

' get_products_hier Program
ALLSTAT
" Fetch values into the products hierarchy
SQL DECLARE grabprods CURSOR FOR SELECT prod_total, -
                                        prod_category, -
                                        prod_subcategory, -
                                        prod_id -
                                   FROM sh.products
SQL OPEN grabprods
SQL IMPORT grabprods INTO :APPEND aw_products_all -
                              :APPEND aw_prod_category -
                              :APPEND aw_prod_subcategory -
                              :APPEND aw_prod_id 
                         
SQL CLOSE grabprods 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT
" Fetch values into supplier_id
SQL DECLARE grabsupid CURSOR FOR SELECT supplier_id -
                                 FROM sh.products
SQL OPEN grabsupid
SQL IMPORT grabsupid INTO :APPEND aw_supplier_id 
SQL CLOSE grabsupid 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT

" Populate self-relation for concat dimension
" and relation between aw_prod_id and aw_supplier_id
SQL DECLARE makerels CURSOR FOR SELECT prod_total, -
                                       prod_category, -
                                       prod_subcategory, -
                                       prod_id, -
                                       supplier_id -
                                 FROM sh.products
SQL OPEN makerels
SQL FETCH makerels LOOP INTO :MATCH aw_products_all -
                             :MATCH aw_prod_category -
                             :MATCH aw_prod_subcategory -
                             :MATCH aw_prod_id -
                             :MATCH aw_supplier_id -
            THEN aw_products.parents(aw_products aw_prod_id) -
                = aw_products(aw_prod_subcategory aw_prod_subcategory) -
            aw_products.parents(aw_products aw_prod_subcategory) -
               = aw_products(aw_prod_category aw_prod_category) -
            aw_products.parents(aw_products aw_prod_category) -
               = aw_products(aw_products_all aw_products_all) -
            aw_prod_id.aw_supplier_id = aw_supplier_id         
SQL CLOSE makerels 
SQL CLEANUP
" Update the analytic workspace and make the updates permanent
UPDATE
COMMIT

The get_products_hier program copies the data from the dimension tables into the base dimensions of the aw_products concat dimension using SQL FETCH commands with the APPEND keyword. As the base dimensions of aw_products are populated, Oracle OLAP automatically populates aw_products, itself. As the THEN clause of the SQL FETCH statement executes, Oracle OLAP fetches data into the child-parent self-relation for aw_products. This program also populates the aw_supplier_id dimension and its relation.


SQL IMPORT

The SQL IMPORT command retrieves and processes data specified by an explicit SQL cursor. SQL IMPORT assigns the retrieved data to OLAP objects. You use the SQL IMPORT command in combination with other SQL commands to copy data from relational tables into analytic workspace objects as outlined in "Copying Relational Data into Analytic Workspace Objects". SQL IMPORT is particularly effective in copying fact data from relational tables into analytic workspace variables.

Note:

You cannot transfer data with the following data types: LONG, BLOB, and BFILE.

Syntax

SQL IMPORT cursor [:var-num-of-rows |num-of-rows[:var-num-of-processed-rows]]-

INTO :targets... [THEN action-statements...]

where:

  • targets is one or more of the following:

    MATCH | MATCHSKIPERR [position] {dimension | surrogate | valueset | relation}
    APPEND dimension
    ASSIGN surrogate
    variable | relation | qualified data reference
  • action-statements is one of the following:

    assignment-statement
    IF-statement
    SELECT-statement
    ACROSS-statement: action-statement
    <action-statement-group>

 

Arguments

cursor

The name of a declared cursor.

var-num-of-rows

The name of a variable that specifies the number of rows that you want SQL IMPORT to attempt to import.

num-of-rows

A numerical constant that specifies the number of rows that you want SQL IMPORT to attempt to import.

var-num-of-processed-rows

When you include the MATCHSKIPERR keyword in the targets parameter, the name of a variable that specifies the actual number of rows that you want SQL IMPORT to import into analytic workspace objects.

targets

Identifies the analytic workspace objects in which you want to store data that is retrieved from a relational table. This list of target analytic workspace objects must correspond in number and data type with the list of table columns specified in the select-statement argument of the SQL DECLARE CURSOR command that declared cursor. A target can be a variable, a qualified data reference, a relation, a dimension, or a composite.

Important:

The order in which you specify the analytic workspace objects affects dimension status. For each dimension value, Oracle OLAP temporarily limits the status of the dimension to the fetched value. Values are assigned to subsequent analytic workspace objects according to this temporary status.
MATCH

(Default) Oracle OLAP does not copy values from the corresponding relational table column into the target dimension or surrogate. It merely uses the values to align data that is being fetched into dimensioned objects. When a value from the relational database does not match any value in the dimension, an error is signaled.

MATCHSKIPERR

Oracle OLAP does not copy values from the corresponding relational table column into the target dimension or surrogate. It merely uses the values to align data that is being fetched into dimensioned objects. When a value from the relational database does not match any value in the dimension, the value is ignored and processing continues without signaling an error.

position

The one-based logical position of the value.

APPEND

Oracle OLAP performs dimension maintenance on the target dimension, adding new values from the corresponding relational table column to the dimension. It uses both old and new dimension values to align data being fetched into dimensioned objects. New values are added to the end of a dimension.

ASSIGN

Oracle OLAP assigns the corresponding relational value to the specified surrogate.

dimension

The name of the analytic workspace dimension.

surrogate

The name of an analytic workspace surrogate.

valueset

The name of the analytic valueset.

relation

The name of the analytic workspace relation.

variable

The name of a variable.

qualified_data_reference

A QDR is a qualifier that limits one or more of the dimensions of a variable or a relation to a single value. Oracle OLAP evaluates QDRs in a SQL IMPORT statement, as follows:

  • When the QDR includes an expression, the expression is evaluated only once before the data is retrieved. In other words, the expression is, in essence, a constant.

  • When the QDR is specified as a relation name, the values of the QDR vary depending on the status of the dimensions of that relation.

THEN action-statements...

Specifies any number of action-statements to be performed each time a row of data is imported and assigned to analytic workspace objects. Action statements may contain simple assignment statements, conditional assignment statements, and assignments across dimensions.

Action statements allow you to examine and manipulate the fetched data on a row-by-row basis. For example, you may want to specify temporary objects as analytic workspace objects and only update your permanent objects once you have performed certain actions on the row of fetched data. However, action statements do not have to reference the imported data. For example, one of your action statements might be an assignment statement that executes a user-defined function (that is, a program) that performs complex processing and then simply increments a counter.

A THEN clause can improve SQL loading performance by eliminating the need for postprocessing upon completion of a SQL IMPORT.

Note:

The syntax of an action statement within SQL IMPORT is essentially the same as the syntax of an action statement within FILEREAD. Exceptions are in the syntax of an assignment statement and the use of the VALUE keyword. In SQL IMPORT action statements, assignments must be explicit; they must include a source, target, and equal sign. In FILEREAD action statements, assignments may be implicit and specify only the target. The VALUE keyword is supported in FILEREAD action statements, but not in SQL IMPORT action statements. When you have already specified action statements for use with FILEREAD, you can reuse the code with SQL IMPORT by simply adjusting the assignment statements and eliminating the VALUE keyword (if necessary). Most of the attributes listed in FILEREAD (with the exception of the attributes that control dimension processing) are not meaningful for SQL loading and are ignored when executing within SQL IMPORT.

For best performance, within a THEN clause reference only the data within the imported row.

In your list of action statements, be sure to process dimensions before variables. Oracle OLAP processes each action statement from left to right for each row in the relational table. When an action statement performs dimension processing, the resulting status remains in effect for subsequent action statements. When you do not first specify action statements that limit a variable's dimensions, Oracle OLAP uses the first value in status to target a cell in the variable. Unless you specify an ACROSS phrase, Oracle OLAP assigns a single value from a row to a single cell in an Oracle OLAP variable. By default, Oracle OLAP does not loop over a variable's dimensions when assigning data to the variable.

assignment-statement

An assignment statement (SET) that assigns a value that is the result of an expression to an Oracle OLAP object.

IF-statement

An IF...THEN...ELSE command that performs some action depending on whether a Boolean expression is TRUE or FALSE.

SELECT-statement

A SQL SELECT statement lets you perform some action based on the value of an expression. A SELECT statement has the following form.

SELECT select-expression
[WHEN expression1 action]
[WHEN expression2 action . . .]
[ELSE action]

SELECT evaluates the SELECT expression and then sequentially compares the result with the WHEN expressions. When the first match is found, the associated action occurs. When no match is found, the ELSE action (if specified) occurs.

ACROSS-statement: action-statement

An ACROSS statement causes the following action statement to execute once for every value in status of the ACROSS dimension. When you want the looping to apply to more than one action statement, enclose the action statements in angle brackets. An ACROSS statement has the following form.

ACROSS dimension [limit]:

action-statement

In an ACROSS statement, limit temporarily change the status of dimension, as long as you are not in a FOR loop over dimension. The new status is in effect only for the duration of the SQL FETCH statement. The format of limit is as follows.

[ADD|COMPLEMENT|KEEP|REMOVE|TO] limit-clause

To specify the temporary status, insert any of the LIMIT command keywords (the default is TO) along with an appropriate list of dimension values or related dimensions. You can use any valid limit clause (see the LIMIT command for further information). The following example limits month to the last six values, no matter what the current status of month is.

   ACROSS month last 6: units
<action-statement-group>

You can group several action statements together by enclosing them in angle brackets. An action-statement-group has the following form.

<action-statement1 -

[action-statement2 . . .]>

A typical use for action statement groups is after an ACROSS statement. With the angle bracket syntax, you can cause more than one action statement to execute for every value in status of the ACROSS dimension.

Notes

Effect of Order of SQL SELECT Targets on Dimension Status

For each dimension value, Oracle OLAP temporarily limits the status of the dimension to the fetched value. Values are assigned to subsequent analytic workspace objects according to this temporary status.

Working with Boolean Data Variables

You can use Boolean variables as input and target analytic workspace objects for OLAP SQL commands. In OLAP DML input expressions, Oracle OLAP treats Boolean values as INTEGER values with a value of 1 (TRUE) or 0 (FALSE).

As target analytic workspace objects, Boolean variables can receive values from any numeric (or bit) column in a relational table.

Importing Text Data into a DATE Variable

When importing text data into a DATE variable, the current setting of the DATEORDER option is used to interpret the value. For example, a text value of 12-08-96 could be interpreted as December 8, 1996, or August 12, 1996, depending on the setting of DATEORDER.

Examples

Example 10-141 Simple Import

The following program fragment shows the basic steps of declaring a cursor and importing the data. Values from the Prod_ID and Prod_Name columns of the Products relational table in the Sales -History (sh) database are fetched into the prod_id dimension and prod_label analytic workspace variable. The prod_label variable is dimensioned by prod_id.

SQL DECLARE productcur CURSOR FOR SELECT Prod_ID, Prod_Name FROM sh.Products
SQL OPEN productdur
SQL IMPORT productcur INTO :prod_id, :prod_label
SQL CLOSE productcur
SQL CLEANUP 

SQL OPEN

The SQL OPEN command activates an explicitly-declared SQL cursor. When the cursor is opened, SQL examines any OLAP DML input expressions used in the definition of the specified cursor, determines the cursor's result set, and leaves the cursor in the open state for use by SQL FETCH or SQL IMPORT. The cursor is positioned before the first row of the result set.

You use the SQL OPEN command in combination with other SQL commands to copy data from relational tables into analytic workspace objects as outlined in "Copying Relational Data into Analytic Workspace Objects".

Syntax

SQL OPEN cursor

Arguments

cursor

The name of a cursor previously declared in the same program. You cannot use ampersand substitution.

Examples

Example 10-142 Opening a Cursor Using SQL OPEN

The following program fragment declares and opens a cursor named geolabels.

SQL DECLARE geolabels CURSOR FOR -
   SELECT Store_ID, Store_Name, City FROM Stores
IF SQLCODE NE 0
   THEN SIGNAL dclerror 'SQLERRM'
SQL OPEN geolabels
IF SQLCODE NE 0
   THEN SIGNAL operror 'SQLERRM' 

SQL PREPARE

Within a program, the SQL PREPARE command precompiles a SQL statement for later execution, in the same program using SQL EXECUTE. Typically, you use SQL PREPARE in programs to optimize the processing of SQL statements that will be executed repeatedly, particularly those involving OLAP DML input expressions, such as INSERT, UPDATE, and DELETE.

Syntax

SQL PREPARE statement-name FROM sql-statement [insert-options]

Arguments

statement-name

A name that you assign to the executable code produced from sql-statement. You can redefine statement-name just by issuing another SQL PREPARE statement.

sql-statement

The SQL statement that you want to precompile for more efficient execution. It cannot contain ampersand substitution or variables that are undefined when the program is compiled.

insert-options

The following options are optional when sql-statement is an INSERT statement:

DIRECT=YES|NO specifies if the insert is a direct-path INSERT. This option must be the first option specified right aver the values phrase of the INSERT statement.

Setting this option to YES specifies that the insert will be a direct-path INSERT. Direct-path INSERT enhances performance during INSERT operations and is similar to the functionality of Oracle's direct-path loader utility, SQL*Loader. 

The default value is NO which specifies a normal INSERT.

NOLOG=YES|NO specifies if logging occurs. Setting this option to YES specifies that the redo information is not recorded in the redo log files which makes load-time faster. The default value is NO which specifies logging mode.

PARTITION=(sub)partition-name specifies that only the segments related to the named partition or subpartition are locked. When you specify this option, another session can insert data to unrelated segments in the same table. When you do not specify this option (the default), other sessions cannot insert data into the same table.

Notes

Using Direct-Path INSERT

When performing a direct-path INSERT, data is written directly into data files, bypassing the buffer cache, free space in the existing data is not reused, and the inserted data is appended after existing data in the table.

Restrictions When Using Direct-Path INSERT Direct-path INSERT is subject to a number of restrictions. When executing a direct-path INSERT using the OLAP DML, transactions in the session issuing the direct-path INSERT must be committed for the INSERT to execute successfully. (You can use the SQL or OLAP DML COMMIT to commit transactions.)

Additionally, the general restrictions that apply to using direct-path INSERT in SQL apply to preparing a direct-pathINSERT using OLAP DML PREPARE statements:

  • The target table cannot be index organized or clustered.

  • The target table cannot contain object type or LOB columns.

  • The target table cannot have any triggers or referential integrity constraints defined on it.

For more information on restrictions when using a direct-path INSERT, see the discussion of the INSERT statement in Oracle Database SQL Language Reference.

Data Type Conversions During Direct-Path Insertion Table 10-11, "Automatic Data Type Conversion During Direct-Path Insertion" shows the automatic data type conversion performed during direct-path insertion.

Table 10-11 Automatic Data Type Conversion During Direct-Path Insertion

Oracle RDBMS Oracle OLAP DML

CHAR(n), VARCHAR(n)

TEXT

LONG

TEXT with WIDE option

CHAR(8), VARCHAR(8)

ID

DATE

DATE

NUMBER(x,x)

DECIMAL (SHORTDECIMAL)

INTEGER (or NUMBER(38)

INTEGER (SHORTINTEGER)

NUMBER(1)

BOOLEAN


Inserting OLAP Text Data into a Column with a DATE Data Type When inserting text data from Oracle OLAP into a column with a DATE data type, you must use the default date format of DD MMM YY. You can use slashes (/) or hyphens (-) as separators, as well as spaces. When the data is in a different format, you can use the Oracle TO_DATE function in a SQL INSERT statement.

Inserting Large Text Values into a CLOB or NCLOB Column To insert more than 2K bytes of text data from an analytic workspace into a CLOB or NCLOB column, use the WIDE keyword before the name of the OLAP DML input expression. When the data type of the OLAP DML input expression is TEXT, then the target data type is CLOB. When the data type of the input expression is NTEXT, then the target data type is NCLOB.

The following is the syntax of an OLAP DML input expression with the WIDE keyword. See Example 10-145, "Using the WIDE Keyword" for an example.

:WIDE input-expression

See Example 10-145, "Using the WIDE Keyword" for an example.

Note that the target table must conform to these guidelines:

  • Any number and combination of CLOB and NCLOB columns

  • No LONG columns

The RDBMS imposes some restrictions on large data types. Oracle OLAP will not signal an error when you violate these restrictions. However, you might get unexpected results. Refer to the Oracle Application Developer's Guide for restrictions on large data types.

Calculating the Number of Characters

You can calculate the number of characters that will be sent to a database table from an Oracle OLAP variable by using the following formula.

NUMCHARS(variable) + 2 * (NUMLINES(variable) - 1)

This formula counts the extra carriage return and line feed characters that Oracle OLAP inserts between lines when passing the text to the database.

Examples

Example 10-143 Preparing a FOR Loop

To automatically add all the sales people from the salesperson dimension to the relational table, you could write a program and put the SQL INSERT statement in a FOR loop.

FOR salesperson
   SQL INSERT INTO Sales VALUES (:Salesperson, :Dollars) DIRECT=YES

When a statement includes OLAP DML input expressions and will be executed repeatedly, such as in a FOR loop, you can make the statements more efficient by "preparing" the SQL statement first. The INSERT statement becomes part of a PREPARE statement.

SQL PREPARE s1 FROM INSERT INTO Sales VALUES -
   (:Salesperson, :Dollars) DIRECT=YES
FOR Salesperson
   DO
      SQL EXECUTE s1
      IF SQLCODE NE 0
      THEN BREAK
   DOEND

Example 10-144 Updating a Table

The next example shows a simple update of a table using data stored in an analytic workspace. The market dimension is limited to one value at a time in the FOR loop. The SQL phrase WHERE S.Market=:market specifies that the sales value in the row for that market is the value that is changed.

FOR market
   SQL UPDATE Mkt SET Sales=:Mkt.Sales WHERE S.Market=:market

Like the INSERT statement in the previous example, an UPDATE statement should be used in a PREPARE statement and executed in an ACROSS statement or FOR loop.

SQL PREPARE s2 FROM UPDATE mkt -
   SET Sales=:mkt.sales WHERE s.market=:market
ACROSS market DO 'SQL EXECUTE s1'

Example 10-145 Using the WIDE Keyword

In both of the following statements, WIDE indicates that the target value is CLOB when var1 is TEXT, or NCLOB when var1 is NTEXT.

SQL INSERT INTO CLOB_TEST values (:dim1 :WIDE var1)
SQL UPDATE CLOB_TEXT SET clob_col = :WIDE var1 WHERE key = 1

SQL PROCEDURE

The SQL PROCEDURE command executes procedures stored in the RDBMS.

Note:

You can also create SQL stored procedures using the OLAP DML. See:

Syntax

SQL PROCEDURE procedure-name (parameters)

where parameters is one or more of the following, separated by commas:

sql-parameter
:dml-parameter

Arguments

procedure-name

The name of the SQL stored procedure.

sql-parameter

The name of a variable in the RDBMS.

:dml-parameter

An OLAP DML expressions such as a OLAP DML variable. See "Using OLAP DML Expressions in OLAP DML SQL Statements" for more information on using OLAP DML expressions in OLAP DML SQL statements.

Notes

Creating SQL Procedures using the OLAP DML

To create a stored procedure using the OLAP DML, issue an OLAP DML SQL statement with a SQL CREATE PROCEDURE statement as its argument. The syntax for coding CREATE PROCEDURE as an argument within an OLAP DML SQL statement is slightly different than the syntax for coding CREATE PROCEDURE in SQL proper. When coded as an arguments to an OLAP DML statements, use a tilde (~) instead of a semicolon as a terminator, and two colons instead of one in an assignment statement. See Example 10-146, "Creating a Stored Procedure".

Restrictions When Calling SQL Procedures using the OLAP DML

A stored procedure called using an OLAP DML SQL PROCEDURE statement cannot contain output variables or transactions.

Examples

Example 10-146 Creating a Stored Procedure

The following example shows the syntax for creating a procedure named new_products.

SQL CREATE OR REPLACE PROCEDURE new_products -
   (id CHAR, name CHAR, cost NUMBER) AS -
      price NUMBER~ -
   BEGIN -
      price ::= cost * 2.5~ -
      INSERT INTO products -
         VALUES(id, name, price)~ -
   END~

Example 10-147 Executing a Stored Procedure

The following FOR loop executes a SQL stored procedure named new_products and inserts data stored in dimensions and variables into a relational table. In this example, prod is an Oracle OLAP dimension, and labels.p and cost.p are variables dimensioned by prod.

FOR prod
   DO
      SQL PROCEDURE new_products(:prod, :labels.p, :cost.p)
      IF SQLCODE NE 0
         THEN BREAK
   DOEND 

SQL SELECT

The SQL SELECT command uses an implicit cursor to copy data from relational tables into analytic workspace objects. You use the SQL SELECT command to copy data from relational tables into analytic workspace objects using an implicit cursor. You can also use copy the data using an explicit cursor using the OLAP DML commands outlined in "Copying Relational Data into Analytic Workspace Objects".

Syntax

SQL SELECT expressions FROM tables -

[WHERE predicates] [GROUP BY expressions] -

[ORDER BY expressions] [HAVING predicates] -

INTO :targets... [THEN action-statements...]

where targets is one or more of the following:

[MATCH] dimension|surrogate
APPEND [positiondimension
ASSIGN surrogate
variable|qualified data reference|relation|composite

Arguments

SELECT expressions FROM tables-
 [WHERE predicates] [GROUP BY expressions] -
[ORDER BY expressions] [HAVING predicates]

A SQL SELECT statement that identifies the data you want to associate with the cursor. For the syntax of an SQL SELECT statement, refer to Oracle Database SQL Language Reference.

targets

Identifies the analytic workspace objects in which you want to store data that is retrieved from a relational table. This list of target analytic workspace objects must correspond in number and data type with the list of table columns specified in the SELECT statement. A target can be a variable, a qualified data reference, a relation, a dimension, or a composite.

Important:

The order in which you specify the analytic workspace objects affects dimension status. For each dimension value, Oracle OLAP temporarily limits the status of the dimension to the fetched value. Values are assigned to subsequent analytic workspace objects according to this temporary status. See "Conjoints as Target Analytic Workspace Objects" and "Composites as Target Analytic Workspace Objects".

A target must be preceded by a colon. When the target is a dimension, it can include the MATCH and APPEND keywords to specify dimension handling; in this case, the colon precedes the keywords.

Tip:

Since both OLAP DML syntax and SQL WHERE clauses allow you to use AND and OR, you should construct the targets clause clearly so that Oracle OLAP can identify the end of an OLAP DML input expression.
[MATCH] {dimension|surrogate}

(Default) Oracle OLAP does not perform dimension maintenance on the target dimension or surrogate. It uses the incoming values to align data that is being fetched into dimensioned objects. When a value from the relational database does not match any value in the dimension or surrogate, an error is signaled.

APPEND [position] dimension

Oracle OLAP performs dimension maintenance on the target dimension, adding new values to the dimension. It uses both old and new dimension values to align data being fetched into dimensioned objects. By default, new values are added to the end of a dimension or surrogate. The position can also be used to control how dimension values are processed in action statements.

ASSIGN surrogate

Assigns the values to the specified surrogate.

THEN action-statements

You may optionally include a THEN clause to specify any number of action-statements to be performed each time a row of data is fetched and assigned to analytic workspace objects. An action-statement can be one of the following:

assignment-statement
IF-statement
SELECT-statement
ACROSS-statementaction-statement
<action-statement-group>

Refer to the SQL IMPORT command for a complete description of the syntax of action-statement.

Notes

General Restrictions that APPLY to SQL SELECT

An SQL SELECT statement cannot contain ampersand substitution.

Optimizing Copies

When copying values from relational tables into a multidimensional input variable, list the columns that correspond to the dimensions in an ORDER BY clause in the select-statement argument of the SQL SELECT statement, with the slowest-varying dimension first. This will optimize performance.

Examples

Example 10-148 Simple select

For example, assume that there is a relational table named sales with the following description.

PROD_ID                     NOT NULL NUMBER(6)
CUST_ID                     NOT NULL NUMBER
TIME_ID                     NOT NULL DATE
CHANNEL_ID                  NOT NULL CHAR(1)
PROMO_ID                    NOT NULL NUMBER(6)
QUANTITY_SOLD               NOT NULL NUMBER(3)
AMOUNT_SOLD                 NOT NULL NUMBER(10,2)

Assume also that your analytic workspace contains the following definitions for corresponding analytic workspace objects.

DEFINE aw_prod_id DIMENSION NUMBER (6)
DEFINE aw_cust_id DIMENSION NUMBER (6) 
DEFINE aw_date DIMENSION TEXT
DEFINE aw_channel_id DIMENSION TEXT
DEFINE aw_promo_id DIMENSION NUMBER (6)
DEFINE aw_sales_dims COMPOSITE <aw_prod_id aw_cust_id -
     aw_channel_id aw_promo_id>
DEFINE aw_sales_quantity_sold VARIABLE NUMBER (3) <aw_date aw_sales_dims -
     <aw_prod_id aw_cust_id aw_date aw_channel_id aw_promo_id>>
DEFINE aw_sales_amount_sold VARIABLE NUMBER (10,2) <aw_date aw_sales_dims -
     <aw_prod_id aw_cust_id aw_date aw_channel_id aw_promo_id>>

To copy the data for product 415 from the sales table into the analytic workspace objects, you execute the following statement in the OLAP worksheet.

SQL SELECT prod_id cust_id time_id channel_id promo_id quantity_sold -
amount_sold WHERE prod_id = 415 -
INTO :aw_prod_id, :aw_cust_id, :aw_date,  -
:aw_channel_id, :aw_promo_id, :aw_sales_quantity_sold, :aw_sales_amount_sold