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:

• SQL CLEANUP

• SQL CLOSE

• SQL DECLARE CURSOR

• SQL EXECUTE

• SQL FETCH

• SQL IMPORT

• SQL OPEN

• SQL PREPARE

• SQL PROCEDURE

• SQL SELECT

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.

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

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 [owner, table]

PROCEDURE SQLCOLUMNS [owner, table, column]

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

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

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

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.

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

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

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 [position] dimension

  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

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

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)

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

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

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 

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>

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

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

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

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.

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

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

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

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

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

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.

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

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~

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 [position] dimension

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

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)

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

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