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. SQL*Plus Worksheet and Oracle Enterprise Manager can be useful tools for browsing through your database.

This entry describes the 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.

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

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

• 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 the OLAP DML SQL command.

  
  

  Important: When you use the OLAP DML SQL command 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 command. 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

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 the SQL SELECT command. You can use this OLAP DML command 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 defines a SQL cursor by associating it with a SELECT statement or procedure.

  2. SQL OPEN activates a SQL cursor.

  3. SQL FETCH and SQL IMPORT retrieve and process data specified by a cursor.

  4. SQL CLOSE closes a SQL cursor.

  5. SQL CLEANUP cancels all SQL cursor declarations and frees 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 the Oracle Database SQL Reference.

A number of options are available to you when embedding SQL. These options are listed in Table 22-1, "Embedded SQL Options".

Before you use the SQL command, ensure that you have access rights to the tables that you want to use.

In this topic, OLAP DML is the "host language," an OLAP DML program is a "host program," and an OLAP DML variable used within a SQL statement is a "host variable." There are two types of host variables: input host variables and output host variables. Host variable names must be preceded by a colon (for example, :MYVAR).

Input host variables 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 input host variables in SQL WHERE clauses, parameter list for procedures, UPDATE statements, and the value clause of INSERT.

When you specify a dimension or a dimensioned variable as an input host variable, the first value in status is used; no implicit looping occurs, although you can use a FOR or ACROSS command to loop through all of the values. An input host variable can be any expression with an appropriate data type. The value of an input host variable is taken when a cursor is opened, not when it is declared. See Example 22-1, "Inserting Data in a Table".

To update or insert relational CLOB and NCLOB data, you use WIDE in the host variable for the OLAP DML expression as described in "Inserting Large Text Values into a Table" .

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.

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.

Refer to the notes for SQL PREPARE and SQL EXECUTE.

A SQL statement cannot exceed 128K bytes including the values of all non-text input host variables.

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. After you have cancelled SQL cursors, you cannot use them again unless you issue new SQL PREPARE, SQL DECLARE CURSOR, and SQL OPEN commands.

Syntax

SQL CLEANUP

Notes

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

Examples

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

SQL CLOSE

 The SQL CLOSE command closes a SQL cursor. After you have closed a cursor, you cannot use it again unless you issue a new SQL OPEN command.

Syntax

SQL CLOSE cursor

Arguments

cursor

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

Notes

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

You can change the result set associated with a cursor by closing the cursor, setting the value of an input host variable, and issuing a new SQL OPEN command. 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.

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 can be used only when declaring a cursor for use by the SQL FETCH command and is one of the following:

PROCEDURE SQLTABLES [owner, table]

PROCEDURE SQLCOLUMNS [owner, table, column]

Arguments

cursor

The name of the cursor you are defining. See "Cursor Names".

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

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 information about tables. 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 information about columns. When declaring a cursor for use by SQL IMPORT, you cannot use this clause.

owner

Literal text or the name of a host 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 a host 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 a host 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 a host 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 a host 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 a host 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

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

The following restrictions apply to the SQL DECLARE CURSOR command:

• You can use it only in a program.

• It cannot contain ampersand substitution.

When declaring a cursor to be used by the SQL IMPORT command, 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 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 command, an error is signaled.

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 input host variable after you open its cursor, the change does not affect the cursor's result set. A cursor remains open until a SQL CLOSE command is executed for that cursor or until a SQL CLEANUP command closes all cursors. A cursor is not automatically closed at the termination of the program in which it was opened.

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 the SQL DECLARE CURSOR command, with the slowest-varying dimension first. This will optimize performance.

The select-statement argument of a SQL DECLARE CURSOR command can include a WHERE clause. 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 input host variable. For example, the following WHERE clause is ambiguous, because the first host variable could be either ":MARKET AND PRDCODE" or simply ":MARKET."

... SELECT ... WHERE mktcode = :market AND prdcode = :product

Use the following construction instead.

... SELECT ... WHERE :market = mktcode AND :product = prdcode

You can also use parenthesis to clarify the syntax, particularly when using a SQL operator that is unknown in Oracle OLAP.

... SELECT ... WHERE (mktcode = :market) AND (prdcode LIKE :product)

SQLTABLES is a pseudo procedure that returns the following values for each table that matches the search criterion. See Example 22-3, "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).

SQLCOLUMNS is a pseudo procedure that returns the following values for each column that matches the search criterion. See Example 22-4, "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.

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 input host variables, 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.

Notes

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

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. When you include a THEN clause, SQL FETCH may perform processing on the retrieved data.

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 command 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, a host 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 a host variable (preceded by a colon). This variable acts as both an input and an output host 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 effects 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

