![]() Previous |
![]() Next |
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:
Note: You specify table-info only to declare a cursor when select-statement is a SQL FETCH statement. |
Parameters
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.
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. |
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. |
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:
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.
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:
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.
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'
.
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'
.
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
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