Previous
Previous
 
Next
Next


SQL DECLARE CURSOR

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

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

Syntax

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

where table-info is one of the following:

PROCEDURE SQLTABLES [ownertable]
PROCEDURE SQLCOLUMNS [ownertablecolumn]

Note:

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

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:

Since 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: Discovering Information About Relational 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 the following values for each column that matches the search criterion as illustrated in Example: Discovering Information About the Columns of a Relational Table:

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:

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

Testing for the Value of SQLCODE

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

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

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

Discovering Information About Relational Tables

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

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

Discovering Information About the Columns of a Relational Table

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

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