[MATCH] surrogate

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. (Default)

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.

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.

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

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

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.

SQL FETCH and SQL IMPORT both copy data from relational tables into analytic workspace objects. Although SQL FETCH offers the most functionality, SQL IMPORT offers improved performance when copying large amounts of data from relational tables into 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 command for a target that is a conjoint dimension, it uses the dimension order that was specified when the conjoint was defined.

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

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 command. When fetching null values into a dimension, however, Oracle OLAP discards the values for the entire row.

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

You can use Boolean variables as input and target analytic workspace objects for OLAP SQL commands. In input host variables, Oracle OLAP treats Boolean values as integers 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.

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.

You cannot transfer data with the following data types: RAW, 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 command 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. When you include a THEN clause, SQL IMPORT may perform processing on the retrieved data SQL IMPORT is particularly effective in copying fact data from relational tables into analytic workspace variables.

Syntax

SQL IMPORT cursor 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.

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

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. (Default)

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 command, 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 includes a relation, the values of the QDR vary depending on the status of the dimensions of that relation.

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

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

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

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.

SQL FETCH and SQL IMPORT both copy data from relational tables into analytic workspace objects. SQL IMPORT offers improved performance when copying large amounts of data from relational tables into analytic workspace objects.

For the syntax to use when declaring a cursor for use by SQL IMPORT see the notes for SQL DECLARE CURSOR.

Table 22-2, "RDBMS Data Type Conversion to OLAP DML Data Types" shows which Oracle RDBMS data types the SQL IMPORT command automatically converts into Oracle OLAP data types. You must explicitly convert or cast other data types in the SELECT statement within the SQL DECLARE CURSOR command.

You can use Boolean variables as input and target analytic workspace objects for OLAP SQL commands. In input host variables, Oracle OLAP treats Boolean values as integers 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.

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.

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

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 input host variables 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.

Syntax

SQL OPEN cursor

Arguments

cursor

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

Notes

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

Examples

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

The SQL PREPARE command precompiles a SQL statement for later execution 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 input host variables, 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 command.

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

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

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

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 the OLAP DML PREPARE command:

• 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 Reference.

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

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 the SQL INSERT command.

To insert more than 2K bytes of text data from Oracle OLAP into a CLOB or NCLOB column, use the WIDE keyword before the name of the input host variable. When the input host variable is TEXT, then the target data type is CLOB. When the input host variable is NTEXT, then the target data type is NCLOB.

The following is the syntax of an input host variable with the WIDE keyword. See Example 22-15, "Using the WIDE Keyword" for an example.

:WIDE input-host-variable

See Example 22-15, "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.

You can calculate the number of characters that will be sent to the database from an input host variable by using the following formula.

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

For example, the following statement shows the number of characters that will be sent using bigvar as the input host variable.

SHOW NUMCHARS(bigvar) + 2 * (NUMLINES(bigvar) -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)

SQL PREPARE s1 FROM INSERT INTO Sales VALUES -
   (:Salesperson, :Dollars)
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:

Arguments

procedure-name

The name of the SQL stored procedure.

sql-parameter

The name of a variable in the RDBMS.

:dml-parameter

A host variable name preceded by a colon. Host variables are OLAP DML expressions such as OLAP DML variables. See "SQL Terminology" and "Input Host Variables" for more information on host variables.

Notes

To create a stored procedure using the OLAP DML, issue an OLAP DML a SQL statement with a SQL CREATE PROCEDURE statement as its argument. The syntax for coding CREATE PROCEDURE as an argument within an OLAP DML a 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 22-16, "Creating a Stored Procedure".

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.

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

position is one of the following:

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 the Oracle Database SQL 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 effects 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

[MATCH] surrogate

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. (Default)

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 statement: action-statement

• <action-statement-group>

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

Notes

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

The following restrictions apply to the SQL SELECT command cannot contain ampersand substitution.

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 command, with the slowest-varying dimension first. This will optimize performance.

The select-statement argument of a SQL SELECT command can include a WHERE clause. 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 input host variable. For example, the following WHERE clause is ambiguous, because the first host variable could be either ":MARKET AND PRDCODE" or simply ":MARKET."

... SELECT ... WHERE mktcode = :market AND prdcode = :product

Use the following construction instead.

... SELECT ... WHERE :market = mktcode AND :product = prdcode

You can also use parenthesis to clarify the syntax, particularly when using a SQL operator that is unknown in Oracle OLAP.

... SELECT ... WHERE (mktcode = :market) AND (prdcode LIKE :product)

Table 22-2, "RDBMS Data Type Conversion to OLAP DML Data Types" shows which Oracle RDBMS data types can be automatically converted into Oracle OLAP data types. You must explicitly convert or cast other data types in the SELECT statement.

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