1 DECLARE 2 ALIAS Specifies the name and the source of the database definitions to be used for module compilation, and makes the named alias part of the implicit environment of an application. You can name either a file or a repository path name to be used for the database definitions. 3 Environment You can use the DECLARE ALIAS statement: o Embedded in host language programs to be precompiled o In a context file o As part of the DECLARE section in an SQL module The alias that you declare must be different from any other alias specified in the module. 3 Format (B)0DECLARE qwqqqqqqqqqqqqqqqqqqwqk   mq> scope-options qj x  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mqwqqqqqqqqqqqqwq ALIAS FOR COMPILETIME qk   mq> <alias> qj x  lqqqqqqqqqqqqqqqqqqqqqqqqq FILENAME qq> 'attach-spec ' qwqqqqqqk   mq> PATHNAME qq> <path-name> qqqqj x  lqqqqqqqqqqqqqqqqqqqqqqqq lit-or-def-user-authentication qqj x  lqqqqqqqqqqqqqqqqqqqqqqqq RUNTIME runtime-options qj x  lqqqqqqqqqqqqqqqqqqqqqqqqq   x tq> database-options qqqqqqqqqqqqqqqqqqqqqqqqu x   x tq> attach-options qqqqqqqqqqqqqqqqqqqqqqqqqqu x   x tq> DEFAULT CHARACTER SET support-char-set qqu x   x tq> NATIONAL CHARACTER SET support-char-set qu x  x mq> DISPLAY CHARACTER SET support-char-set qqj x  mqqqqqqqqqqqqqqqqqqqqqqq USER qwq> '<username>' qwqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   mq> DEFAULT qqqqqj mqq> USING qwq> '<password>' qqwqj   mq> DEFAULT qqqqqqj    (B)0scope-options =    qwq> LOCAL qqqqqqqqqqqqqqwqq>   tq> GLOBAL qqqqqqqqqqqqqu   mq> EXTERNAL qqqqqqqqqqqj    (B)0attach-spec =    qqwqqqqqqqqqqqqqqqqwq> <file-spec> qqqqq>   mq> <node-spec> qj    (B)0node-spec =    qwq> <nodename> qwqqqqqqqqqqqqqqqqqqqwqwq>  x mq> <access-string> j x   mqqqqqqqqqqqqqqqqqq :: " <user-name> " qqwq>   mq> " " qqqj    (B)0runtime-options =     qwq> FILENAME qqwq> '<attach-spec>' wqwqqq>   x mq> <parameter> qqqj x   tq> PATHNAME qqwq> <path-name> qqwqqu   x mq> <parameter> qqj x   mq> runtime-string qqqqqqqqqqqqqqqqqqj    (B)0runtime-string =    qw> ' qw> FILENAME <attach-spec> qwqwqqqqqqqqqqqqqqqqqqqqqw> ' wq>   x m> PATHNAME <pathname> qqqqj m> literal-user-auth qj x   m> parameter qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj    (B)0database-options =    qqwqq> ELN qqqqqqqqqqqqqqqqqqqqqwqq>   tqq> NSDS qqqqqqqqqqqqqqqqqqqqu   tqq> rdb-options qqqqqqqqqqqqqu   tqq> VIDA qqqqqqqqqqqqqqqqqqqqu   tqq> VIDA V1 qqqqqqqqqqqqqqqqqu   tqq> VIDA V2 qqqqqqqqqqqqqqqqqu   tqq> VIDA V2N qqqqqqqqqqqqqqqqu   tqq> NOVIDA qqqqqqqqqqqqqqqqqqu   tqq> DBIV1 qqqqqqqqqqqqqqqqqqqu   tqq> DBIV31 qqqqqqqqqqqqqqqqqqu   mqq> DBIV70 qqqqqqqqqqqqqqqqqqj    (B)0 rdb-options =    qwq> RDBVMS qqwqq>  tq> RDB030 qqu   tq> RDB031 qqu   tq> RDB040 qqu   tq> RDB041 qqu  tq> RDB042 qqu tq> RDB050 qqu tq> RDB051 qqu tq> RDB060 qqu tq> RDB061 qqu tq> RDB070 qqu mq> RDB071 qqj (B)0attach-options =    qwq> DBKEY qwq> SCOPE IS qwq> ATTACH qqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqwq>  tq> ROWID qj mq> TRANSACTION qqj x   tq> MULTISCHEMA IS qwq> ON qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> OFF qj x   tq> PRESTARTED TRANSACTIONS ARE qwq> ON qqwqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> OFF qj x   mqwqqqqqqqwq> RESTRICTED ACCESS qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   mq> NO qj  3 Arguments 4 alias_ALIAS Specifies a name for the attach to the database. Specifying an alias lets your program refer to more than one database. You do not have to specify an alias in the DECLARE ALIAS statement. The default alias in interactive SQL and in precompiled programs is RDB$DBHANDLE. In the SQL module language, the default is the alias specified in the module header. Using the default alias (either by specifying it explicitly in the DECLARE ALIAS statement or by omitting any alias) makes the database part of the default environment. Specifying a default database means that statements that refer to the default database do not need to use an alias. If a default alias was already declared and you specify the default alias in the alias clause (or specify any alias that was already declared), you receive an error when you precompile the program or process it with the SQL module processor. 4 database-options By default, SQL uses only the database options used to compile a program as valid options for that program. If you want to use the program with other supported databases, you can override the default options by specifying database options in the ATTACH or DECLARE ALIAS statement. For more information on database options, see the Database_ Options HELP topic. 4 DBKEY_SCOPE Syntax options: DBKEY SCOPE IS ATTACH | DBKEY SCOPE IS TRANSACTION Controls when the database key of a deleted row can be used again by SQL. o The default DBKEY SCOPE IS TRANSACTION means that SQL can reuse the database key of a deleted table row (to refer to a newly inserted row) as soon as the transaction that deleted the original row completes with a COMMIT statement. (If the user who deleted the original row enters a ROLLBACK statement, then the database key for that row cannot be used again by SQL.) During the connection of the user who entered the DECLARE ALIAS statement, the DBKEY SCOPE IS TRANSACTION clause specifies that a database key is guaranteed to refer to the same row only within a particular transaction. o The DBKEY SCOPE IS ATTACH clause means that SQL cannot use the database key again (to refer to a newly inserted row) until all users who have attached with DBKEY SCOPE IS ATTACH have detached from the database. It only requires one process to attach with DBKEY SCOPE IS ATTACH to force all database users to assume this characteristic. o Oracle Corporation recommends using DBKEY SCOPE IS TRANSACTION to prevent excessive consumption of storage area space by overhead space needed to support DBKEY SCOPE IS ATTACH, and to prevent performance problems when storing new rows. During the connection of the user who entered the DECLARE ALIAS statement, the DBKEY SCOPE IS ATTACH clause specifies that a database key is guaranteed to refer to the same row until the user detaches from the database. See the DBKEY HELP topic for more information. 4 DEFAULT_CHARACTER_SET Specifies the default character set of the alias at compile time. For a list of allowable character set names, see Supported Character Sets. 4 DISPLAY_CHARACTER_SET_support-char-set Specifies the character set encoding and characteristics expected of text strings returned from Oracle Rdb. 4 FILENAME A quoted string containing full or partial information needed to access a database. For an Oracle Rdb database, an attach specification contains the file specification of the .rdb file. When you use the FILENAME argument, any changes you make to database definitions are entered only to the database system file, not to the repository. If you specify FILENAME, your application attaches to the database with that file name at run time. If you specify FILENAME: - During compilation, your application attaches to the specified database and reads metadata from the database definitions. - At run time, your application attaches to the specified database. For information regarding node-spec and file-spec, see Oracle Rdb Attach Specifications. 4 FOR_COMPILETIME Optional keyword provided for upward compatibility: DECLARE ALIAS specifies the compile-time environment by default. Specifies that the alias declared is the source of the database definition for program compiling and execution. 4 lit-or-def-user-authentication Specifies the user name and password to enable access to databases, particularly remote databases. You can use this clause to explicitly provide user name and password information in the DECLARE ALIAS statement. 4 literal-user-auth Specifies the user name and password for the specified database to be accessed at run time. For more information about when to use this clause, see the ATTACH statement. 4 MULTISCHEMA_IS Syntax options: MULTISCHEMA IS ON | MULTISCHEMA IS OFF The MULTISCHEMA IS ON clause enables multischema naming for the duration of the database attach. The MULTISCHEMA IS OFF clause disables multischema naming for the duration of the database attach. Multischema naming is disabled by default. 4 NATIONAL_CHARACTER_SET Specifies the national character set of the alias at compile time. For a list of allowable character set names, see Supported Character Sets. 4 PATHNAME A full or relative repository path name that specifies the source of the database definitions. When you use the PATHNAME argument, any changes you make to database definitions are entered in both the repository and the database system file. Oracle Rdb recommends using the PATHNAME argument if you have the repository on your system and you plan to use any data definition statements. If you specify PATHNAME: o During compilation, your application attaches to the repository database definition and reads metadata from the dictionary definitions. SQL extracts the file name of the Oracle Rdb database from the dictionary and saves it for use at run time. o At run time, your application attaches to the Oracle Rdb database file name extracted from the dictionary at compilation. 4 PRESTARTED_TRANSACTIONS_ARE Syntax options: PRESTARTED TRANSACTIONS ARE ON | PRESTARTED TRANSACTIONS ARE OFF Specifies whether Oracle Rdb enables or disables prestarted transactions. Use the PRESTARTED TRANSACTIONS ARE OFF clause only if your application uses a server process that is attached to the database for long periods of time and causes the snapshot file to grow excessively. If you use the PRESTARTED TRANSACTIONS ARE OFF clause, Oracle Rdb may require additional I/O as each SET TRANSACTION statement must reserve a transaction sequence number (TSN). For most applications, Oracle Rdb recommends that you enable prestarted transactions. The default is PRESTARTED TRANSACTIONS ARE ON. If you use the PRESTARTED TRANSACTIONS ARE ON clause or do not specify the PRESTARTED TRANSACTIONS clause, the COMMIT or ROLLBACK statement for the previous read/write transaction automatically reserves the TSN for the next transaction and reduces I/O. You can use ALTER DATABASE . . . PRESTARTED TRANSACTIONS clause to establish a default setting for all applications using the database. You can also define the RDMS$BIND_PRESTART_TXN logical name to define the default setting for prestarted transactions outside of an application. The PRESTARTED TRANSACTION clause overrides this logical name and database setting. For more information, see the Oracle Rdb7 Guide to Database Performance and Tuning. 4 RESTRICTED_ACCESS Syntax options: RESTRICTED ACCESS | NO RESTRICTED ACCESS Restricts access to the database. This allows you to access the database but locks out all other users until you disconnect from the database. Setting restricted access to the database requires DBADM privileges. The default is NO RESTRICTED ACCESS if not specified. 4 ROWID_SCOPE Syntax options: ROWID SCOPE IS ATTACH | ROWID SCOPE IS TRANSACTION The ROWID keyword is a synonym for the DBKEY keyword. See the DBKEY_SCOPE argument for more information. 4 RUNTIME Specifies the source of the database definitions when the program is run. 4 runtime-string A quoted string or parameter that specifies the file name or path name of the database to be accessed at run time, and optionally, the user name and password of the user accessing the database at run time. 4 scope-options LOCAL | GLOBAL | EXTERNAL Specifies the scope of the alias declaration in precompiled SQL or SQL module language. The scope-option declarations are: o LOCAL declares an alias that is local to procedures in the module in which it is declared, or local to dynamic statements prepared in the module in which it is declared. SQL attaches to a database with LOCAL scope only when you execute a procedure in the same module without a session. The alias of a database with LOCAL scope pertains only to that module. If the execution of a procedure in another module has attached to the implicit environment and that procedure subsequently calls another procedure that references a local database, SQL attempts to attach to that local database. If no transaction is active, SQL adds the local database to the implicit environment for this module. If a transaction is active, SQL returns an error message. o GLOBAL declares an alias definition that is global to procedures in the application. GLOBAL is the default. o EXTERNAL declares an external reference to a global alias that is defined in another module. In single-image applications, the distinction between alias definitions and alias references is often unimportant. It is only necessary that each alias have at least one definition. For this reason, Oracle Rdb has treated all alias references (declared with the EXTERNAL keyword) the same as alias definitions (declared with the GLOBAL keyword or the default.) For compatibility with previous versions, this remains the default. However, applications that share aliases between multiple images require a distinction between alias definitions and alias references. All definitions of any aliases shared between multiple OpenVMS images must be defined in one image, generally the shareable image against which you link the other images. Oracle Rdb recommends that you distinquish alias definitions from alias references in any new source code. Use the GLOBAL (or default) scope keyword for alias definitions and the EXTERNAL keyword for alias references. If you share aliases between multiple OpenVMS images, use the NOEXTERNAL_GLOBALS command line qualifier to override the default and cause SQL to properly treat alias references as references. If you use the EXTERNAL_GLOBAL command line qualifier, SQL treats aliases declared with the EXTERNAL keyword as GLOBAL. That is, SQL initializes alias references as well as alias definitions. If you use the NOEXTERNAL_GLOBAL command line qualifier, SQL treats aliases declared with the EXTERNAL keyword as alias references and does not initialize them. It initializes all other aliases. The EXTERNAL_GLOBAL qualifier is the default. The [NO]INITIALIZE_HANDLES command line qualifiers also affect the initialization of aliases, but they are recommended only for use in versions prior to V7.0. See the SQL Module Language and SQL Precompiler help topics for more information about the command line qualifiers. 4 USER Syntax options: username | DEFAULT Specifies the operating system user name that the database system uses for privilege checking. You can specify a character string literal for the user name or you can specify the DEFAULT keyword. The DEFAULT keyword allows you to avoid placing the user name in a program's source code. If you specify the DEFAULT keyword, you pass the user name to the program by using a command line qualifier when you compile an SQL module or precompiled program. You use the USERNAME qualifier. 4 USING Syntax options: USING 'password' | DEFAULT Specifies the user's password for the user name specified in the USER clause. You can specify a character string literal for the PASSWORD or you can specify the DEFAULT keyword. The DEFAULT keyword allows you to avoid placing the user name in a program's source code. If you specify the DEFAULT keyword, you pass the password to the program by using a command line qualifier when you compile an SQL module or precompiled program. You use the PASSWORD qualifier. 3 Examples Example 1: Specifying a database and an alias in embedded SQL This statement declares the database defined by the file specification personnel. The precompiler uses this definition when compiling the program and SQL uses the file personnel when the program runs. This name may be a logical name or the name portion of the file personnel.rdb. EXEC SQL DECLARE PERS_ALIAS ALIAS FOR FILENAME personnel END-EXEC Example 2: Specifying a database with restricted access This statement is the same as Example 1, but specifies restricted access to the database. EXEC SQL DECLARE PERS_ALIAS ALIAS FOR FILENAME personnel RESTRICTED ACCESS END-EXEC Example 3: Specifying the DECLARE ALIAS statement This portion of an application program declares the databases MIA1 and MIA_CHAR_SET. The precompiler uses the MIA1 database when compiling the program and SQL uses the MIA_CHAR_SET database when the program runs. EXEC SQL DECLARE ALIAS COMPILETIME FILENAME MIA1 RUNTIME FILENAME MIA_CHAR_SET DEFAULT CHARACTER SET DEC_KANJI NATIONAL CHARACTER SET KANJI; Example 4: Specifying the DEFAULT user authentication The following example shows how to use the DEFAULT clause for user name and password in an SQL module: MODULE TEST_DECLARE DIALECT SQL99 LANGUAGE C PARAMETER COLONS ALIAS RDB$DBHANDLE ------------------------------------------------------- -----------------------declarations-------------------- DECLARE ALIAS COMPILETIME FILENAME mf_personnel USER DEFAULT USING DEFAULT RUNTIME :run_time_spec . . . You pass the compile-time user name and password to the program by using command line qualifiers. For example, to compile the program use the following command line: $ SQLMOD TESTDEC /USER=heleng /PASS= helenspasswd At run time, the host language program can prompt the run- time user to specify only the file specification or the file specification and the user name and password at run time. The host language program can build the run time string. For example, if the host language program uses only the file specification, the value of the variable passed to the program can be the following: FILENAME "mf_personnel" If the host language program uses the file specification, user name and password, the value of the variable passed to the program can be the following: FILENAME "mf_personnel 'USER heleng' USING 'mypassword' " You must enclose the string in quotation marks; whether you use single (') or double quotation marks (") depends upon the programming language. If you use the following DECLARE ALIAS statement, the host language program can only prompt the run-time user to specify the file name. DECLARE ALIAS COMPILETIME FILENAME mf_personnel USER DEFAULT USING DEFAULT RUNTIME FILENAME :foo 2 CURSOR Declares a cursor. With cursors, the conditions that define the result table are specified by the select expression in the DECLARE CURSOR statement. SQL creates the result table when it executes an OPEN statement. The result table for a cursor exists until a CLOSE, COMMIT, or ROLLBACK statement executes, the program stops, or you exit from interactive SQL. However, the result table can exist across transactions if you define a holdable cursor. A holdable cursor can remain open and retain its position when a new SQL transaction begins. Host language programs require cursors because programs must perform operations one row or element at a time, and therefore may execute statements more than once to process an entire result table or list. The scope of a cursor describes the portion of a module or program where the cursor is valid. The extent of a cursor tells how long it is valid. All cursors in SQL have the scope of the entire module. You can create three classes of cursors, depending on which DECLARE CURSOR statement you use: o The DECLARE CURSOR statement is executed immediately. A cursor that you create with this statement, sometimes called a static cursor, exists only within the scope and extent of its module. Both the cursor name and SELECT statement are known to your application at compile time. o The dynamic DECLARE CURSOR statement is executed immediately. The cursor name is known at compile time, and the SELECT statement is determined at run time. You must supply a name for the SELECT statement that is generated at run time. A dynamic cursor exists within the scope of its module, but its extent is the entire run of the program or image. For information about the dynamic DECLARE CURSOR statement, see the DECLARE Dynamic_CURSOR statement. o The extended dynamic DECLARE CURSOR statement must be precompiled or used as part of a procedure in an SQL module. You must supply parameters for the cursor name and for the identifier of a prepared SELECT statement that is generated at run time. An extended dynamic cursor exists within the scope and extent of the entire module. For information about the extended dynamic DECLARE CURSOR statement, see the DECLARE Extended_Dynamic_CURSOR statement. Within each class, you can create two types of cursors: o Table cursors are a method that SQL provides to access individual rows of a result table. (A result table is a temporary collection of columns and rows from one or more tables or views.) o List cursors are a method that SQL provides to access individual elements in a list. A list is an ordered collection of elements, or segments, of the data type LIST OF BYTE VARYING. For more information about the LIST OF BYTE VARYING data type, see the Data_Types HELP topic. List cursors enable users to scan through a very large data structure from within a language that does not provide support for objects of such size. Because lists exist as a set of elements within a row of a table, a list cursor must refer to a table cursor because the table cursor provides the row context. Cursors are further divided according to the modes of operations that they can perform. Table cursors have four modes: o Update cursors are the default table cursor. Rows are first read and locked for SHARED READ or PROTECTED READ and then later, when an UPDATE is performed, the rows are locked for EXCLUSIVE access. If the table is reserved for EXCLUSIVE access, the subsequent update lock is not required. o Read-only cursors can be used to access row information from a result table whenever you do not intend to update the database. For example, you could use a read-only cursor to fetch row and column information for display. o Insert-only cursors position themselves on a row that has just been inserted so that you can load lists into that row. o Update-only cursors are used whenever you intend to modify many rows in the result table. When the UPDATE ONLY option is used, SQL uses a more aggressive lock mode that locks the rows for EXCLUSIVE access when first read. This mode avoids a lock promotion from SHARED READ or PROTECTED READ to EXCLUSIVE access. It may, therefore, avoid deadlocks normally encountered during the lock promotion. List cursors have two modes: o Read-only cursors are the default list cursor. They enable you to read existing lists. By adding the SCROLL keyword to the read-only list cursor clause, you enable Oracle Rdb to scroll forward and backward through the list segments as needed. o Insert-only cursors enable you to insert data into a list. The following table lists the classes, types, and modes of cursors that SQL provides. Table 1-2 Classes, Types, and Modes of Cursors Dynamic Extended Dynamic DECLARE CURSOR DECLARE CURSOR DECLARE CURSOR Table List Table List Table List Insert- Insert- Insert- Insert- Insert- Insert- only only only only only only Read- Read- Read- Read- Read- Read- only only only only only only Update- Update- Update- only only only For example, you must declare an insert-only table cursor to insert data into a table. If the table includes lists, use the table cursor to position on the correct row, and declare an insert-only list cursor to load the lists into that row. For details about using cursors to load data into your database, see the INSERT statement. To process the rows of a result table formed by a DECLARE CURSOR statement, you must use the OPEN statement to position the cursor before the first row. Subsequent FETCH statements retrieve the values of each row for display on the terminal or processing in a program. (You must close the cursor before you attempt to reopen it.)You can similarly process the elements of a list by using an OPEN statement to position the cursor before the first element in the list and repeating FETCH statements to retrieve successive elements. 3 Environment You can use the DECLARE CURSOR statement: o In interactive SQL o Embedded in host language programs to be precompiled o As part of the DECLARE section in an SQL module o In a context file 3 Format (B)0DECLARE qqqqqqqqqqqqqqqqqqqqk  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mqwqwqqqqqqqqqqqqqqqqwqq> TABLE CURSOR qqqwqqqqqqqqqqqqqqqqwqk   x tq> INSERT ONLY qu mq> with-clause qj x   x tq> READ ONLY qqqu x   x mq> UPDATE ONLY qj x   x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   x mq> FOR q> select-expr qqwqqqqqqqqqqqqqqqqqqqqqqwqqk   x mq> for-update-clause qj x   x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   x mqwqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>  x mq> optimize-clause qj x   mwqqqqqqqqqqqqqqqwwqqqqqqqqqwqwq> LIST CURSOR FOR SELECT k x   t> READ ONLY qqqjm> SCROLL j x x x   m> INSERT ONLY qqqqqqqqqqqqqqj x x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x   m> <column-name> WHERE CURRENT OF qqqqqj    (B)0with-clause =    qqq> WITH qq> HOLD qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mq> PRESERVE qqwq> ON COMMIT qqqu   tq> ON ROLLBACK qu   tq> ALL qqqqqqqqqu   mq> NONE qqqqqqqqj    (B)0select-expr =        qwqwq> select-clause qqqqqqqqqqqqwqwqqqqqqk    x tq> ( select-expr ) qqqqqqqqqqqu x  x      x mq> TABLE table-ref qqqqqqqqqqj x x     mqqqqqq select-merge-clause <qqqqqqqj   x  lqqqqqqqqqqqqqqqqqqq     mq> order-by-clause qj mq> offset-clause qj  mq> limit-to-clause qj (B)0for-update-clause =    qq> FOR UPDATE qwqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mqwq> OF <column-name> qwqj   mqqqqqqqq ,   mq> OPTIMIZE qqwqwq> FOR qwq> FAST FIRST qqqqqqqqwqqqqqqqqqqwqwqj   x x tq> TOTAL TIME qqqqqqqqu   x x  x x mq> SEQUENTIAL ACCESS qj x x  x tq> USING <outline-name> qqqqqqqqqqqqqqqqqqu x  x tq> WITH qwq> DEFAULT qqwq> SELECTIVITY qu x x x tq> SAMPLED qqu x x x x mq> AGGRESSIVE j x x  x mq> AS <query-name> qqqqqqqqqqqqqqqqqqqqqqqj x   mqqqqqqqqqqqqqqqq DECLARE TEMP1 TABLE CURSOR cont> FOR cont> SELECT * cont> FROM EMPLOYEES cont> WHERE EMPLOYEE_ID > '00400' cont> OPTIMIZE FOR FAST FIRST; SQL> -- SQL> DECLARE TEMP2 TABLE CURSOR cont> FOR cont> SELECT LAST_NAME, FIRST_NAME cont> FROM EMPLOYEES cont> ORDER BY LAST_NAME cont> OPTIMIZE FOR TOTAL TIME; o SEQUENTIAL ACCESS Forces the use of sequential access. This is particularly valuable for tables that use the strict partitioning functionality. 4 OPTIMIZE_USING_outline_name Explicitly names the query outline to be used with the select expression even if the outline IDs for the select expression and for the outline are different. See the CREATE OUTLINE statement for more information on creating an outline. 4 OPTIMIZE_WITH Selects one of three optimization controls: DEFAULT (as used by previous versions of Oracle Rdb), AGGRESSIVE (assumes smaller numbers of rows will be selected), and SAMPLED (which uses literals in the query to perform preliminary estimation on indices). 4 preserve-clause Syntax options: PRESERVE ON COMMIT PRESERVE ON ROLLBACK PRESERVE ALL PRESERVE NONE Specifies when a cursor remains open. o PRESERVE ON COMMIT On commit, all cursors close except those defined with the WITH HOLD PRESERVE ON COMMIT syntax. On rollback, all cursors close including those defined with the WITH HOLD PRESERVE ON COMMIT syntax. This is the same as specifying the WITH HOLD clause without any preserve options. o PRESERVE ON ROLLBACK On rollback, all cursors close except those defined with the WITH HOLD PRESERVE ON ROLLBACK syntax. On commit, all cursors close including those defined with the WITH HOLD PRESERVE ON ROLLBACK syntax. o PRESERVE ALL All cursors remain open after commit or rollback. Cursors close with the CLOSE statement or when the session ends. o PRESERVE NONE All cursors close after a CLOSE, COMMIT, or ROLLBACK statement, when the program stops, or when you exit from interactive SQL. This is the same as not specifying the WITH HOLD clause at all. 4 READ_ONLY Specifies that the cursor is not used to update the database. 4 SCROLL Specifies that Oracle Rdb can read the items in a list from either direction (up or down) or at random. The SCROLL keyword must be used if the following fetch options are desired: o NEXT o PRIOR o FIRST o LAST o RELATIVE o ABSOLUTE If SCROLL is not specified, the default for FETCH is NEXT. SCROLL is only supported for LIST cursors. 4 TABLE_CURSOR Specifies that the cursor you want to declare is a table cursor, rather than a list cursor. If you do not specify a cursor type, SQL declares a table cursor by default. 4 UPDATE_ONLY Specifies that the cursor is used to update the database. Use an update-only cursor when you plan to update most of the rows you are fetching. The update-only cursor causes Oracle Rdb to apply more restrictive locking during the initial read operation, so that locks do not need to be upgraded later from READ to exclusive WRITE. This reduces the total number of lock requests per query, and may help to avoid deadlocks. Use update-only table cursors to modify table rows. SQL does not allow update-only list cursors. 4 WHERE_CURRENT_OF Specifies the table cursor that provides the row context for the list cursor. The table cursor named must be defined using a DECLARE CURSOR statement. 4 WITH_HOLD Indicates that the cursor remain open and maintain its position after the transaction ends. This is called a holdable cursor. 3 Examples Example 1: Declaring a table cursor in interactive SQL The following example declares a cursor named SALARY_INFO. The result table for SALARY_INFO contains the names and current salaries of employees and is sorted by last name. SQL> -- SQL> DECLARE SALARY_INFO CURSOR FOR cont> SELECT E.FIRST_NAME, E.LAST_NAME, S.SALARY_AMOUNT cont> FROM EMPLOYEES E, SALARY_HISTORY S cont> WHERE E.EMPLOYEE_ID = S.EMPLOYEE_ID cont> AND cont> S.SALARY_END IS NULL cont> ORDER BY cont> E.LAST_NAME ASC; SQL> -- SQL> -- Use an OPEN statement to open the cursor and SQL> -- position it before the first row of the SQL> -- result table: SQL> OPEN SALARY_INFO; SQL> -- SQL> -- Finally, use two FETCH statements to see the SQL> -- first two rows of the cursor: SQL> FETCH SALARY_INFO; E.FIRST_NAME E.LAST_NAME S.SALARY_AMOUNT Louie Ames $26,743.00 SQL> FETCH SALARY_INFO; E.FIRST_NAME E.LAST_NAME S.SALARY_AMOUNT Leslie Andriola $50,424.00 Example 2: Declaring a table cursor in a C program This simple program uses embedded DECLARE CURSOR, OPEN, and FETCH statements to retrieve and print the names and departments of managers. #include void main () { int SQLCODE; char FNAME[15]; char LNAME[15]; char DNAME[31]; /* Declare the cursor: */ exec sql DECLARE MANAGER CURSOR FOR SELECT E.FIRST_NAME, E.LAST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE E.EMPLOYEE_ID = D.MANAGER_ID ; /* Open the cursor: */ exec sql OPEN MANAGER; /* Start a loop to process the rows of the cursor: */ for (;;) { /* Retrieve the rows of the cursor and put the value in host language variables: */ exec sql FETCH MANAGER INTO :FNAME, :LNAME, :DNAME; if (SQLCODE != 0) break; /* Print the values in the variables: */ printf ("%s %s %s\n", FNAME, LNAME, DNAME); } /* Close the cursor: */ exec sql CLOSE MANAGER; } Example 3: Using table and list cursors to retrieve list data in interactive SQL The following example declares a table and list cursor to retrieve list information: SQL> DECLARE TBLCURSOR INSERT ONLY TABLE CURSOR FOR cont> SELECT EMPLOYEE_ID, RESUME FROM RESUMES; SQL> DECLARE LSTCURSOR INSERT ONLY LIST CURSOR FOR SELECT RESUME WHERE CURRENT OF TBLCURSOR; SQL> OPEN TBLCURSOR; SQL> INSERT INTO CURSOR TBLCURSOR (EMPLOYEE_ID) VALUES ('00164'); 1 row inserted SQL> OPEN LSTCURSOR; SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('This is the resume for 00164'); SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('Boston, MA'); SQL> INSERT INTO CURSOR LSTCURSOR VALUES ('Oracle Corporation'); SQL> CLOSE LSTCURSOR; SQL> CLOSE TBLCURSOR; SQL> COMMIT; SQL> DECLARE TBLCURSOR2 CURSOR FOR SELECT EMPLOYEE_ID, cont> RESUME FROM RESUMES; SQL> DECLARE LSTCURSOR2 LIST CURSOR FOR SELECT RESUME WHERE CURRENT OF TBLCURSOR2; SQL> OPEN TBLCURSOR2; SQL> FETCH TBLCURSOR2; 00164 SQL> OPEN LSTCURSOR2; SQL> FETCH LSTCURSOR2; RESUME This is the resume for 00164 SQL> FETCH LSTCURSOR2; RESUME Boston, MA SQL> FETCH LSTCURSOR2; RESUME Oracle Corporation SQL> FETCH LSTCURSOR2; RESUME %RDB-E-STREAM_EOF, attempt to fetch past end of record stream SQL> CLOSE LSTCURSOR2; SQL> SELECT * FROM RESUMES; EMPLOYEE_ID RESUME 00164 1:701:2 1 row selected SQL> CLOSE TBLCURSOR2; SQL> COMMIT; Example 4: Using the scroll attribute for a list cursor The following example declares a table and read-only scrollable list cursor to retrieve list information by scrolling back and forth between segments of the list: SQL> DECLARE CURSOR_ONE cont> TABLE CURSOR FOR cont> (SELECT EMPLOYEE_ID,RESUME FROM RESUMES); SQL> -- SQL> DECLARE CURSOR_TWO cont> READ ONLY cont> SCROLL cont> LIST CURSOR cont> FOR SELECT RESUME cont> WHERE CURRENT OF CURSOR_ONE; Example 5: Declaring a holdable cursor SQL> -- Declare a holdable cursor that remains open on COMMIT SQL> -- SQL> DECLARE curs1 CURSOR cont> WITH HOLD PRESERVE ON COMMIT cont> FOR SELECT e.first_name, e.last_name cont> FROM employees e cont> ORDER BY e.last_name; SQL> OPEN curs1; SQL> FETCH curs1; FIRST_NAME LAST_NAME Louie Ames SQL> FETCH curs1; FIRST_NAME LAST_NAME Leslie Andriola SQL> COMMIT; SQL> FETCH curs1; FIRST_NAME LAST_NAME Joseph Babbin SQL> FETCH curs1; FIRST_NAME LAST_NAME Dean Bartlett SQL> ROLLBACK; SQL> FETCH curs1; %SQL-F-CURNOTOPE, Cursor CURS1 is not opened SQL> -- SQL> -- Declare another holdable cursor that remains open always SQL> -- SQL> DECLARE curs2 CURSOR cont> WITH HOLD PRESERVE ALL cont> FOR SELECT e.first_name, e.last_name cont> FROM employees e cont> ORDER BY e.last_name; SQL> OPEN curs2; SQL> FETCH curs2; FIRST_NAME LAST_NAME Louie Ames SQL> FETCH curs2; FIRST_NAME LAST_NAME Leslie Andriola SQL> COMMIT; SQL> FETCH curs2; FIRST_NAME LAST_NAME Joseph Babbin SQL> FETCH curs2; FIRST_NAME LAST_NAME Dean Bartlett SQL> ROLLBACK; SQL> FETCH curs2; FIRST_NAME LAST_NAME Wes Bartlett 2 Dynamic_CURSOR Declares a cursor where the SELECT statement is supplied at run time in a parameter. Refer to the DECLARE CURSOR for a detailed description of statement elements that apply to both dynamic and nondynamic DECLARE CURSOR statements. 3 Environment You can use the dynamic DECLARE CURSOR statement: o Embedded in host language programs to be precompiled o As part of the DECLARE statement section in an SQL module 3 Format (B)0DECLARE qqqqqqqqqqqqqqqqqqqqqqk  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mqwqwqqqqqqqqqqqqqqqqwq> TABLE CURSOR qwqqqqqqqqqqqqqqqqwqk   x tq> INSERT ONLY qu mq> with-clause qj x   x tq> READ ONLY qqqu x   x mq> UPDATE ONLY qj x   x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   x mq> FOR q> <statement-name> qqqqqqqqqqqqqqqqqqqqqqqqwqq>  mwqqqqqqqqqqqqqqwwqqqqqqqqqqwwq> LIST CURSOR qqqqqqk x   tq> READ ONLY qjm> SCROLL qjx x x   mq> INSERT ONLY qqqqqqqqqqqqj x x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x   mq> FOR q> <statement-name> qqqqqqqqqqqqqqqqqqqqqqqqqj    (B)0with-clause =    qqq> WITH qq> HOLD qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mq> PRESERVE qqwq> ON COMMIT qqqu   tq> ON ROLLBACK qu   tq> ALL qqqqqqqqqu   mq> NONE qqqqqqqqj    3 Arguments 4 cursor-name The name of the cursor you want to declare. Use a name that is unique among all the cursor names in the module. Use any valid SQL name. See the User_Supplied_Names HELP topic for more information on identifiers. 4 FOR_statement_name A name that identifies a prepared SELECT statement that is generated at run time. 4 INSERT_ONLY Specifies that a new list or a new row is created or opened. 4 LIST_CURSOR Specifies that you are declaring a cursor to access the elements in a list. 4 preserve-clause Syntax options: PRESERVE ON COMMIT PRESERVE ON ROLLBACK PRESERVE ALL PRESERVE NONE Specifies when a cursor remains open. o PRESERVE ON COMMIT On commit, all cursors close except those defined with the WITH HOLD PRESERVE ON COMMIT syntax. On rollback, all cursors close including those defined with the WITH HOLD PRESERVE ON COMMIT syntax. This is the same as specifying the WITH HOLD clause without any preserve options. o PRESERVE ON ROLLBACK On rollback, all cursors close except those defined with the WITH HOLD PRESERVE ON ROLLBACK syntax. On commit, all cursors close including those defined with the WITH HOLD PRESERVE ON ROLLBACK syntax. o PRESERVE ALL All cursors remain open after commit or rollback. Cursors close with the CLOSE statement or when the session ends. o PRESERVE NONE All cursors close after a CLOSE, COMMIT, or ROLLBACK statement, when the program stops, or when you exit from interactive SQL. This is the same as not specifying the WITH HOLD clause at all. 4 READ_ONLY Specifies that the cursor is not used to update the database. 4 SCROLL Specifies that Oracle Rdb can read the items in a list from either direction (up or down) or at random. 4 TABLE_CURSOR Specifies that you are declaring a cursor to access the rows in a table. 4 UPDATE_ONLY Specifies that the cursor is used to update the database. 4 WITH_HOLD Indicates that the cursor remain open and maintain its position after the transaction ends. This is called a holdable cursor. 3 Examples Example 1: Using a parameter for a statement name . . . * This program prepares a statement for dynamic execution from the string * passed to it, and uses a dynamic cursor to fetch a row from a table. * */ #include #include struct SQLDA_STRUCT { char SQLDAID[8]; int SQLDABC; short SQLN; short SQLD; struct { short SQLTYPE; short SQLLEN; char *SQLDATA; short *SQLIND; short SQLNAME_LEN; char SQLNAME[30]; } SQLVAR[]; } *SQLDA; main() { /* * General purpose locals */ int i; long sqlcode; char command_string[256]; /* * Allocate SQLDA structures. */ SQLDA = malloc(500); SQLDA->SQLN = 20; /* Get the SELECT statement at run time. */ printf("\n Enter a SELECT statement.\n"); printf("\n Do not end the statement with a semicolon.\n"); gets(command_string); /* Prepare the SELECT statement. */ PREP_STMT( &sqlcode, &command_string, SQLDA ); if (sqlcode != 0) goto err; /* Open the cursor. */ OPEN_CURSOR( &sqlcode ); if (sqlcode != 0) goto err; /* Allocate memory. */ for (i=0; i < SQLDA->SQLD; i++) { SQLDA->SQLVAR[i].SQLDATA = malloc( SQLDA->SQLVAR[i].SQLLEN ); SQLDA->SQLVAR[i].SQLIND = malloc( 2 ); } /* Fetch a row. */ FETCH_CURSOR( &sqlcode, SQLDA ); if (sqlcode != 0) goto err; /* Use the SQLDA to determine the data type of each column in the row and print the column. For simplicity, test for only two data types. CHAR and INT. */ for (i=0; i < SQLDA->SQLD; i++) { switch (SQLDA->SQLVAR[i].SQLTYPE) { case SQLDA_CHAR; /* Character */ printf( "%s", SQLDA->SQLVAR[i].SQLDATA ); break; case SQLDA_INTEGER: /* Integer */ printf( "%d", SQLDA->SQLVAR[i].SQLDATA ); break; default: printf( "Some other datatype encountered\n"); } } /* Close the cursor. */ CLOSE_CURSOR( &sqlcode ); ROLLBACK(&sqlcode ); return; . . . } Example 2: SQL module file that the preceding program calls -- This program uses dynamic cursors to fetch a row. -- -- MODULE C_MOD_DYN_CURS LANGUAGE C AUTHORIZATION RDB$DBHANDLE DECLARE ALIAS FOR FILENAME personnel -- Declare the dynamic cursor. Use a statement name to identify a -- prepared SELECT statement. DECLARE CURSOR1 CURSOR FOR STMT_NAME -- Prepare the statement from a statement entered at run time -- and specify that SQL write information about the number and -- data type of select list items to the SQLDA. PROCEDURE PREP_STMT SQLCODE COMMAND_STRING CHAR (256) SQLDA; PREPARE STMT_NAME SELECT LIST INTO SQLDA FROM COMMAND_STRING; PROCEDURE OPEN_CURSOR SQLCODE; OPEN CURSOR1; PROCEDURE FETCH_CURSOR SQLCODE SQLDA; FETCH CURSOR1 USING DESCRIPTOR SQLDA; PROCEDURE CLOSE_CURSOR SQLCODE; CLOSE CURSOR1; PROCEDURE ROLLBACK SQLCODE; ROLLBACK; 2 Extended_Dynamic_CURSOR Declares an extended dynamic cursor. An extended dynamic DECLARE CURSOR statement is a DECLARE CURSOR statement in which both the cursor name and the SELECT statement are supplied in parameters at run time. See the DECLARE CURSOR for a detailed description of statement elements that apply to both dynamic and nondynamic DECLARE CURSOR statements. 3 Environment You can use the extended dynamic DECLARE CURSOR statement: o Embedded in host language programs to be precompiled o As part of a procedure in an SQL module 3 Format (B)0DECLARE qqqqqqqqqqqqqqqk  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mwqqqwqqqqqqqqqqqqqqqqwq> TABLE CURSOR qwqqqqqqqqqqqqqqqqwk   x tq> INSERT ONLY qu mq> with-clause qjx   x tq> READ ONLY qqqu x   x mq> UPDATE ONLY qj x   x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   x mqq> FOR qqq> <statement-id-parameter> qqqqqqqqqqqqwqqqqqqq>   mqwqqqqqqqqqqqqqqwwqqqqqqqqqqwwq> LIST CURSOR FOR qk x   tq> READ ONLY qjm> SCROLL qjx x x   mq> INSERT ONLY qqqqqqqqqqqqj x x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x   mq> <statement-id-parameter> qqqqqqqqqqqqqqqqqqqqqqqqj    (B)0with-clause =    qqq> WITH qq> HOLD qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mq> PRESERVE qqwq> ON COMMIT qqqu   tq> ON ROLLBACK qu   tq> ALL qqqqqqqqqu   mq> NONE qqqqqqqqj    3 Arguments 4 cursor-name-parameter Contains the name of the cursor you want to declare. Use a character string parameter to hold the cursor name that the program supplies at run time. 4 FOR_statement_id_parameter A parameter that contains an integer that identifies a prepared SELECT statement. Use an integer parameter to hold the statement identifier that SQL generates and assigns to the parameter when SQL executes a PREPARE statement. 4 INSERT_ONLY Specifies that a new list or a new row is created or opened. 4 LIST_CURSOR_FOR Specifies that you are declaring a cursor to access the elements in a list. 4 preserve-clause Syntax options: PRESERVE ON COMMIT PRESERVE ON ROLLBACK PRESERVE ALL PRESERVE NONE Specifies when a cursor remains open. o PRESERVE ON COMMIT On commit, all cursors close except those defined with the WITH HOLD PRESERVE ON COMMIT syntax. On rollback, all cursors close including those defined with the WITH HOLD PRESERVE ON COMMIT syntax. This is the same as specifying the WITH HOLD clause without any preserve options. o PRESERVE ON ROLLBACK On rollback, all cursors close except those defined with the WITH HOLD PRESERVE ON ROLLBACK syntax. On commit, all cursors close including those defined with the WITH HOLD PRESERVE ON ROLLBACK syntax. o PRESERVE ALL All cursors remain open after commit or rollback. Cursors close with the CLOSE statement or when the session ends. o PRESERVE NONE All cursors close after a close, commit, or rollback statement, when the program stops, or when you exit from interactive SQL. This is the same as not specifying the WITH HOLD clause at all. 4 READ_ONLY Specifies that the cursor is not used to update the database. 4 SCROLL Specifies that Oracle Rdb can read the items in a list from either direction (up or down) or at random. 4 TABLE_CURSOR_FOR Specifies that you are declaring a cursor to access the rows in a table. 4 UPDATE_ONLY Specifies that the cursor is used to update the database. 4 WITH_HOLD Indicates that the cursor remain open and maintain its position after the transaction ends. This is called a holdable cursor. 3 Example Example 1: Using parameters for statement and cursor names The following example shows two procedures from the online sample program SQL$MULTI_STMT_DYN.SQLADA. These procedures show the use of parameters for statement and cursor names. . . . -- This procedure prepares a statement for dynamic execution from the string -- passed to it. This procedure can prepare any number of statements -- because the statement is passed to it as the parameter, cur_procid. procedure PREPARE_SQL is CUR_CURSOR : string(1..31) := (others => ' '); CUR_PROCID : integer := 0; CUR_STMT : string(1..1024) := (others => ' '); begin -- Allocate separate SQLDAs for parameter markers (sqlda_in) and select list -- items (sqlda_out). Assign the value of the constant MAXPARMS (set in the -- declarations section) to the SQLN field of both SQLDA structures. SQLN -- specifies to SQL the maximum size of the SQLDA. sqlda_in := new sqlda_record; sqlda_in.sqln := maxparms; sqlda_out := new sqlda_record; sqlda_out.sqln := maxparms; -- Assign the SQL statement that was constructed in the procedure -- CONSTRUCT_SQL to the variable cur_stmt. cur_stmt := sql_stmt; -- Use the PREPARE...SELECT LIST statement to prepare the dynamic statement -- and write information about any select list items in it to sqlda_out. -- It prepares a statement for dynamic execution from the string passed to -- it. It also writes information about the number and data type of any -- select list items in the statement to an SQLDA (specifically, the -- sqlda_out SQLDA specified). -- -- Note that the PREPARE statement could have prepared the statement without -- writing to an SQLDA. Instead, a separate DESCRIBE...SELECT LIST statement -- would have written information about any select list items to an SQLDA. EXEC SQL PREPARE :cur_procid SELECT LIST INTO :sqlda_out FROM :cur_stmt; case sqlca.sqlcode is when sql_success => null; when others => raise syntax_error; end case; -- Use the DESCRIBE...MARKERS statement to write information about any -- parameter markers in the dynamic statement to sqlda_in. This statement -- writes information to an SQLDA (specifically, the sqlda_in SQLDA -- specified) about the number and data type of any parameter markers in -- the prepared dynamic statement. Note that SELECT statements may also -- have parameter markers. EXEC SQL DESCRIBE :cur_procid MARKERS INTO sqlda_in; case sqlca.sqlcode is when sql_success => null; when others => raise syntax_error; end case; -- If the operation is "Read," create a unique name for the cursor name -- so that the program can pass the cursor name to the dynamic DECLARE -- CURSOR statement. if cur_op(1) = 'R' then cur_cursor(1) := 'C'; cur_cursor(2..name_strlng) := cur_name(1..name_strlng - 1); -- Declare the dynamic cursor. EXEC SQL DECLARE :cur_cursor CURSOR FOR :cur_procid; case sqlca.sqlcode is when sql_success => null; when others => raise syntax_error; end case; end if; number_of_procs := number_of_procs + 1; sqlda_in_array(number_of_procs) := sqlda_in; sqlda_out_array(number_of_procs) := sqlda_out; procedure_names(number_of_procs) := cur_name; procedure_ids(number_of_procs) := cur_procid; if cur_op(1) = 'R' then cursor_names(number_of_procs) := cur_cursor; end if; exception when syntax_error => sql_get_error_text(get_error_buffer,get_error_length); put_line(get_error_buffer(1..integer(get_error_length))); put("Press RETURN to continue. "); get_line(terminal,release_screen,last); new_line; end PREPARE_SQL; . . . begin -- procedure body DISPLAY_DATA -- Before displaying any data, allocate buffers to hold the data -- returned by SQL. -- allocate_buffers; -- Allocate and assign SQLDAs for the requested SQL procedure. -- sqlda_in := new sqlda_record; sqlda_in := sqlda_in_array(stmt_index); sqlda_out := new sqlda_record; sqlda_out := sqlda_out_array(stmt_index); cur_cursor := cursor_names(stmt_index); -- Open the previously declared cursor. The statement specifies -- an SQLDA (specifically, sqlda_in) as the source of addresses for any -- parameter markers in the cursor's SELECT statement. -- EXEC SQL OPEN :cur_cursor USING DESCRIPTOR sqlda_in; case sqlca.sqlcode is when sql_success => null; when others => raise unexpected_error; end case; -- Fetch the first row from the result table. This statement fetches a -- row from the opened cursor and writes it to the addresses specified -- in an SQLDA (specifically, sqlda_out). -- EXEC SQL FETCH :cur_cursor USING DESCRIPTOR sqlda_out; case sqlca.sqlcode is -- Check to see if the result table has any rows. when sql_success => null; when stream_eof => put_line("No records found."); new_line; when others => raise unexpected_error; end case; -- Set up a loop to display the first row, then fetch and display second -- and subsequent rows. rowcount := 0; while sqlca.sqlcode = 0 loop rowcount := rowcount + 1; -- Execute the DISPLAY_ROW procedure. display_row; -- To only display 5 rows, exit the loop if the loop counter -- equals MAXROW (coded as 5 in this program). if rowcount = maxrows then exit; end if; -- Fetch another row, exit the loop if no more rows. EXEC SQL FETCH :cur_cursor USING DESCRIPTOR sqlda_out; case sqlca.sqlcode is when sql_success => null; when stream_eof => exit; when others => raise unexpected_error; end case; end loop; -- Close the cursor. EXEC SQL CLOSE :cur_cursor; case sqlca.sqlcode is when sql_success => null; when others => raise unexpected_error; end case; exception when unexpected_error => sql_get_error_text(get_error_buffer,get_error_length); EXEC SQL ROLLBACK; put_line("This condition was not expected."); put_line(get_error_buffer(1..integer(get_error_length))); put("Press RETURN to continue. "); get_line(terminal,release_screen,last); -- Stop and let the user look before returning. skip; put_line("Press RETURN to proceed. "); get_line(terminal,release_screen,last); end DISPLAY_DATA; 2 FUNCTION Declares an external function interface for use in database definition statements. The DECLARE FUNCTION statement is documented under the DECLARE Routine. For complete information on declaring a procedure, see the DECLARE ROUTINE Help topic. 2 PROCEDURE Declares a procedure interface for use in database definition statements. The DECLARE PROCEDURE statement is documented under the DECLARE Routine. For complete information on declaring a procedure, see the DECLARE ROUTINE Help topic. 2 Routine Declares a routine interface for use in database definition statements. A routine is either a function or a procedure. The declared routine acts as a template for calls to the function or procedure in DDL statements such as CREATE TABLE, CREATE VIEW and CREATE MODULE. The template allows Rdb to validate that the routine is correctly named, is passed the correct number of parameters and that those parameters are passed compatible arguments. For functions the returned data type is used to calculate data types for COMPUTED BY, AUTOMATIC and other stored value expressions. 3 Environment You can use the DECLARE Routine statement: o In interactive SQL o In dynamic SQL as a statement to be dynamically executed 3 Format (B)0DECLARE qqwq> FUNCTION qqqwq> <routine-name> qqqqqqqqqqqqqqqqqqk     mq> PROCEDURE qqj x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqq STORED NAME IS <identifier> qj   x   lqqqqqqqqqqqqqqqqqqqqqqqqqqq  ( qwqqqqqqqqqqqqqqqqqqqqqqqwq> ) qqqqqqqqqqqqqqqqqqqqqqqqk   mwq> parameter-list qqwqj   x   mqqqqqqqq , qqwqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqq>   mq> returns-clause qj mq> LANGUAGE SQL qqj    (B)0parameter-list =    qwqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqwqwq> data-type qqqqqwqqqqqqqqqqk   tq> IN qqqqu m> <parameter-name> j mq> <domain-name> qj  x   tq> OUT qqqu  x  mq> INOUT qj  x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqwqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqk   mq> DEFAULT value-expr qqqqqj mq> mechanism-clause qj x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq  mq> COMMENT IS qwq> 'string' qqqwqj   mqqqqqq / BY qqwq> DESCRIPTOR qwqqq>   tq> LENGTH qqqqqu   tq> REFERENCE qqu   mq> VALUE qqqqqqj    (B)0returns-clause =     qqq> RETURNS qw> result-data-type qwwqqqqqqqqqqqqqqqqqqqqqwqq>   m> <domain-name> qqqqjmq> mechanism-clause qj  3 Arguments 4 DEFAULT_value-expr Specifies the default value of a parameter for a function or procedure defined with mode IN. If you omit this parameter or if the CALL statement argument list or function invocation specifies the DEFAULT keyword, then the value-expr specified with this clause is used. The parameter uses NULL as the default if you do not specify a value expression explicitly. 4 FUNCTION Declares a function definition. A function optionally accepts a list of IN parameters, always returns a value, and is referenced by name as an element of a value expression. 4 LANGUAGE_SQL Names the language that calls the routine. 4 mechanism-clause Defines the passing mechanism for an external routine. The following list describes the passing mechanisms. o BY DESCRIPTOR Allows passing character data with any parameter access mode to routines compiled by language compilers that implement the OpenVMS calling standard. o BY LENGTH The LENGTH passing mechanism is the same as the DESCRIPTOR passing mechanism. o BY REFERENCE Allows passing data with any parameter access mode as a reference to the actual data. This is the default passing mechanism for parameters. This is also the default passing mechanism for a function value returning character data. o BY VALUE Allows passing data with the IN parameter access mode to a routine as a value and allows functions to return a value. This is the default passing mechanism for a function value returning noncharacter data. 4 parameter-list The optional parameters of the routine. For each parameter you can specify a parameter access mode (IN, OUT, and INOUT), a parameter name, a data type, and a passing mechanism (by DESCRIPTOR, LENGTH, REFERENCE, or VALUE). The parameter access mode (IN, OUT, and INOUT) is optional and specifies how the parameter is accessed (whether it is read, written, or both). IN signifies read only, OUT signifies write only, and INOUT signifies read and write. The parameter access mode defaults to IN. Only the IN parameter access mode may be specified with parameters to a function. Any of the parameter access modes (IN, OUT, and INOUT) may be specified with parameters to a procedure. The parameter name is prefixed with a colon (:). The parameter name must be unique within the routine parameters. The data type is required and describes the type of parameter using either an SQL data type or a domain name. You cannot declare a parameter as the LIST OF BYTE VARYING data type. 4 PROCEDURE Declares a procedure definition. A procedure optionally accepts a list of IN, OUT, or INOUT parameters, and is referenced by name in a CALL statement. 4 RETURNS Describes a function (returned) value. You can specify a data type and a passing mechanism (BY DESCRIPTOR, LENGTH, REFERENCE, or VALUE). The function value is, by definition, an OUT access mode value. The data type is required and describes the type of parameter using either an SQL data type or a domain name. You cannot declare a function value as the LIST OF BYTE VARYING data type. 4 routine-name The name of the external routine. The name must be unique among external and stored routines in the schema and can be qualified with an alias or, in a multischema database, a schema name. 4 STORED_NAME_IS The name that Oracle Rdb uses to access the routine when defined in a multischema database. The stored name allows you to access multischema definitions using interfaces that do not recognize multiple schemas in one database. You cannot specify a stored name for a routine in a database that does not allow multiple schemas. For more information about stored names, see Stored Names. 3 Examples Example 1: Definining a domain and referencing an external function SQL> create domain MONEY as integer (2); SQL> SQL> create function INTEREST_PAID cont> (in :amt MONEY) cont> returns MONEY; cont> external cont> language C cont> parameter style GENERAL; SQL> SQL> alter domain MONEY cont> add cont> check (INTEREST_PAID (value) > 0) cont> not deferrable; Once the ALTER DOMAIN is completed, neither the function nor the domain can be defined before the other. Here is a fragment of the result of executing the output from the RMU Extract command. SQL> create domain MONEY cont> INTEGER (2) cont> check((INTEREST_PAID(value) > 0)) cont> not deferrable; %SQL-F-RTNNOTDEF, function or procedure INTEREST_PAID is not defined SQL> SQL> commit work; SQL> create function INTEREST_PAID ( cont> in :AMT cont> MONEY cont> by reference) cont> returns cont> MONEY by value cont> language SQL; cont> external cont> language C cont> parameter style GENERAL cont> deterministic cont> called on null input cont> ; %SQL-F-NO_SUCH_FIELD, Domain MONEY does not exist in this database or schema SQL> commit work; This problem is avoided for RMU Extract by adding the FORWARD_ REFERENCES item to the command line: $ RMU/EXTRACT/ITEM=(ALL,FORWARD_REFERENCES) databasename/OUTPUT=script.SQL The script now contains a forward declaration of the function INTEREST_PAID so that execution of the script can succeed. SQL> declare function INTEREST_PAID ( cont> in :AMT cont> INTEGER (2)) cont> returns cont> INTEGER (2) cont> ; SQL> SQL> create domain MONEY cont> INTEGER (2) cont> check((INTEREST_PAID(value) > 0)) cont> not deferrable; SQL> SQL> commit work; SQL> create function INTEREST_PAID ( cont> in :AMT cont> MONEY cont> by reference) cont> returns cont> MONEY by value cont> language SQL; cont> external cont> language C cont> parameter style GENERAL cont> deterministic cont> called on null input cont> ; SQL> commit work; 2 LOCAL_TEMPORARY_TABLE Explicitly declares a local temporary table. The metadata for a declared local temporary table is not stored in the database and cannot be shared by other modules. These tables are sometimes called scratch tables. The data stored in the table cannot be shared between SQL sessions or modules in a single session. Unlike persistent base tables, the metadata and data do not persist beyond an SQL session. In addition to declared local temporary tables, there are two other types of temporary tables: o Global temporary tables o Local temporary tables See the CREATE TABLE statement for additional information on global and local temporary tables. 3 Environment You can use the DECLARE LOCAL TEMPORARY TABLE statement: o In interactive SQL o In dynamic SQL as a statement to be dynamically executed o In a stored module 3 Format (B)0DECLARE LOCAL TEMPORARY TABLE qwqqqqqqqqqqqqqqqqqw> MODULE . qk  mq> alias-name . qj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj mq> <table-name> qq> dec-local-table-body qqqqqqqqqqqqqqqqqqqk  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mqqqwqqqqwqq> COMPRESSION IS qwq> ENABLED qqwqqqqqqqwqqqqqwqqqq> x x mq> DISABLED qj x x x mqq> ON COMMIT qwq> DELETE qqqqwqq> ROWS qqj x    x     mq> PRESERVE qqj   x  mqqqqqqqqqqqqqqqqqqqqqqqqqqq (dec_local_col_list) qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> x   x mq> LIKE <other-table-name> qqwqqqqqqqqqqqqqqqqqqqqqqqqqqwj  mqq> (dec_local_col_list) qj   (B)0dec-local-col-list =    qqwq> <column-name> qqk   x lqqqqqqqqqqqqqqqqqj   x tqwq> data-type qqqqwwqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqwq>  x x mq> <domain-name> jmq> DEFAULT default-value qqj x x   x mqq> COMPUTED BY value-expr qqqqqqqqqqqqqqqqqqqqqqqj x   mqqqqqqqqqqqqqqqqqqqqqqq,  char-data-types qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   tq> TINYINT qqqqqqqqqqqqqqwqqqqqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqu   tq> SMALLINT qqqqqqqqqqqqqu mq> ( <n> ) qj x   tq> INTEGER qqqqqqqqqqqqqqu x   tq> BIGINT qqqqqqqqqqqqqqqu x   tq> FLOAT qqqqqqqqqqqqqqqqj x   tq> NUMBER qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqu   x mq> ( qwq> <p> qwqwqqqqqqqqqqwq> ) j x   x mq> * qqqj mq> , <d> qj x   tq> LIST OF BYTE VARYING qqwqqqqqqqqqqqqwqqwqqqqqqqqqqqqqqwqqu   x mq> ( <n> ) qj tq> AS BINARY qu x   x mq> AS TEXT qqqj x   tq> DECIMAL qwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqu   tq> NUMERIC qjmq> ( qq> <n> wqqqqqqqqqqwq> ) j x   x mq> , <n> qj x   tq> REAL qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> DOUBLE PRECISION qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   mq> date-time-data-types qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj    (B)0char-data-types =    qwq> CHAR qqqqqqqqqqqqqwwqqqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>  tq> CHARACTER qqqqqqqqumq> ( <n> ) qjmq> CHARACTER SET char-set-name qj x  tq> CHAR VARYING qqqqqu   x  tq> CHARACTER VARYING j x  tq> VARCHAR qqw> ( <n> ) qqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqu  tq> VARCHAR2 qj  mq> CHARACTER SET char-set-name qj  x  tq> LONG VARCHAR qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> NCHAR qqqqqqqqqqqqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> NATIONAL CHAR qqqqqqu mq> ( <n> ) qj    x   tq> NATIONAL CHARACTER qj    x   tq> NCHAR VARYING qqqqqqqqqqqqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqu  tq> NATIONAL CHAR VARYING qqqqqqu mq> ( <n> ) qj    x   tq> NATIONAL CHARACTER VARYING qj    x  tq> RAW q> ( <n> ) qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu mq> LONG qwqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mq> RAW qj (B)0date-time-data-types =    qqwq> DATE qwqqqqqqqqqqwqqqqqqqqqqqqqqqqqwqq>   x tq> ANSI qu x   x mq> VMS qqqj x   tq> TIME qqq> frac qqqqqqqqqqqqqqqqqqqqu   tq> TIMESTAMP qq> frac qqqqqqqqqqqqqqqqu   mq> INTERVAL qqq> interval-qualifier qqj    (B)0frac =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mqq> ( <numeric-literal> ) qj    (B)0interval-qualifier =    qqwq> YEAR qqq> prec qqwqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqwq>   x mq> TO MONTH qj x   tq> MONTH qq> prec qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> DAY qqqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO qwq> HOUR qqqqqqqqqqqqqqqu   x tq> MINUTE qqqqqqqqqqqqqu   x mq> SECOND q> frac qqqqqu   tq> HOUR qqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO qwq> MINUTE qqqqqqqqqqqqqu   x mq> SECOND q> frac qqqqqu   tq> MINUTE q> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO SECOND qqqqqq> frac qqqqqu   mq> SECOND q> seconds-prec qqqqqqqqqqqqqqqqqqqqqqqqqqj    (B)0prec =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mqq> ( <numeric-literal> ) qj    (B)0seconds-prec =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   mq> ( <numeric-literal-1> qqqk x   lqqqqqqqqqqqqqqqqqqqqqqqqqj x   mwqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qqj   m> , <numeric-literal-2> qqj    3 Arguments 4 dec-local-col-definition The definition for a column in the table. SQL gives you two ways to specify column definitions: o By directly specifying a data type to associate with a column name o By naming a domain that indirectly specifies a data type to associate with a column name See the CREATE TABLE for more information about column definitions. See the Data_Types HELP topic for more information about data types. 4 COMPRESSION_IS Syntax options: COMPRESSION IS ENABLED | COMPRESSION IS DISABLED Specifies whether run-length compression is enabled or disabled for rows inserted into the declared local temporary table. In some cases, the data inserted into a local temporary table may not compress and so incur only overhead in the row. This overhead is used by Rdb to describe the sequence of uncompressible data. Use COMPRESSION IS DISABLED to prevent Rdb from attempting the compression of such data. The default is COMPRESSION IS ENABLED. 4 ON_COMMIT Syntax options: ON COMMIT PRESERVE ROWS | ON COMMIT DELETE ROWS Specifies whether data is preserved or deleted after a COMMIT statement for declared local temporary tables. The default, if not specified, is ON COMMIT DELETE ROWS. 4 table-name The name of the table you want to declare. You can optionally precede the table-name with an alias-name and a period (.). You must, however, precede the table-name with the keyword MODULE and a period (.), for example, MODULE.EMPL_PAYROLL. 3 Examples Example 1: Declaring and using a declared local temporary table in interactive SQL SQL> DECLARE LOCAL TEMPORARY TABLE MODULE.PAYCHECK_DECL_INT cont> (EMPLOYEE_ID ID_DOM, cont> LAST_NAME CHAR(14), cont> HOURS_WORKED INTEGER, cont> HOURLY_SAL INTEGER(2), cont> WEEKLY_PAY INTEGER(2)) cont> ON COMMIT PRESERVE ROWS; SQL> -- SQL> INSERT INTO MODULE.PAYCHECK_DECL_INT cont> (EMPLOYEE_ID, LAST_NAME, HOURS_WORKED, HOURLY_SAL, WEEKLY_PAY) cont> SELECT P.EMPLOYEE_ID, E.LAST_NAME, P.HOURS_WORKED, cont> P.HOURLY_SAL, P.HOURS_WORKED * P.HOURLY_SAL cont> FROM EMPLOYEES E, PAYROLL P cont> WHERE E.EMPLOYEE_ID = P.EMPLOYEE_ID cont> AND P.WEEK_DATE = DATE '1995-08-01'; 100 rows inserted SQL> SELECT * FROM MODULE.PAYCHECK_DECL_INT LIMIT TO 2 ROWS; EMPLOYEE_ID LAST_NAME HOURS_WORKED HOURLY_SAL WEEKLY_PAY 00165 Smith 40 30.50 1220.00 00166 Dietrich 40 36.00 1440.00 2 rows selected Example 2: Creating a stored module that contains the following: o A declared local temporary table, MODULE.PAYCHECK_DECL_TAB o A procedure, PAYCHECK_INS_DECL, that inserts weekly salary records into the declared local temporary table, MODULE.PAYCHECK_DECL_TAB o A procedure, LOW_HOURS_DECL, that counts the number of employees with less than 40 hours worked The following example also demonstrates that you can access the declared local temporary table only from within the module. SQL> -- Create the module containing a declared temporary table. SQL> -- SQL> CREATE MODULE PAYCHECK_DECL_MOD cont> LANGUAGE SQL cont> DECLARE LOCAL TEMPORARY TABLE MODULE.PAYCHECK_DECL_TAB cont> (EMPLOYEE_ID ID_DOM, cont> LAST_NAME CHAR(14) , cont> HOURS_WORKED INTEGER, HOURLY_SAL INTEGER(2), cont> WEEKLY_PAY INTEGER(2)) cont> ON COMMIT PRESERVE ROWS cont> -- cont> -- Create the procedure to insert rows. cont> -- cont> PROCEDURE PAYCHECK_INS_DECL; cont> BEGIN cont> INSERT INTO MODULE.PAYCHECK_DECL_TAB cont> (EMPLOYEE_ID, LAST_NAME, HOURS_WORKED, HOURLY_SAL, WEEKLY_PAY) cont> SELECT P.EMPLOYEE_ID, E.LAST_NAME, P.HOURS_WORKED, cont> P.HOURLY_SAL, P.HOURS_WORKED * P.HOURLY_SAL cont> FROM EMPLOYEES E, PAYROLL P cont> WHERE E.EMPLOYEE_ID = P.EMPLOYEE_ID cont> AND P.WEEK_DATE = DATE '1995-08-01'; cont> END; cont> -- cont> -- Create the procedure to count the low hours. cont> -- cont> PROCEDURE LOW_HOURS_DECL (:cnt INTEGER); cont> BEGIN cont> SELECT COUNT(*) INTO :cnt FROM MODULE.PAYCHECK_DECL_TAB cont> WHERE HOURS_WORKED < 40; cont> END; cont> END MODULE; SQL> -- SQL> -- Call the procedure to insert the rows. SQL> -- SQL> CALL PAYCHECK_INS_DECL(); SQL> -- SQL> -- Declare a variable and call the procedure to count records with SQL> -- low hours. SQL> -- SQL> DECLARE :low_hr_cnt integer; SQL> CALL LOW_HOURS_DECL(:low_hr_cnt); LOW_HR_CNT 2 SQL> -- SQL> -- Because the table is a declared local temporary table, you cannot SQL> -- access it from outside the stored module that contains it. SQL> -- SQL> SELECT * FROM MODULE.PAYCHECK_DECL_TAB; %SQL-F-RELNOTDCL, Table PAYCHECK_DECL_TAB has not been declared in module or environment Example 3: Disabling Compression for a Declard Local Temporary Table The following example shows a declared local temporary table that will not benefit from compression. The clause COMPRESSION IS DISABLED is used to reduce the CPU overhead for the table as well as preventing a possible row size increase because of compression notations. SQL> declare local temporary table module.scratch0 cont> (averages double precision) cont> compression is DISABLED cont> on commit PRESERVE rows cont> ; SQL> SQL> insert into module.scratch0 cont> select avg (char_length (a)) from module.scratch1; 1 row inserted SQL> SQL> select * from module.scratch0; AVERAGES 2.100000000000000E+001 2 MODULE Specifies characteristics, such as character sets, quoting rules, and the default date format for a nonstored module. 3 Environment You can use the DECLARE MODULE statement: o Embedded in host language programs to be precompiled o In a context file This command is not executable. 3 Format (B)0DECLARE MODULE qqwqqqqqqqqqqqqqqqqqqqqqqqqwqk   mq> DIALECT environment qj x  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mqwqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk   mq> char-set-options qj mqqq> CATALOG <catalog-name> qj x  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mqwqqqqqqqqqqqqqqqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwk   mq> SCHEMA <schema-name> jmq> AUTHORIZATION <auth-id> qjx  lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq>  mq> PRAGMA (module-pragma-list) qj mq> module-language-options qj    (B)0environment =  qqwqq> SQL99 qqqqqwq>  tqq> SQL92 qqqqqu   tqq> SQL89 qqqqqu   tqq> SQLV40 qqqqu   mqq> MIA qqqqqqqj    (B)0char-set-options =    qqwqqqqqqqqqqqqqqqq> qqqqqqqqqqqqqwqqqqqk   mqq> NAMES ARE names-char-set qqj x  lqqqqqqqqqqqqqqqqq qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>   x tqq> LITERAL CHARACTER SET support-char-set qqqqu x   x tqq> NATIONAL CHARACTER SET support-char-set qqqu x   x tqq> DEFAULT CHARACTER SET support-char-set qqqqu x   x tqq> IDENTIFIER CHARACTER SET names-char-set qqqu x  x mqq> DISPLAY CHARACTER SET names-char-set qqqqqqj x  mqqqqqqqqqqqqqqqqqqqqqqqqqqq IDENT string-literal qqqqqqqqqqq> (B)0module-language-options =    qwqwqqq> ALIAS <alias-name> qqqqqqqqqqqqqqqqqqqqqqqqwqwqq>  x tqqq> CHARACTER LENGTH qwq> CHARACTERS qwqqqqqqqqu x   x x mq> OCTETS qqqqqj x x   x tqqq> DEFAULT DATE FORMAT qqwqq> SQL99 qwqqqqqqqqu x   x x tqq> SQL92 qu x x  x x mqq> VMS qqqj x x  x tqqq> KEYWORD RULES environment qqqqqqqqqqqqqqqqqu x   x tqqq> PARAMETER COLONS qqqqqqqqqqqqqqqqqqqqqqqqqqu x   x tqqq> QUOTING RULES environment qqqqqqqqqqqqqqqqqu x   x tqqq> RIGHTS qqwqq> INVOKER qqqwqqqqqqqqqqqqqqqqqu x   x x mqq> RESTRICT qqj x x   x tqqq> VIEW UPDATE RULES environment qqqqqqqqqqqqqu x   x tqqq> QUIET COMMIT qwq> ON qqqwqqqqqqqqqqqqqqqqqqu x  x x mq> OFF qqj x x x mqqq> COMPOUND TRANSACTIONS  qwq> INTERNAL qwqqqqj x x  mq> EXTERNAL qj x mqqqqqqqqqqqqqqqqqqqqqqqqqqq <statement-name> wq> STATEMENT   mqqqqqqq , <table-name> qwq> TABLE qqk   mq> <view-name> qqj x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   mq> ( qwwq> declare-col-definition qwwq> ) qq>   xmq> table-constraint qqqqqqqjx   mqqqqqqqqqq , <column-name> qq> data-type qwwqqqqqqqqqqqqqqqqqqqqqqqqwwq>   xtq> col-constraint qqqqqqux   xmq> sql-and-dtr-clause qqjx   mqqqqqqqqqqq char-data-types qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   tq> TINYINT qqqqqqqqqqqqqqwqqqqqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqu   tq> SMALLINT qqqqqqqqqqqqqu mq> ( <n> ) qj x   tq> INTEGER qqqqqqqqqqqqqqu x   tq> BIGINT qqqqqqqqqqqqqqqu x   tq> FLOAT qqqqqqqqqqqqqqqqj x   tq> NUMBER qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqu   x mq> ( qwq> <p> qwqwqqqqqqqqqqwq> ) j x   x mq> * qqqj mq> , <d> qj x   tq> LIST OF BYTE VARYING qqwqqqqqqqqqqqqwqqwqqqqqqqqqqqqqqwqqu   x mq> ( <n> ) qj tq> AS BINARY qu x   x mq> AS TEXT qqqj x   tq> DECIMAL qwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqu   tq> NUMERIC qjmq> ( qq> <n> wqqqqqqqqqqwq> ) j x   x mq> , <n> qj x   tq> REAL qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> DOUBLE PRECISION qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   mq> date-time-data-types qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj    (B)0char-data-types =    qwq> CHAR qqqqqqqqqqqqqwwqqqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>  tq> CHARACTER qqqqqqqqumq> ( <n> ) qjmq> CHARACTER SET char-set-name qj x  tq> CHAR VARYING qqqqqu   x  tq> CHARACTER VARYING j x  tq> VARCHAR qqw> ( <n> ) qqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqu  tq> VARCHAR2 qj  mq> CHARACTER SET char-set-name qj  x  tq> LONG VARCHAR qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> NCHAR qqqqqqqqqqqqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> NATIONAL CHAR qqqqqqu mq> ( <n> ) qj    x   tq> NATIONAL CHARACTER qj    x   tq> NCHAR VARYING qqqqqqqqqqqqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqu  tq> NATIONAL CHAR VARYING qqqqqqu mq> ( <n> ) qj    x   tq> NATIONAL CHARACTER VARYING qj    x  tq> RAW q> ( <n> ) qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu mq> LONG qwqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mq> RAW qj (B)0date-time-data-types =    qqwq> DATE qwqqqqqqqqqqwqqqqqqqqqqqqqqqqqwqq>   x tq> ANSI qu x   x mq> VMS qqqj x   tq> TIME qqq> frac qqqqqqqqqqqqqqqqqqqqu   tq> TIMESTAMP qq> frac qqqqqqqqqqqqqqqqu   mq> INTERVAL qqq> interval-qualifier qqj    (B)0frac =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mqq> ( <numeric-literal> ) qj    (B)0interval-qualifier =    qqwq> YEAR qqq> prec qqwqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqwq>   x mq> TO MONTH qj x   tq> MONTH qq> prec qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> DAY qqqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO qwq> HOUR qqqqqqqqqqqqqqqu   x tq> MINUTE qqqqqqqqqqqqqu   x mq> SECOND q> frac qqqqqu   tq> HOUR qqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO qwq> MINUTE qqqqqqqqqqqqqu   x mq> SECOND q> frac qqqqqu   tq> MINUTE q> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO SECOND qqqqqq> frac qqqqqu   mq> SECOND q> seconds-prec qqqqqqqqqqqqqqqqqqqqqqqqqqj    (B)0prec =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mqq> ( <numeric-literal> ) qj    (B)0seconds-prec =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   mq> ( <numeric-literal-1> qqqk x   lqqqqqqqqqqqqqqqqqqqqqqqqqj x   mwqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qqj   m> , <numeric-literal-2> qqj    (B)0col-constraint=    qqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk   m> CONSTRAINT <constraint-name> qj x   lqqqqqqqqqqqqqqq PRIMARY KEY qqqqqqqqqqqqqqqqqk   tq> UNIQUE qqqqqqqqqqqqqqqqqqqqqqu   tq> NOT NULL qqqqqqqqqqqqqqqqqqqqu   tq> NULL qqqqqqqqqqqqqqqqqqqqqqqqu  tq> CHECK (predicate) qqqqqqqqqqqu   tq> references-clause qqqqqqqqqqqu   mqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqu   lqqqqqqqqqqqqqqq   mqq> constraint-attributes qqj  (B)0constraint-attributes =    qwq> DEFERRABLE qqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>   x mq> INITIALLY wq> IMMEDIATE qqwj x   x mq> DEFERRED qqqj x   tq> NOT DEFERRABLE qqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqu   x mq> INITIALLY IMMEDIATE qqj x   tq> INITIALLY IMMEDIATE qqqqwqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqu   x tq> DEFERRABLE qqqqqu x   x mq> NOT DEFERRABLE qj x   mq> INITIALLY DEFERRED qqqqqwqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqj   mq> DEFERRABLE qqqqqj  (B)0sql-and-dtr-clause =    qwq> QUERY HEADER IS qw> <quoted-string> wqqqqqqqqqqqqqqqqqqqwq>   x mqqqqqq / EDIT STRING IS <quoted-string> qqqqqqqqqqqqqqqqqqqqqqqqqu   x x   tq> QUERY NAME FOR qwq> DTR qqqqqqqqwq> IS <quoted-string> qu   x mq> DATATRIEVE qj x   mq> DEFAULT VALUE FOR qwq> DTR qqqqqqqqwq> IS literal  qqqqj   mq> DATATRIEVE qj    (B)0table-constraint =    qqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqk   mq> CONSTRAINT <constraint-name> qqqj x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   mqq> table-constraint-clause qqqqqqqqqqqqqqk   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   mqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqq>   mq> constraint-attributes qqj    (B)0table-constraint-clause =    qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>  tq> PRIMARY KEY q> ( qw> <column-name> wq> ) qqu   x mqqqqqqq , UNIQUE q> ( qw> <column-name> wq> ) qqqqqqqu   x mqqqqqqq , CHECK (predicate) qqqqqqqqqqqqqqqqqqqqqqqqqu   mq> FOREIGN KEY q> ( qw> <column-name> wq> ) k x   mqqqqqqq , references-clause qqqqqqqqqq>qqqqqqqqqqqqj    3 Arguments 4 character-set-name A valid character set name. See the Oracle Rdb SQL Reference Manual for more information on character sets. 4 col-constraint A column constraint. See the CREATE TABLE statement for more information about column constraints. 4 column-name The name of the column you want to define. 4 data-type The data type of the column you want to define. See the Data_ Types HELP topic for more information on data types. 4 date-time-data-types Data types for dates, times, and intervals. See the Data_Types HELP topic for more information on date-time data types. 4 declare-col-definition The definition for a column in the table. The column definition must correspond to the table definition in the schema. See the CREATE TABLE statement for more information about column definitions. However, you cannot refer to domain names in a DECLARE TABLE statement. For tables whose definitions refer to domain names, you must substitute the data type and size of the domain for the domain name. 4 frac Precision specifications for date-time data types. See the Data_ Types HELP topic for more information. 4 interval-qualifier Precision specifications for date-time data types. See the Data_ Types HELP topic for more information. 4 prec Precision specifications for date-time data types. See the Data_ Types HELP topic for more information. 4 references-clause See the CREATE TABLE statement for more information. 4 seconds-prec Precision specifications for date-time data types. See the Data_ Types HELP topic for more information. 4 sql-and-dtr-clause Optional SQL and DATATRIEVE formatting clause. See the DATATRIEVE HELP topic for more information about formatting clauses. 4 table-name The name of the table definition you want to declare. 4 view-name The name of the view definition you want to declare. 4 table-constraint A constraint definition that applies to the whole table. See the CREATE TABLE statement for more information about specifying table constraints. 3 Examples Example 1: Declaring the table EMPLOYEES in a COBOL program EXEC SQL DECLARE EMPLOYEES TABLE (EMPLOYEE_ID CHAR (5) CONSTRAINT EMP_EMPLOYEE_ID_NOT_NULL NOT NULL, LAST_NAME CHAR (14), FIRST_NAME CHAR (10), MIDDLE_INITIAL CHAR (1), ADDRESS_DATA_1 CHAR (25), ADDRESS_DATA_2 CHAR (25), CITY CHAR (20), STATE CHAR (2), POSTAL_CODE CHAR (5), SEX CHAR (1), CONSTRAINT EMP_SEX_VALUES CHECK ( SEX IN ('M', 'F') OR SEX IS NULL ), BIRTHDAY DATE , STATUS_CODE CHAR (1) CONSTRAINT EMP_STATUS_CODE_VALUES CHECK ( STATUS_CODE IN ('0', '1', '2') OR STATUS_CODE IS NULL ) ) END_EXEC 2 TRANSACTION Specifies the characteristics for a default transaction. A transaction is a group of statements whose changes can be made permanent or undone only as a unit. A transaction ends with a COMMIT or ROLLBACK statement. If you end the transaction with the COMMIT statement, all changes made to the database by the statements are made permanent. If you end the transaction with the ROLLBACK statement, the statements do not take effect. The characteristics specified in a DECLARE TRANSACTION statement affect all transactions (except those started by the SET TRANSACTION or START TRANSACTION statement) until you issue another DECLARE TRANSACTION statement. The characteristics specified in a SET TRANSACTION or START TRANSACTION statement affect only that transaction. A DECLARE TRANSACTION statement does not start a transaction. The declarations made in a DECLARE TRANSACTION statement do not take effect until SQL starts a new transaction. SQL starts a new transaction with the first executable data manipulation or data definition statement following a DECLARE TRANSACTION, COMMIT, or ROLLBACK statement. In the latter case (following a COMMIT or ROLLBACK statement), SQL applies the transaction characteristics you declared for the transaction that just ended to the next one you start. In addition to the DECLARE TRANSACTION statement, you can specify the characteristics of a transaction in one of two ways: o If you specify the SET TRANSACTION or START TRANSACTION statement, the declarations in the statement take effect immediately and SQL starts a new transaction. o You can retrieve and update data without declaring or setting a transaction explicitly. If you omit the DECLARE TRANSACTION, SET TRANSACTION or START TRANSACTION statements, SQL automatically starts a transaction (using the read/write option) with the first executable data manipulation or data definition statement following a COMMIT or ROLLBACK statement. See the Oracle Rdb SQL Reference Manual for examples of when you would want to use the DECLARE TRANSACTION statement instead of the SET TRANSACTION or START TRANSACTION statement. You can specify many options with the DECLARE TRANSACTION statement, including: o A transaction mode (READ ONLY/READ WRITE/BATCH UPDATE) o A lock specification clause (RESERVING options) o A wait mode (WAIT/NOWAIT) o An isolation level o A constraint evaluation specification clause o Multiple sets of all the preceding options for each database involved in the transaction (ON clause) 3 Environment You can use the DECLARE TRANSACTION statement: o In interactive SQL o Embedded in host language programs to be precompiled o In a context file o As part of the DECLARE section in an SQL module o As part of the module header in a CREATE MODULE statement o In dynamic SQL as a statement to be dynamically executed In host language programs, you can have only a single DECLARE TRANSACTION statement in each separately compiled source file. See the Oracle Rdb SQL Reference Manual for more information. The DECLARE TRANSACTION statement is an extension to standard SQL syntax. If your program must adhere to standard SQL syntax, you can isolate a DECLARE TRANSACTION statement by putting it in a context file. For more information on context files, see the Oracle Rdb Guide to SQL Programming. 3 Format (B)0DECLARE TRANSACTION qqwqqqqqqqqqqqqqqqwq>   tq> tx-options qu   mq> db-txns qqqqj  (B)0tx-options =    qqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>  x tq> NAME 'quoted-string' qqqqqqqqqqqqqqqqu x x tq> EVALUATING qwq evaluating-clause qqwqu x x x  mqqqqqqq> ,  RESERVING qqwq> reserving-clause qqwqu x  x x   mqqqqqqqq ,  isolation-level qqqqqqqqqqqqqqqqqqqqqu x x tq> transaction-mode qqqqqqqqqqqqqqqqqqqqu x x mq> wait-option qqqqqqqqqqqqqqqqqqqqqqqqqj x mqqqqqqqqqqqqqqqqwqqqqqqqwqqqqqqqqqqqqqqqqqqqj mqq , <constraint-name> q> AT qwq> VERB TIME qqqwqq>   mq> <alias.> qj mq> COMMIT TIME qj    (B)0reserving-clause =    qwqwq> <view-name> qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqk   x mq> <table-name> qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqj x x   x mq> PARTITION qq> ( qwq> <part-num> qwq> ) qj x x   x mqqqqqq , FOR qwqqqqqqqqqqqqqqwqqwq> READ qqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqq>   tq> EXCLUSIVE qu tq> WRITE qqqqqqqqqqqu   tq> PROTECTED qu mq> DATA DEFINITION qj   mq> SHARED qqqqj    (B)0isolation-level = qqq> ISOLATION LEVEL qqwq> READ COMMITTED qqqqwqq> tq> REPEATABLE READ qqqu mq> SERIALIZABLE qqqqqqj (B)0transaction-mode = qqwq> READ ONLY qqqqwq> tq> READ WRITE qqqu mq> BATCH UPDATE qj (B)0wait-option = qqwq> WAIT qwqqqqqqqqqqqqqqqqqqqqqwqwq> x mq> <timeout-value> qqj x mq> NOWAIT qqqqqqqqqqqqqqqqqqqqqqqj (B)0db-txns =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>  mwq> ON qwq> <alias> qwq> USING qq> ( wq> tx-options qqwq> ) qwj   x mqqqq , DEFAULTS qqqqj x   mqqqqqqqqqqqqqqqqqqqqqqqqqq AND DECLARE TRANSACTION READ WRITE NOWAIT; SQL> -- SQL> -- Notice the "no transaction is in progress" message: SQL> -- SQL> SHOW TRANSACTION Transaction information: Statement constraint evaluation is off On the default alias Transaction characteristics: Nowait Read Write Transaction information returned by base system: no transaction is in progress - session ID number is 80 SQL> -- SQL> -- The first executable statement following the SQL> -- DECLARE TRANSACTION statement starts the transaction. SQL> -- In this case, SELECT is the first executable statement. SQL> -- SQL> SELECT LAST_NAME FROM CURRENT_SALARY; LAST_NAME Toliver Smith Dietrich . . . SQL> -- SQL> -- Note the transaction inherits the read/write characteristics SQL> -- specified in the DECLARE TRANSACTION statement: SQL> -- SQL> SHOW TRANSACTION; Transaction information: Statement constraint evaluation is off On the default alias Transaction characteristics: Nowait Read Write Transaction information returned by base system: a read-write transaction is in progress - updates have not been performed - transaction sequence number (TSN) is 416 - snapshot space for TSNs less than 416 can be reclaimed - session ID number is 80 SQL> -- SQL> ROLLBACK; SQL> -- SQL> -- Again, notice the "no transaction is in progress" message: SQL> -- SQL> SHOW TRANSACTION; Transaction information: Statement constraint evaluation is off On the default alias Transaction characteristics: Nowait Read Write Transaction information returned by base system: no transaction is in progress - transaction sequence number (TSN) 416 is reserved - snapshot space for TSNs less than 416 can be reclaimed - session ID number is 80 SQL> -- SQL> -- Unlike DECLARE TRANSACTION, the SET TRANSACTION statement SQL> -- immediately starts a transaction: SQL> -- SQL> SET TRANSACTION READ ONLY WAIT; SQL> -- SQL> -- Note the transaction characteristics show the SQL> -- read-only characteristics: SQL> -- SQL> SHOW TRANSACTION; Transaction information: Statement constraint evaluation is off On the default alias Transaction characteristics: Wait Read only Transaction information returned by base system: a snapshot transaction is in progress - all transaction sequence numbers (TSNs) less than 416 are visible - TSN 416 is invisible - all TSNs greater than or equal to 417 are invisible - session ID number is 80 Example 2: Using a DECLARE TRANSACTION statement in a context file The following example shows a context file, test_declares.sql, that contains declarations for precompiling source file test.sco: DECLARE ALIAS FOR FILENAME personnel; DECLARE TRANSACTION READ WRITE RESERVING EMPLOYEES FOR PROTECTED WRITE, JOB_HISTORY FOR PROTECTED WRITE, DEPARTMENTS FOR SHARED READ, JOBS FOR SHARED READ; The section in the Oracle Rdb Guide to SQL Programming about program transportability explains when you may need an SQL context file to support a program that includes SQL statements. Example 3: Explicitly setting the isolation level in a DECLARE TRANSACTION statement In this example, you declare the default characteristics for a read/write transaction, which includes changing the default ISOLATION LEVEL SERIALIZABLE to ISOLATION LEVEL REPEATABLE READ. SQL> DECLARE TRANSACTION READ WRITE ISOLATION LEVEL REPEATABLE READ; Example 4: Reserving a Partition SQL> -- Determine the ordinal position of the EMPLOYEES SQL> -- partitions. SQL> SELECT RDB$MAP_NAME, RDB$AREA_NAME, RDB$ORDINAL_POSITION cont> FROM RDB$STORAGE_MAP_AREAS cont> WHERE RDB$MAP_NAME='EMPLOYEES_MAP'; RDB$MAP_NAME RDB$AREA_NAME RDB$ORDINAL_POSITION EMPLOYEES_MAP EMPIDS_LOW 1 EMPLOYEES_MAP EMPIDS_MID 2 EMPLOYEES_MAP EMPIDS_OVER 3 3 rows selected SQL> -- SQL> -- Reserve EMPIDS_MID and EMPIDS_OVER for SQL> -- exclusive write. SQL> -- SQL> DECLARE TRANSACTION cont> RESERVING EMPLOYEES PARTITION (2,3) cont> FOR EXCLUSIVE WRITE; 2 Variable Declares variables that you can use in interactive and dynamic SQL for invoking stored procedures and for testing procedures in modules or embedded SQL programs. For information on declaring variables in compound statements, see the Compound_Statement HELP topic. 3 Environment You can use the DECLARE statement: o In interactive SQL o In dynamic SQL as a statement to be dynamically executed 3 Format (B)0DECLARE qwq> :<variable-name> wqqwqqqqqqqqqqqqqqqwqk  mqqqqqqqq , CONSTANT qqu x mqq> UPDATABLE qj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj mqwq> data-type qqqqwqwqqqqqqqqqqqqqqqqqqqqwqqqqqqq>  mq> <domain-name> j mq> default-clause qj    (B)0default-clause =    qqwq> DEFAULT qqwqqwqq> date-time-literal qwqqqqq>  mq> = qqqqqqj tqq> interval-literal qu   tqq> numeric-literal qqu    tqq> string-literal qqqu   mqq> : <variable-name> qj 3 Arguments 4 CONSTANT Syntax options: CONSTANT | UPDATABLE CONSTANT changes the variable into a declared constant that cannot be updated. If you specify CONSTANT, you must also have specified the DEFAULT clause to ensure the variable has a value. CONSTANT also indicates that the variable cannot be used as the target of an assignment or be passed as an expression to a procedure's INOUT or OUT parameter. UPDATABLE is the default and allows the variable to be modified. An update of a variable can occur due to a SET assignment, an INTO assignment (as part of an INSERT ... RETURNING, UPDATE ... RETURNING, or SELECT statement), or as a procedure's OUT or INOUT parameter on a CALL statement. 4 data-type Specifies the data type assigned to the variable. See the Data_ Types HELP topic for more information on data types. 4 default-clause You can only use references to simple literal values and other declared variables as a default. 4 domain-name Specifies the domain name assigned to the variable. The domain supplies the data type and, for interactive SQL, the edit string of the variable. See the User_Supplied_Names HELP topic for more information on domain names. 4 variable-name Specifies the local variable. 3 Example Example 1: Declaring variables in interactive SQL SQL> DECLARE :X INTEGER; SQL> DECLARE :Y CHAR(10); SQL> SQL> BEGIN cont> SET :X = 100; cont> SET :Y = 'Active'; cont> END; SQL> PRINT :X, :Y ; X Y 100 Active SQL> SHOW VARIABLES; X INTEGER Y CHAR(10) Example 2: Using the values of SQLSTATE in an interactive SQL script The following simple script uses the named SQLSTATE variable with the SIGNAL statement to make the script easier to read. @SYS$LIBRARY:SQLSTATE set verify; begin signal :SQLSTATE_DATA_ASSIGN ('Error in assignment'); end; When executed the output appears as shown below. SQL> begin cont> signal :SQLSTATE_DATA_ASSIGN ('Error in assignment'); cont> end; %RDB-E-SIGNAL_SQLSTATE, routine "(unnamed)" signaled SQLSTATE "22005" -RDB-I-TEXT, Error in assignment SQL>