SQL

Syntax

SQL CLEANUP

Examples

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

Syntax

SQL CLOSE cursor

Parameters

cursor

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

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

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]

Parameters

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:

Because both OLAP DML syntax and SQL syntax allow you to use AND and OR, 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 is used to write data to the table. This clause is required when the cursor is 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-133, "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-134, "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 do 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 do 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 do not match 'salary' or 'Salary' with 'SALARY'.

Usage 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 which optimizes 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 

Syntax

SQL EXECUTE statement-name

Parameters

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

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

Parameters

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 continues 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, use the LOOP clause to improve the performance of SQL FETCH.

When you do not specify a LOOP clause and the cursor contains multiple rows 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 loops 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 continues 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 processes 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 attempts to process. Upon completion of the SQL FETCH, loopcount contains the number of rows actually processed.

When you specify NA for loopcount, SQL FETCH processes rows until SQLCODE is nonzero. However, upon completion of the SQL FETCH, loopcount contains 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.

Usage 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. Because 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.

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>

Parameters

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 numeric 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 only 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 only 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 (except for 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 multiple action statements, 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, if it is 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 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 multiple action statements to execute for every value in status of the ACROSS dimension.

Usage 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 

Syntax

SQL OPEN cursor

Parameters

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' 

Syntax

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

Parameters

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

Usage 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 several 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 (/), hyphens (-), or spaces as separators. 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-146, "Using the WIDE Keyword" for an example.

:WIDE input-expression

See Example 10-146, "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 does 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 are 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

Syntax

SQL PROCEDURE procedure-name (parameters)

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

sql-parameter

:dml-parameter

Parameters

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

Usage 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-147, "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 

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

Parameters

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:

Because both OLAP DML syntax and SQL WHERE clauses allow you to use AND and OR, 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.

Usage 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 which optimizes 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