1 SQL SQL is an industry standard interface for accessing relational databases. The SQL interface included with Oracle Rdb provides full access to Oracle Rdb databases. With Rdb's SQL interface, users can define, update, and query relational databases. SQL provides the following environments for issuing SQL statements: o An interactive SQL utility o A precompiler that lets users embed SQL statements in Ada, C, COBOL, FORTRAN, Pascal, or PL/I programs o SQL module language modules containing SQL statements that any language can call o A dynamic SQL interface that process SQL statements generated when the program runs 2 More_Information Develop applications using Oracle Rdb's SQL interface because it complies with the ANSI/ISO standard for SQL languages. This syntax is widely accepted as a standard for access to relational databases. 2 Release_Notes Release note information for Oracle Rdb's SQL interface is included in the file for Oracle Rdb. To see the current release notes, type or print the following file: SYS$HELP:RDBvvu.RELEASE_NOTES where vv = version u = update For example: SYS$HELP:RDB070.RELEASE_NOTES 2 Logical_Names The following table lists the logical names and configuration parameters that SQL recognizes for special purposes. Table 5 Summary of SQL Logical Names and Configuration Parameters Configuration Logical Name Parameter Function RDB$CHARACTER_SET Not applicable Specifies the database default and national character sets in addition to the session default, identifier, literal, and national character sets. The logical name is used by the EXPORT and IMPORT statements and by the SQL precompiler and SQL module language to allow compatibility of most recent versions with earlier versions of Oracle Rdb. This logical name sets the attributes for the default connection. This logical name is available only on the OpenVMS platform. This logical name is also deprecated and will not be supported in a future release. RDB$LIBRARY Not applicable Specifies a protected library that you can use to store external routine images, such as external functions. Oracle Rdb recommends that you manage public or sensitive external routine images using a protected library that is referenced by the logical name RDB$LIBRARY. You should define RDB$LIBRARY as an executive mode logical name in the system logical name table. If the external routine image is located in the protected area, you can ensure that the desired image is used by specifying the RDB$LIBRARY logical name with an explicit file name in the LOCATION clause plus the WITH SYSTEM LOGICAL_NAME TRANSLATION clause in a CREATE FUNCTION statement. RDB$RDBSHR_EVENT_ SQL_RDBSHR_EVENT_ Can be used to override the FLAGS FLAGS four event flag numbers that are assigned to RDB$SHARE at startup time by the LIB$GET_EF system service. RDB$REMOTE_ SQL_REMOTE_ Changes the default BUFFER_SIZE BUFFER_SIZE buffer size, up to your system quota limits, of network transfers. This can reduce the number of network I/O operations used during large transfers. Restrictions apply, depending on which version of Oracle Rdb you are using. RDB$REMOTE_ SQL_REMOTE_ Disables the multiplex MULTIPLEX_OFF MULTIPLEX_OFF feature. RDB$ROUTINES Not applicable Specifies the location of an external routine image. If you do not specify a location clause in a CREATE FUNCTION or CREATE PROCEDURE statement, or if you specify the DEFAULT LOCATION clause, SQL uses the RDB$ROUTINES logical name as the default image location. RDM$BIND_ABS_ RDB_BIND_ABS_ Enables quiet-point after- QUIET_POINT QUIET_POINT image journal backup operations. RDM$BIND_ABW_ RDB_BIND_ABW_ Enables asynchronous batch- ENABLED ENABLED write operations. RDM$BIND_APF_ RDB_BIND_APF_ Enables asynchronous ENABLED ENABLED prefetch operations. RDM$BIND_LOCK_ RDB_BIND_LOCK_ Specifies the amount of TIMEOUT_INTERVAL TIMEOUT_INTERVAL time a transaction waits for locks to be released. RDM$BIND_STATS_ RDB_BIND_STATS_ Allows you to enable ENABLED ENABLED the writing of database statistics for a process. Disabling statistics is useful for static, performance-critical applications that have been previously tuned and do not need the information provided by the statistics collection package. RDMS$BIND_ RDB_BIND_OUTLINE_ When multiple outlines OUTLINE_MODE MODE exist for a query, this logical name is defined to select which outline to use. RDMS$BIND_ RDB_BIND_ Defines the default setting PRESTART_TXN PRESTART_TXN for prestarted transactions outside an application. RDMS$BIND_QG_CPU_ RDB_BIND_QG_CPU_ Specifies the amount of TIMEOUT TIMEOUT CPU time used to optimize a query for execution. RDMS$BIND_QG_REC_ RDB_BIND_QG_REC_ Specifies the number of LIMIT LIMIT rows that SQL fetches before the query governor stops output. RDMS$BIND_QG_ RDB_BIND_QG_ Specifies the number of TIMEOUT TIMEOUT seconds that SQL spends compiling a query before the query governor aborts that query. RDMS$BIND_ RDB_BIND_ Allows you to reduce the SEGMENTED_STRING_ SEGMENTED_STRING_ overhead of I/O operations BUFFER BUFFER at run time when you are manipulating a segmented string. RDMS$DEBUG_FLAGS RDB_DEBUG_FLAGS Allows you to examine database access strategies and the estimated cost of those strategies when your program runs. RDMS$DIAG_FLAGS RDB_DIAG_FLAGS When defined to 'L', prevents the opening of a scrollable list cursor when the online format of lists is chained. RDMS$RTX_SHRMEM_ RDB_RTX_SHRMEM_ Specifies the size of the PAGE_CNT PAGE_CNT shared memory area used to manipulate server site- bound, external routine parameter data and control data. RDMS$USE_OLD_ RDB_USE_OLD_ Allows applications to CONCURRENCY CONCURRENCY use the isolation-level behavior that was in effect for V4.1. RDMS$USE_OLD_ RDB_USE_OLD_ When defined to YES, the SEGMENTED_STRING SEGMENTED_STRING default online format for lists (segmented strings) is chained. RDMS$VALIDATE_ RDB_VALIDATE_ Controls the validation of ROUTINE ROUTINE routines. SQL$DATABASE SQL_DATABASE Specifies the database that SQL declares if you do not explicitly declare a database. SQL$DISABLE_ SQL_DISABLE_ Disables the two-phase CONTEXT CONTEXT commit protocol. Useful for turning off distributed transactions when you want to run batch-update transactions. SQL$EDIT SQL_EDIT Specifies the editor that SQL invokes when you issue the EDIT statement in interactive SQL. SQL$KEEP_PREP_ SQL_KEEP_PREP_ On Oracle Rdb for OpenVMS FILES FILES VAX, causes the SQL precompiler and module processor to retain the intermediate macro (.MAR and .MOB) files. By default, these files are deleted when the precompiler or module processor completes processing your source file. SQL checks to see if the logical name is defined (as any value), and does not delete the intermediate files if it is. The .MAR files sometimes help to diagnose problems in precompiled programs and programs that call SQL modules. The .MOB files are object files representing the SQL code. On Oracle Rdb for OpenVMS Alpha, the SQL precompiler and module processor do not generate .MAR files, even when the SQL$KEEP_ PREP_FILES logical name is defined. SQLINI SQLINI Specifies the command file that SQL executes when you invoke interactive SQL. SYS$CURRENCY Not applicable Specifies the character that SQL substitutes for the dollar sign ($) symbol in an EDIT STRING clause of a column or domain definition. SYS$DIGIT_SEP Not applicable Specifies the character that SQL substitutes for the comma symbol (,) in an EDIT STRING clause of a column or domain definition. SYS$LANGUAGE Not applicable Specifies the language that SQL uses for date and time input and displays. SYS$RADIX_POINT Not applicable Specifies the character that SQL substitutes for the decimal point symbol (.) in an EDIT STRING clause of a column or domain definition. The following table shows the valid equivalence names for the logical name RDB$CHARACTER_SET. Table 6 Valid Equivalence Names for RDB$CHARACTER_SET Logical Name Name of Character Character Set Set Equivalence Name MCS DEC_MCS Undefined Korean and ASCII DEC_KOREAN DEC_HANGUL Hanyu and ASCII DEC_HANYU DEC_HANYU Hanzi and ASCII DEC_HANZI DEC_HANZI Kanji and ASCII DEC_KANJI DEC_KANJI For more information on these and other logical names, see the Oracle Rdb7 Guide to Database Performance and Tuning. 2 System_Relations Oracle Rdb stores information about the database as a set of tables called system relations. The system relations are the definitive source of Oracle Rdb metadata. Metadata defines the structure of the database; for example, metadata defines the fields that comprise a particular relation and the fields that can index that relation. The definitions of most system relations are standard and are likely to remain constant in future versions of Oracle Rdb. However, some system relations and fields are specific to Oracle Rdb. Within each HELP topic for a particular system relation, a list identifies the fields that are Oracle Rdb extensions to the standard definitions, if any exist. Definitions of these extensions are not guaranteed to remain stable in future versions or in other database products. Under each HELP topic for a particular system relation, BLR refers to binary language representation. This is low-level syntax used internally to represent Oracle Rdb data manipulation operations. 3 Special_Notes The following HELP topics describe the usage of system relations with respect to particular versions of Oracle Rdb or in relation to other database constructs, operations, or products. 4 Name_changes Prior to Oracle Rdb Version 6.0, several system relation names and field names had the prefix RDBVMS$. For Oracle Rdb Version 6.0 and higher, the prefix for all of these system relations and most of the field names is RDB$. In addition, some field names have been changed to more accurately reflect their usage. However, a system view is defined that will allow existing applications to continue to use the old names. Rdb recommends that new applications use the new system relation names. The system relations names affected by this change are: o RDBVMS$CATALOG_SCHEMA o RDBVMS$COLLATIONS o RDBVMS$INTERRELATIONS o RDBVMS$PRIVILEGES o RDBVMS$RELATION_CONSTRAINTS o RDBVMS$RELATION_CONSTRAINT_FLDS o RDBVMS$STORAGE_MAPS o RDBVMS$STORAGE_MAP_AREAS o RDBVMS$SYNONYMS o RDBVMS$TRIGGERS All system relations existing prior to Oracle Rdb V6.0 had some changes made to field names. 4 Using_Data_Dictionary Although you can store your data definitions in the data dictionary, the database system refers only to the system relations in the database file itself for these definitions. In a sense, the system relations are an internal data dictionary for the database. This method improves performance as Oracle Rdb does not have to access the data dictionary at run time. 4 Modifying When you create a database, Oracle Rdb defines, populates, and manipulates the system relations. As the user performs data definition operations on the database, Oracle Rdb reads and modifies the system relations to reflect those operations. You should not modify any of the Oracle Rdb system relations using data manipulation language, nor should you define any domains based on system relation fields. However, you can use regular Oracle Rdb data manipulation statements to retrieve the contents of the system relations. This means that your program can determine the structure and characteristics of the database by retrieving the fields of the system relations. See the Example subtopic under System_Relations in this HELP library for an example of querying system relations. 4 Adding_structures To avoid problems when backing up or restoring the database, do not define indexes, triggers, or any other database object on any system relation fields. You should not add fields to relations that are based on system relation fields. You can, however, add fields to views based on system relation fields; this restriction was lifted for views beginning with V4.0A. 4 Updating_Metadata When you use the SET TRANSACTION . . . RESERVING statement to lock a set of relations for an Oracle Rdb operation, you normally exclude from the transaction all the relations not listed in the RESERVING clause. However, Oracle Rdb accesses and updates system relations as necessary, no matter which relations you have locked with the SET TRANSACTION statement. When your transaction updates database metadata, Oracle Rdb reserves the system relations involved in the update in the EXCLUSIVE share mode. Other users are unable to perform data definition operations on these relations until you complete your transaction. For example: o When you refer to a domain (global field) in an update transaction that changes data definitions, Oracle Rdb locks an index for the system relation, RDB$RELATION_FIELDS. No other users can refer to the same domain until you commit your transaction. o When you change a relation (table) or domain definition, Oracle Rdb locks an index in the system relation, RDB$FIELD_ VERSIONS. No other users can change relation or global field definitions until you commit your transaction. o When you change a relation definition, Oracle Rdb locks an index in the system relation, RDB$RELATION_FIELDS. No other users can change relations in the same index node until you commit your transaction. 3 Data_types The following table lists the SQL data types and their corresponding Oracle Rdb data types. SQL Data Type Rdb Data Type char (n bytes) text (n bytes) byte varying segmented string integer signed longword scale 0 varchar (n_bytes) varying_string_(n_bytes) 4 LIST_OF_BYTE_VARYING_Metadata Oracle Rdb has supported multiple segment LIST OF BYTE VARYING data types for user-defined data. However, in previous versions Oracle Rdb maintained its own LIST OF BYTE VARYING metadata columns as single segments. This restricted the length to approximately 65530 bytes. A CREATE TRIGGER or CREATE MODULE statement could fail due to this restriction. In V7.0, this limit has been lifted by changing the way Oracle Rdb stores its own metadata. o For columns containing binary data, such as the binary representation of query, routine, constraint, trigger action, computed by column, or query outline, Oracle Rdb breaks the data into pieces that best fit the system storage area page size. Thus, the segments are all the same size with a possible small trailing segment. The LIST OF BYTE VARYING column value is no longer fragmented, improving performance when reading system metadata. o For columns containing text data such as the SQL source (for elements such as triggers and views) and user-supplied comment strings, Oracle Rdb breaks the text at line boundaries (indicated by ASCII carriage returns and line feeds) and stores the text without the line separator. Thus, the segments are of varying size with a possible zero length for blank lines. An application can now easily display the LIST OF BYTE VARYING column value and the application no longer needs to break up the single text segment for printing. No change is made to the LIST OF BYTE VARYING column values when a database is converted (RMU Convert, RMU Restore, EXPORT/IMPORT) from a previous version. Applications that read the Oracle Rdb system LIST OF BYTE VARYING column values must be changed to understand multiple segments. Applications that do not read these system column should see no change to previous behavior. Tools such as the RMU Extract command and the SQL SHOW and EXPORT statements handle both the old and new formats of the system metadata. 3 Read_only_access The following table lists the fields of certain relations which are set to read-only access. Relation Read-Only Fields RDB$FIELD_VERSIONS RDB$ACCESS_CONTROL RDB$MODULES RDB$MODULE_OWNER, RDB$ACCESS_CONTROL RDB$ROUTINES RDB$ROUTINE_OWNER, RDB$ACCESS_CONTROL RDB$RELATIONS RDB$FLAGS, RDB$ACCESS_CONTROL RDB$RELATION_FIELDS RDB$ACCESS_CONTROL RDB$DATABASE RDB$ACCESS_CONTROL Note that prior to Oracle Rdb Version 6.0, the only field set to read-only access was the RDB$FLAGS field in the RDB$RELATIONS relation. 3 Example The following BASIC program uses an SQL Module to query system relations PROGRAM SYSTEM_RELATION ! This BASIC program interactively prompts a user to enter a name ! of a system relation (table). Next, the program calls an SQL ! Module which uses a cursor to read the system relation that the ! user entered. Upon reading the fields (domains) of the system ! relation, the program displays a message to the user as to whether ! the fields in a system relation can be updated. OPTION TYPE = EXPLICIT, SIZE = INTEGER LONG ON ERROR GOTO ERR_ROUTINE ! ! Declare variables and constants ! DECLARE STRING Column_name, Table_name DECLARE INTEGER Update_yes, sqlcode DECLARE INTEGER CONSTANT TRIM_BLANKS = 128, UPPER_CASE = 32 EXTERNAL SUB SET_TRANSACTION (LONG) EXTERNAL SUB OPEN_CURSOR(LONG,STRING) EXTERNAL SUB FETCH_COLUMN(LONG,STRING,INTEGER) EXTERNAL SUB CLOSE_CURSOR(LONG) EXTERNAL SUB COMMIT_TRANS (LONG) ! ! Prompt for table name ! INPUT 'Name of Table'; Table_name Table_name = EDIT$(Table_name, UPPER_CASE) PRINT 'Starting query' PRINT 'In '; Table_name; ' Table, columns:' ! ! Call the SQL module to start the transaction. ! CALL SET_TRANSACTION(Sqlcode) ! ! Open the cursor. ! CALL OPEN_CURSOR(Sqlcode, Table_name) GET_LOOP: WHILE (Sqlcode = 0) ! ! Fetch each column ! CALL FETCH_COLUMN(Sqlcode, Column_name, Update_yes) IF (Sqlcode = 0) THEN ! ! Display returned column ! PRINT ' '; EDIT$(Column_name, TRIM_BLANKS); IF (update_yes = 1) THEN PRINT ' can be updated' ELSE PRINT ' cannot be updated' END IF END IF NEXT ERR_ROUTINE: IF Sqlcode = 100 THEN PRINT "No more rows." RESUME PROG_END ELSE PRINT "Unexpected error: ", Sqlcode, Err RESUME PROG_END END IF PROG_END: ! ! Close the cursor, commit work and exit ! CALL CLOSE_CURSOR(Sqlcode) CALL COMMIT_TRANS(Sqlcode) END PROGRAM The following module provides the SQL procedures that are called by the preceding BASIC program. -- This SQL module provides the SQL procedures that are called by the -- preceding BASIC program, System Relation ----------------------------------------------------------------- -- Header Information Section ----------------------------------------------------------------- MODULE SQL_SYSTEM_REL_BAS -- Module name LANGUAGE BASIC -- Language of calling program AUTHORIZATION SQL_SAMPLE -- Authorization ID -------------------------------------------------------------------- -- DECLARE Statements Section -------------------------------------------------------------------- DECLARE ALIAS FILENAME 'MF_PERSONNEL' -- Declaration of the database. DECLARE SELECT_UPDATE CURSOR FOR SELECT RDB$FIELD_NAME, RDB$UPDATE_FLAG FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = table_name ORDER BY RDB$FIELD_POSITION -------------------------------------------------------------------- -- Procedure Section -------------------------------------------------------------------- -- Start a transaction. PROCEDURE SET_TRANSACTION SQLCODE; SET TRANSACTION READ WRITE; -- Open the cursor. PROCEDURE OPEN_CURSOR SQLCODE table_name RDB$RELATION_NAME; OPEN SELECT_UPDATE; -- Fetch a row. PROCEDURE FETCH_COLUMN SQLCODE field_name RDB$FIELD_NAME update_flag RDB$UPDATE_FLAG; FETCH SELECT_UPDATE INTO :field_name, :update_flag; -- Close the cursor. PROCEDURE CLOSE_CURSOR SQLCODE; CLOSE SELECT_UPDATE; -- Commit the transaction. PROCEDURE COMMIT_TRANS SQLCODE; COMMIT; 3 All_System_Relations The Oracle Rdb system relations are as follows: RDB$CATALOG_SCHEMA Contains the name and definition of each SQL catalog and schema. This relation is present only in databases with the SQL multischema feature enabled. RDB$COLLATIONS The collating sequences used by this database. RDB$CONSTRAINTS Name and definition of each constraint. RDB$CONSTRAINT_RELATIONS Name of each relation that participates in a given constraint. RDB$DATABASE Database specific information. RDB$FIELD_VERSIONS One record for each version of each field definition in the database. RDB$FIELDS Characteristics of each field in the database. RDB$INDEX_SEGMENTS Fields that make up keys for relations. RDB$INDICES Characteristics of the indexes for each relation. RDB$INTERRELATIONS Interdependencies of entities used in the database. RDB$MODULES Module definition as defined by a user, including the header and declaration section. RDB$PARAMETERS Interface definition for each routine stored in RDB$ROUTINES. Each parameter to a routine (procedure or function) is described by a row in RDB$PARAMETERS. RDB$PRIVILEGES Protection for the database, relations, views, and fields. RDB$QUERY_OUTLINES Query outline definitions used by the optimizer to retrieve known query outlines prior to optimization. RDB$RELATION_CONSTRAINTS Lists all relation-specific constraints. RDB$RELATION_CONSTRAINT_ Lists the fields that participate FLDS in unique, primary, or foreign key declarations for relation-specific constraints. RDB$RELATION_FIELDS Fields defined for each relation. RDB$RELATIONS Relations in the database. RDB$ROUTINES Description of each routine (either standalone or within a module.) RDB$STORAGE_MAPS Characteristics of each storage map. RDB$STORAGE_MAP_AREAS Characteristics of each storage area referred to by a storage map. RDB$SYNONYMS Connects an object's user- specified name to its internal database name. This relation is only present in databases with the SQL multischema feature enabled. RDB$TRIGGERS Definition of a trigger. RDB$VIEW_RELATIONS Interdependencies of relations used in views. RDB$WORKLOAD Collects workload information. 3 RDB$CATALOG_SCHEMA The RDB$CATALOG_SCHEMA system relation contains the name and definition of each SQL catalog and schema. This relation is present only in databases that have the SQL multischema feature enabled. The following table provides information on the fields of the RDB$CATALOG_SCHEMA system relation. Data Field Name Type Summary Description RDB$PARENT_ID integer For a schema, this is the RDB$CATALOG_SCHEMA_ID of the catalog to which this schema belongs. For a catalog, this field is always 0. RDB$CATALOG_SCHEMA_NAME char 31 The name of the catalog or schema. RDBV$CATALOG_SCHEMA_ID integer A unique identifier indicating whether this is a catalog or a schema. Schema objects have positive identifiers starting at 1 and increasing. Catalog objects have negative identifiers starting at -1 and decreasing. 0 is reserved. RDB$DESCRIPTION byte A user-supplied description varying of the catalog or schema. RDB$SCHEMA_AUTH_ID char 31 The authorization identifier of the creator of the schema. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. RDB$SECURITY_CLASS char 20 Reserved for future use RDB$CREATED date vms Set when the schema or catalog is created RDB$LAST_ALTERED date vms Set when ALTER CATALOG or ALTER SCHEMA is used (future) RDB$CATALOG_SCHEMA_ char 31 Creator of this schema or CREATOR catalog 3 RDB$COLLATIONS The RDB$COLLATIONS system relation describes the collating sequence to be used in the database. The following table provides information on the fields of the RDB$COLLATIONS system relation. Data Field Name Type Summary Description RDB$COLLATION_NAME char 31 Supplies the name by which the database's collating sequences are known within the database. RDB$COLLATION_SEQUENCE byte Internal representation of varying the collating sequence. RDB$DESCRIPTION byte A user-supplied description varying of the collating sequence. RDB$FLAGS integer A bit mask where the following bits are set: o Bit 0 If an ASCII collating sequence. o Bit 1 If a Digital MCS collating sequence. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb_ database. RDB$CREATED date vms Set when the collating sequence is created RDB$LAST_ALTERED date vms Reserved for future use RDB$COLLATION_CREATOR char 31 Creator of this collating sequence 3 RDB$CONSTRAINTS The RDB$CONSTRAINTS system relation contains the name and definition of each constraint. The following table provides information on the fields of the RDB$CONSTRAINTS system relation. Data Field Name Type Summary Description RDB$CONSTRAINT_NAME char 31 The system-wide unique name of the constraint. RDB$CONSTRAINT_BLR byte The BLR that defines the varying constraint. RDB$CONSTRAINT_SOURCE byte The user's source for the varying constraint. RDB$DESCRIPTION byte A user-supplied description varying of the contents of this record. RDB$EVALUATION_TIME integer A value that represents when a constraint is evaluated, as follows: o 0 At default time o 1 At commit time o 2 At verb time RDB$EXTENSION_PARAMETERS byte Reserved for future use. varying RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. RDB$CREATED date vms Set when the constraint is created RDB$LAST_ALTERED date vms Reserved for future use RDB$CONSTRAINT_CREATOR char 31 Creator of this constraint 3 RDB$CONSTRAINT_RELATIONS The RDB$CONSTRAINT_RELATIONS system relation lists all relations that participate in a given constraint. The following table provides information on the fields of the RDB$CONSTRAINT_ RELATIONS system relation. Data Field Name Type Summary Description RDB$CONSTRAINT_NAME char 31 The system-wide unique name of the constraint. RDB$RELATION_NAME char 31 The name of a relation involved in the constraint. RDB$FLAGS integer A bit mask where the following bits are set: o Bit 0 Reserved for future use. o Bit 1 Reserved for future use. o Bit 2 If the constraint is on the specified relation o Bit 3 If the constraint evaluates with optimization by dbkey lookup. o Bit 4 If the constraint checks for existence. o Bit 5 If the constraint checks for uniqueness. o Bit 6 If the constraint needs to evaluate on store of specified relation record. o Bit 7 If the constraint need not evaluate on store of specified relation record. o Bit 8 If the constraint needs to evaluate on erase of specified relation record. o Bit 9 If the constraint need not evaluate on erase of specified relation record. RDB$CONSTRAINT_CONTEXT integer The context variable of the relation involved in the constraint. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. 3 RDB$DATABASE The RDB$DATABASE system relation contains information that pertains to the overall database. This relation can contain only one record. The following table provides information on the fields of the RDB$DATABASE system relation. In the RDB$DATABASE relation, all fields except the following are Oracle Rdb extensions: o RDB$DESCRIPTION o RDB$DATABASE_PARAMETERS o RDB$EXTENSION_PARAMETERS RDB$DATABASE_PARAMETERS and RDB$EXTENSION_PARAMETERS are standard fields. Data Field Name Type Summary Description RDB$CDD_PATH char 256 The dictionary path name for the database. RDB$FILE_NAME char 255 Oracle Rdb returns the file specification of the database root file. [1] RDB$MAJ_VER integer Derived from the database major version. RDB$MIN_VER integer Derived from the database minor version. RDB$MAX_RELATION_ID integer The largest relation identifier assigned. Oracle Rdb assigns the next relation an ID of MAX_ RELATION_ID + 1. RDB$RELATION_ID integer The unique identifier of the RDB$RELATIONS relation. If you delete a relation, that identifier is not assigned to any other relation. RDB$RELATION_ID_ROOT_DBK char 8 A pointer (database key or dbkey) to the base of the RDB$REL_REL_ID_NDX index on field RDB$RELATION_ID. RDB$RELATION_NAME_ROOT_ char 8 A pointer (dbkey) to the DBK base of the RDB$REL_REL_ NAME_NDX index on field RDB$RELATION_NAME. RDB$FIELD_ID integer The identifier of the RDB$FIELD_VERSIONS relation. RDB$FIELD_REL_FLD_ROOT_ char 8 A pointer (dbkey) to the DBK base of the RDB$VER_ REL_ID_VER_NDX index on fields RDB$RELATION_ID and RDB$VERSION. RDB$INDEX_ID integer The identifier of the RDB$INDICES relation. RDB$INDEX_NDX_ROOT_DBK char 8 A pointer (dbkey) to the base of the RDB$NDX_NDX_ NAME_NDX index on field RDB$INDEX_NAME. RDB$INDEX_REL_ROOT_DBK char 8 A pointer (dbkey) to the base of the RDB$NDX_REL_ NAM_NDX index on field RDB$RELATION_ID. RDB$INDEX_SEG_ID integer The identifier of the RDB$INDEX_SEGMENTS relation. RDB$INDEX_SEG_FLD_ROOT_ char 8 A pointer (dbkey) to the DBK base of the RDB$NDX_SEG_ NAM_FLD_POS_NDX index on fields RDB$INDEX_NAME and RDB$FIELD_POSITION. RDB$SEGMENTED_STRING_ID integer The logical area ID that contains the segmented strings. RDB$ACCESS_CONTROL byte The access control policy varying for the database. RDB$DESCRIPTION byte A user-supplied description varying of the contents of this record. RDB$DATABASE_PARAMETERS byte Reserved. varying RDB$EXTENSION_PARAMETERS byte Reserved. varying RDB$FLAGS integer A bit mask where the following bits can be set: o Bit 0 If dictionary required. o Bit 1 If ANSI protection used. o Bit 2 If database file is a CDD$DATABASE database. o Bit 3 Reserved. o Bit 4 Reserved. o Bit 5 Reserved. o Bit 6 Multischema enabled. RDBVMS$MAX_VIEW_ID integer The largest view identifier assigned. This value is assigned to RDB$RELATION_ID in the RDB$RELATIONS table. Oracle Rdb assigns the next view an ID of MAX_VIEW_ID + 1. RDBVMS$SECURITY_AUDIT integer A bit mask that indicates the privileges that will be audited for the field, as specified in the RMU/SET AUDIT command. RDBVMS$SECURITY_ALARM integer A bit mask that indicates the privileges that will produce alarms for the field, as specified in the RMU/SET AUDIT command. RDBVMS$SECURITY_USERS byte An access control list varying that identifies users who will be audited or who will produce alarms for DAC (discretionary access control) events when DACCESS (discretionary access) auditing is enabled for specific relations, fields, or the database. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. RDBVMS$SECURITY_AUDIT2 integer Reserved for future use. RDBVMS$SECURITY_ALARM2 integer Reserved for future use. RDBVMS$CHARACTER_SET_ID integer Value is the character set id used for the identifier character set. RDBVMS$CHARACTER_SET_ integer Value is the character set NATIONAL id used for all NCHAR (also called NATIONAL CHAR, or NATIONAL CHARACTER) data types and literals. RDBVMS$CHARACTER_SET_ integer Value is the character set DEFAULT id used for the default character set. RDBVMS$MAX_VIEW_ID integer The largest view identifier assigned to RDB$RELATION_ID in the RDB$RELATIONS table. Oracle Rdb assigns the next view an ID of MAX_VIEW_ID + 1. RDB$MAX_ROUTINE_ID integer Maintains a count of the modules and routines added to the database. Value is zero if no routines or modules have been added to the database. RDB$CREATED date vms Set when the database is created RDB$LAST_ALTERED date vms Set when ALTER DATABASE is used RDB$DATABASE_CREATOR char 31 Creator of this database RDB$DEFAULT_STORAGE_AREA_ integer Default storage area used ID for unmapped persistent tables and indices RDB$DEFAULT_TEMPLATE_ integer Reserved for future use AREA_ID Footnote: [1] The root file specification is not stored on disk (an RMU/DUMP/AREA command shows that this field is blank) and is only returned to queries at runtime. Therefore, the root file specification remains correct after you use the RMU/MOVE_AREA, RMU/COPY_DATABASE, and RMU/BACKUP commands, and the EXPORT/IMPORT statements. The following ALTER DATABASE clauses modify the RDB$LAST_ALTERED column in the RDB$DATABASE system relation: o DICTIONARY IS [NOT] REQUIRED o DICTIONARY IS [NOT]USED o MULTISCHEMA IS [ON | OFF] o WORKLOAD COLLECTION IS [ENABLED | DISABLED] o CARDINALITY COLLECTION IS [ENABLED | DISABLED] o METADATA CHANGES ARE [ENABLED | DISABLED] The following statements modify the RDB$LAST_ALTERED column in the RDB$DATABASE system relation: o GRANT statement o REVOKE statement 3 RDB$FIELD_VERSIONS The RDB$FIELD_VERSIONS system relation is an Oracle Rdb extension. This relation contains one record for each version of each field definition in the database. This relation collects the information required to build relation definitions for any given versions of a relation. The following table provides information on the fields of the RDB$FIELD_VERSIONS system relation. Data Field Name Type Summary Description RDB$RELATION_ID integer The identifier for a relation within the database. RDB$FIELD_ID integer An identifier used internally to name the field represented by this record. RDB$FIELD_NAME char 31 The name of the field. RDB$VERSION integer The version number for the relation definition to which this field belongs. RDB$FIELD_TYPE integer The data type of the field represented by this record. This data type must be interpreted according to the rules for interpreting the DSC$B_DTYPE field of class S descriptors (as defined in the OpenVMS Calling Standard). Segmented strings require a unique field type identifier. This identifier is currently 261. RDB$FIELD_LENGTH integer The length of the field represented by this record. This length must be interpreted according to the rules for interpreting the DSC$W_LENGTH field within class S and SD descriptors (as defined in the OpenVMS Calling Standard). RDB$OFFSET integer The byte offset of the field from the beginning of the record. RDB$FIELD_SCALE integer For numeric data types, the scale factor to be applied when interpreting the contents of the field represented by this record. This scale factor must be interpreted according to the rules for interpreting the DSC$B_SCALE field of class SD descriptors (as defined in the OpenVMS Calling Standard). For date-time data types, RDB$FIELD_SCALE is fractional seconds precision. For other nonnumeric data types, RDB$FIELD_SCALE is 0. RDB$FLAGS integer Reserved for future use. RDB$VALIDATION_BLR byte The BLR that represents the varying VALID_IF clause defined in this version of the field. RDB$COMPUTED_BLR byte The BLR that represents the varying COMPUTED BY clause defined in this version of the field. RDB$MISSING_VALUE byte The BLR that represents varying the MISSING_VALUE clause defined in this version of the field. RDB$SEGMENT_LENGTH integer The length of a segmented string segment. For date- time interval fields, the interval leading field precision. RDBVMS$COLLATION_NAME char 31 The name of the collating sequence for the field. RDB$ACCESS_CONTROL byte The access control list for varying the field. RDB$DEFAULT_VALUE2 byte The BLR for the SQL default varying value. RDBVMS$SECURITY_AUDIT integer A bit mask that indicates the privileges that will be audited for the database, as specified in the RMU/SET AUDIT command. RDBVMS$SECURITY_ALARM integer A bit mask that indicates the privileges that will produce alarms for the database, as specified in the RMU/SET AUDIT command. RDB$FIELD_SUB_TYPE integer A value that describes the data subtype of RDB$FIELD_ TYPE as shown in HELP topic RDB$FIELD_SUB_TYPE. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. 3 RDB$FIELD_SUB_TYPE The following table lists the values for the RDB$FIELD_SUB_TYPE and the RDB$PARAMETER_SUB_TYPE fields. RDB$FIELD_TYPE = DSC$K_DTYPE_ADT RDB$FIELD_SUB_TYPE [1] Summary Description Less than 0 Reserved for future use. Equal to 0 Traditional OpenVMS timestamp, which includes year, month, day, hour, minute, second. 7 DATE ANSI, which includes year, month, day. 56 TIME, which includes hour, minute, second. 63 TIMESTAMP, which includes year, month, day, hour, minute, second. 513 INTERVAL YEAR. 514 INTERVAL MONTH. 515 INTERVAL YEAR TO MONTH. 516 INTERVAL DAY. 520 INTERVAL HOUR. 524 INTERVAL DAY TO HOUR. 528 INTERVAL MINUTE. 536 INTERVAL HOUR TO MINUTE. 540 INTERVAL DAY TO MINUTE. 544 INTERVAL SECOND. 560 INTERVAL MINUTE TO SECOND. 568 INTERVAL HOUR TO SECOND. 572 INTERVAL DAY TO SECOND. RDB$FIELD_TYPE = DSC$K_DTYPE_T or DSC$K_DTYPE_VT RDB$FIELD_SUB_TYPE Summary Description Equal to 0 ASCII or Digital MCS character set. Greater than 0 Character set other than ASCII or Digital MCS. Less than 0 Special use of character data. RDB$FIELD_TYPE = DSC$K_DTYPE_BLOB [2] RDB$FIELD_SUB_TYPE Summary Description Less than 0 User-specified. Equal to 0 Default. Equal to 1 BLR-type. Equal to 2 Character-type. Greater than 2 Reserved for future use. Footnotes: [1] When RDB$FIELD_SUB_TYPE is not equal to 0, then RDB$SEGMENT_LENGTH can hold the interval leading field precision for intervals, and RDB$FIELD_SCALE can hold the fractional seconds precision for interval, time, or timestamp. [2] RDB$SEGMENT LENGTH is the suggested size for a single binary large object (BLOB) segment. 3 RDB$FIELDS The RDB$FIELDS system relation describes the global (generic) characteristics of each field in the database. There is one record for each global field in the database. RDB$EXTENSION_ PARAMETERS field is a standard field. The following table provides information on the fields of the RDB$FIELDS system relation. Data Field Name Type Summary Description RDB$FIELD_NAME char 31 The name of the field represented by this record. Each record within RDB$FIELDS must have a unique RDB$FIELD_ NAME field. RDB$FIELD_TYPE integer The data type of the field represented by this record. This data type must be interpreted according to the rules for interpreting the DSC$B_DTYPE field of class S descriptors (as defined in the OpenVMS Calling Standard.) Segmented strings require a unique field type identifier. This identifier is 261. RDB$FIELD_LENGTH integer The length of the field represented by this record. This length must be interpreted according to the rules for interpreting the DSC$W_LENGTH field within class S and SD descriptors as defined in OpenVMS Calling Standard. For strings, this field contains the length in octets (8-bit bytes), not in characters. RDB$FIELD_SCALE integer For numeric data types, the scale factor to be applied when interpreting the contents of the field represented by this record. This scale factor must be interpreted according to the rules for interpreting the DSC$B_SCALE field of class SD descriptors as defined in the OpenVMS Calling Standard. For date- time data types, RDB$FIELD_ SCALE is fractional seconds precision. For other nonnumeric data types, RDB$FIELD_SCALE is 0. RDB$SYSTEM_FLAG integer A bit mask where the following bits are set: o If Bit 0 is clear, then this is a user-defined field. o If Bit 0 is set, then this is a system relation field. RDB$VALIDATION_BLR byte The BLR that represents varying the validation expression to be checked each time the field's contents are modified. RDB$COMPUTED_BLR byte The BLR that represents varying the expression used to calculate a value for this field at execution time. RDB$EDIT_STRING varchar The edit string used by 255 DATATRIEVE and interactive SQL when printing the field. RDB$EDIT_STRING can be null. RDB$MISSING_VALUE byte The value used when the varying field's missing value is retrieved or displayed. RDB$MISSING_VALUE does not store any value in a field; instead, it flags the field to indicate that the value is missing. RDB$FIELD_SUB_TYPE integer A value that describes the data subtype of RDB$FIELD_ TYPE as shown in the RDB$FIELD_TYPE HELP topic. RDB$DESCRIPTION byte A user-supplied description varying of the contents of this record. RDB$VALIDATION_SOURCE byte The user's source text for varying the validation criteria. RDB$COMPUTED_SOURCE byte The user's source for the varying BLR used to calculate a value for this field at execution time. RDB$QUERY_NAME char 31 The query name of this field for use by DATATRIEVE. DATATRIEVE field attributes in RDB$RELATION_FIELDS take precedence over attributes in RDB$FIELDS. If the attribute value is missing in RDB$RELATION_ FIELDS, DATATRIEVE uses the value from RDB$FIELDS. RDB$QUERY_NAME can be null. RDB$QUERY_HEADER byte The query header of the varying field is used by DATATRIEVE and interactive SQL. DATATRIEVE field attributes in RDB$RELATION_FIELDS take precedence over attributes in RDB$FIELDS. If the attribute value is missing in RDB$RELATION_ FIELDS, DATATRIEVE uses the value from RDB$FIELDS. RDB$DEFAULT_VALUE byte The default value used varying by DATATRIEVE when no value is specified for a field during a STORE statement. It differs from RDB$MISSING_VALUE in that it holds an actual field value. DATATRIEVE field attributes in RDB$RELATION_FIELDS take precedence over attributes in RDB$FIELDS. If the attribute value is missing in RDB$RELATION_ FIELDS, DATATRIEVE uses the value from RDB$FIELDS. RDB$SEGMENT_LENGTH integer The length of a segmented string segment. For date- time interval fields, the interval leading field precision. RDB$EXTENSION_PARAMETERS byte Reserved for future use. varying RDB$CDD_NAME byte The fully qualified name of varying the dictionary entity upon which the field definition is based, as specified in the FROM PATHNAME clause. RDBVMS$COLLATION_NAME char 31 The name of the collating sequence for the field. RDB$DEFAULT_VALUE2 byte The BLR for the SQL default varying value. This value is used when no value is provided in an SQL INSERT statement. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. RDB$FLAGS tinyint A bit mask where the following bit can be set: o Bit 0 If SQL VALID_IF clause is defined on field. RDB$CREATED date vms Set when the domain is created RDB$LAST_ALTERED date vms Set when ALTER DOMAIN used RDB$FIELD_CREATOR char 31 Creator of this domain 3 RDB$INDEX_SEGMENTS The RDB$INDEX_SEGMENTS system relation describes the fields that make up an index's key. Each index must have at least one field within the key. The following table provides information on the fields of the RDB$INDEX_SEGMENTS system relation. Data Field Name Type Summary Description RDB$INDEX_NAME char 31 The name of the index of which this record is a segment. RDB$FIELD_NAME char 31 The name of a field that participates in the index key. This field name matches the name in the FIELD_NAME field of the RDB$RELATION_FIELDS relation. RDB$FIELD_POSITION integer The ordinal position of this key segment within the total index key. No two segments in the key may have the same RDB$FIELD_ POSITION. RDB$FLAGS integer A bit mask where bit 0 is set for descending segments, otherwise the segments are ascending. RDB$FIELD_LENGTH integer Shortened length of text for compressed indexes. RDBVMS$FIELD_MAPPING_LOW integer Shows the lower limit of the mapping range. RDBVMS$FIELD_MAPPING_HIGH integer Shows the higher limit of the mapping range. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is a SERdb database. RDB$CARDINALITY bigint Prefix cardinality for this and all prior key segments (assumes sorting by ordinal position) 3 RDB$INDICES The RDB$INDICES system relation contains information about indexes in the database. The RDB$EXTENSION_PARAMETERS field is a standard field. The following table provides information on the fields of the RDB$INDICES system relation. Data Field Name Type Summary Description RDB$INDEX_NAME char 31 A unique index name. RDB$RELATION_NAME char 31 The name of the relation in which the index is used. RDB$UNIQUE_FLAG integer A value that indicates whether duplicate values are allowed in indexes, as follows: o 0 If duplicate values allowed. o 1 If no duplicate values allowed. RDB$ROOT_DBK char 8 A pointer to the base of the index. RDB$INDEX_ID integer The identifier of the index. RDB$FLAGS integer A bit mask indicating the type of index, as follows: o 0 Hashed index. o 1 Index segments are numeric with mapping values compression. o 2 Hashed ordered top index. (If bit is clear, hashed scattered.) o 3 Reserved for future use. o 4 Run-length compression. o 5 Index is disabled. o 6 Reserved for future use. RDB$SEGMENT_COUNT integer The number of segments in the key. RDB$DESCRIPTION byte A user-supplied description varying of the contents of this record. RDB$EXTENSION_PARAMETERS byte Stores NODE SIZE value, varying PERCENT FILL value, compression algorithm, and compression run length for this index. Also reserved for other future use. RDB$CARDINALITY bigint The number of unique entries for a non-unique index. For a unique index, the number is 0. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is a SERdb database RDB$CREATED date vms Set when the index is created RDB$LAST_ALTERED date vms Set when ALTER INDEX is used RDB$INDEX_CREATOR char 31 Creator of this index RDB$KEY_CLUSTER_FACTOR bigint(7) Sorted Index: The ratio of the number of clump changes that occur when you traverse level- 1 index nodes and the duplicate node chains to the number of keys in the index. This statistic is based on entire index traversal. This means last duplicate node of current key is compared with first duplicate node of next key for clump change. Hash Index: The average number of clump changes that occur when you go from system record to hash bucket to overflow hash bucket (if fragmented), and traverse the duplicate node chain for each key. This statistics is based on per key traversal. RDB$DATA_CLUSTER_FACTOR bigint(7) Sorted Index: The ratio of the number of clump changes that occur between adjacent dbkeys in duplicate chains of all keys to the number of keys in the index. For unique index, the dbkeys of adjacent keys are compared for clump change. This statistic is based on entire index traversal. This means last dbkey of current key is compared with first dbkey of next key for clump change. Hashed Index: The average number of clump changes that occur between adjacent dbkeys in a duplicate chain for each key. For a unique index, this value will be always 1. This statistics is based on per key traversal. RDB$INDEX_DEPTH integer Sorted Index: The depth of the B-tree Hashed Index: this column is not used for hashed indices, and left as 0. 3 RDB$INTERRELATIONS Contains information that indicates the interdependencies of objects in the database. The RDB$INTERRELATIONS relation can be used to determine if an object can be deleted or if some other object depends upon its existence in the database. The following table provides information on the fields of the RDB$INTERRELATIONS system relation. Data Field Name Type Summary Description RDB$OBJECT_NAME char 31 The name of the relation that cannot be deleted because it is used by some other entity in the database. RDB$SUB_OBJECT_NAME char 31 The name of the field that cannot be deleted because the field is used by another entity in the database. RDB$ENTITY_NAME1 char 31 The name of the entity that depends on the existence of the field identified by the RDB$FIELD_NAME and RDB$RELATION_NAME. RDB$ENTITY_NAME2 char 31 If used, the name of the entity, together with RDB$ENTITY_NAME1, that depends on the existence of the field specified in RDB$FIELD_NAME. RDB$USAGE char 31 The relationship among RDB$RELATION_NAME, RDB$FIELD_NAME, RDB$ENTITY_ NAME1, and RDB$ENTITY_ NAME2. RDBVMS$USAGE can have the following values: constraint, computed field, storage map, view, or view field. RDB$FLAGS integer Reserved for future use. RDB$CONSTRAINT_NAME char 31 This field is the name of a constraint that is referred to from another system relation. The value in this field equates to a value for the same field in the RDB$CONSTRAINTS system relation. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is a SERdb database. 3 RDB$MODULES Describes a module as defined by a user. A module can contain a stored procedure or an external function. Each module has a header, a declaration section, and a series of routines. The header and declaration section are defined in RDB$MODULES. (Each routine is defined by an entry in RDB$ROUTINES.) A record is stored in the RDB$MODULES relation for each module that is defined by a user. The following table provides information on the fields of the RDB$MODULES system relation. Data Field Name Type Summary Description RDB$MODULE_NAME char 31 Name of the module. RDB$MODULE_OWNER char 31 Owner of the module. If the module is an invokers rights module, then this field is set to NULL. Otherwise, definers username from this column is used for definers rights checking. RDB$MODULE_ID integer Unique identifier assigned to this module by Oracle Rdb. RDB$MODULE_VERSION char 16 Module version and checksum. Allows runtime validation of the module with respect to the database. RDB$EXTENSION_PARAMETERS byte Encoded information for varying module level declarations. RDB$MODULE_HDR_SOURCE byte Source of the module header varying as provided by the definer. RDB$DECSRIPTION byte Description of the module. varying RDB$ACCESS_CONTROL byte Access Control List (ACL) varying to control access to the module. This value can be NULL. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is a SERdb database. RDB$CREATED date vms Set when the module is created RDB$LAST_ALTERED date vms Set when ALTER MODULE is used (future) RDB$MODULE_CREATOR char 31 Creator of this module. Differentiates between OWNER and AUTHORIZATION 3 RDB$PARAMETERS Defines the routine interface for each routine stored in RDB$ROUTINES. Each parameter to a routine (procedure or function) is described by a row in RDB$PARAMETERS. The following table provides information on the fields of the RDB$PARAMETERS system relation. Data Field Name Type Summary Description RDB$PARAMETER_NAME char 31 Name of the parameter. RDB$PARAMETER_SOURCE char 31 Source (domain or table) to the routine containing the parameter. RDB$ROUTINE_ID integer Unique identifier assigned to the routine containing this parameter by Oracle Rdb. RDB$ORDINAL_POSITION integer Position in parameter list. Position 0 indicates function result description. RDB$PARAMETER_TYPE integer Data type of the parameter. RDB$PARAMETER_SUB_TYPE integer A value that describes the data subtype of RDB$PARAMETER_TYPE as shown in RDB$FIELD_TYPE HELP topic. RDB$PARAMETER_LENGTH integer Length of the parameter. RDB$PARAMETER_SCALE integer Scale of the data type. RDB$PARAMETER_SEG_LENGTH integer The length of the segmented string segment. For date- time interval fields, the interval leading field precision. RDB$FLAGS integer Bit mask indicating how parameters are passed and type of parameter as follows: o 0 IN (read) o 1 OUT (write) o 2 Reserved for future use o 3 BY DESCRIPTOR (default is BY REFERENCE) o 4 BY VALUE o 5 Reserved for future use o 0 and 1 INOUT (modify) o 3 and 4 BY VALUE (bit number 3 is ignored) If bits 0 and 1 are both clear then the parameter is the RETURN TYPE of a function. RDB$DESCRIPTION byte Description of the varying parameter. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. 3 RDB$PRIVILEGES Describes the protection for the database, relations, views, modules, routines, and fields. There is one record per grantor, grantee, and privileges combination per entity in the database. A record is stored in the RDB$PRIVILEGES relation for each user who grants another user privileges for a database, relation, view, or field. If the privilege for a database, relation, view, or field was granted without the SQL GRANT option, the record of the grantor and grantee is modified. The privilege change takes effect at commit time of the command. The following table provides information on the fields of the RDB$PRIVILEGES system relation. ________________________ Note ________________________ The RDB$PRIVILEGES system relation is used only in ANSI databases. ______________________________________________________ Data Field Name Type Summary Description RDB$SUBOBJECT_ID integer The ID of the field or procedure for which protection is defined. If protection is on a database, relation, or view, this field is null. The value stored in this field must be unique within the database. RDB$OBJECT_ID integer The ID of the relation view, or module for which protection is defined. The field is null if the protection is defined for the database. The value stored in this field must be unique within the database. RDB$GRANTOR integer The binary format UIC of the person who defined or changed the privileges. This is usually the UIC of the person who executed the protection command. For an IMPORT statement, the UIC is that of the person who originally defined the protection for the user, not necessarily the person who performed the IMPORT statement. RDB$GRANTEE byte The binary format of the varying UICs of the persons who hold privileges on the database relation or field. RDB$PRIV_GRANT integer Specifies the access mask of privileges that the grantee has that he can grant to other users. RDB$PRIV_NOGRANT integer Specifies the access mask of privileges that the grantee has that he can use himself but cannot give to other users. RDB$FLAGS integer If bit zero is set, then the privilege is defined for a module and procedure; otherwise it is a relation and field privilege. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. 3 RDB$QUERY_OUTLINES Contains query outline definitions that are used by the optimizer to retrieve known query outlines prior to optimization. The following table provides information on the fields of the RDB$QUERY_OUTLINES system relation. Data Field Name Type Summary Description RDB$OUTLINE_NAME char 31 The query outline name. RDB$BLR_ID char 16 The BLR hashed identifier. This identifier is generated by the optimizer whenever a query outline is created. RDB$MODE integer The query mode (mandatory or optional). RDB$FLAGS integer A bit mask indicating the following: o 0 This outline has been invalidated by some action, such as dropping a required table or index. o 1-31 Reserved for future use. RDB$DESCRIPTION byte A user-supplied description varying of the contents of this record. RDB$OUTLINE_BLR byte The compiled query outline. varying RDB$SECURITY_CLASS char 20 This field is created only when the database being created is a SERdb database. RDB$CREATED date vms Set when the outline is created RDB$LAST_ALTERED date vms Reserved for future use RDB$OUTLINE_CREATOR char 31 Creator of this outline 3 RDB$RELATION_CONSTRAINTS The RDB$RELATION_CONSTRAINTS system relation lists all relation- specific constraints. The following table provides information on the fields of the RDB$RELATION_CONSTRAINTS system relation. Data Field Name Type Summary Description RDB$CONSTRAINT_MATCH_TYPE integer The match type associated with a referential integrity relation-specific constraint. This field is not used by the database system. The value is always zero. RDB$CONSTRAINT_NAME char 31 The name of the constraint defined by the relation specified by RDB$RELATION_ NAME. The value in this field equates to a value for the same field in the RDB$CONSTRAINTS system relation. RDB$CONSTRAINT_SOURCE byte This text string contains varying the DDL source of the constraint from the relation definition. This field is not used by the database system; it reflects the relation context expression of the constraint. RDB$CONSTRAINT_TYPE integer The type of relation- specific constraint defined. The values are shown in the RDB$CONSTRAINT_TYPE HELP topic. RDB$ERASE_ACTION integer The type of referential integrity erase action specified. This field is not used by the database system. The value is always zero. RDB$FIELD_NAME char 31 The name of the field for which a field level relation-specific constraint is defined. The field is null for a relation level constraint. This field is not used by the database system; it identifies field level constraints for the interfaces. RDB$FLAGS integer The relation constraint flag. This value is not used by the database system. RDB$MODIFY_ACTION integer The type of referential integrity modify action specified. This field is not used by Oracle Rdb. The value is always zero. RDB$REFD_CONSTRAINT_NAME char 31 The name of the unique or primary key constraint referred to by a referential integrity foreign key constraint. If the constraint is not a referential integrity constraint or no referential integrity constraint was specified, this field will be null. Otherwise, the value in this field will equate to a value for the same fields in the RDB$CONSTRAINTS and RDB$RELATION_CONSTRAINT_ FLDS system relations. This field is used to determine the foreign key referenced relation name and referenced field names. RDB$RELATION_NAME char 31 The name of the relation on which the specified constraint is defined. The value in this field equates to a value for the same field in the RDB$RELATIONS system relation. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is a SERdb database 4 RDB$CONSTRAINT_TYPE The following table lists the values for the RDB$CONSTRAINT_TYPE field. Value Symbol Meaning 1 RDB$K_CON_CONDITION Requires conditional expression constraint. 2 RDB$K_CON_PRIMARY_KEY Primary key constraint. 3 RDB$K_CON_REFERENTIAL Referential (foreign key) constraint. 4 RDB$K_CON_UNIQUE Unique constraint. 5 Reserved for future use. 6 RDB$K_CON_NOT_NULL Not null (missing) constraint. 7 RDB$K_CON_SINGLE_LEVEL Used only by SERdb. 3 RDB$RELATION_CONSTRAINT_FLDS RDB$RELATION_CONSTRAINT_FLDS lists the fields that participate in unique, primary, or foreign key declarations for relation- specific constraints. There is one record for each field that represents all or part of a unique, primary, or foreign key field. The records in this relation should not be modified. To change a record, delete it and store it again. The following table provides information on the fields of the RDB$RELATION_ CONSTRAINT_FLDS system relation. Data Field Name Type Summary Description RDB$CONSTRAINT_NAME char 31 The name of a constraint for which the specified field participates in the unique field or key declaration. RDB$FIELD_NAME char 31 The name of the field that is all or part of a unique field or key for the specified constraint. The value in this field is the same as that stored in the RDB$RELATION_FIELDS system relation. RDB$FIELD_POSITION integer The ordinal position of the specified field within the field list that declares the unique or key field. For field level constraints, there will always be only one field in the list. The first field in the list has position value 1, the second has position value 2, and so on. RDB$FLAGS integer Reserved for future use. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. 3 RDB$RELATION_FIELDS The RDB$RELATION_FIELDS system relation contains one record for each field in each relation. The RDB$EXTENSION_ PARAMETERS field is a standard field. The following table provides information on the fields of the RDB$RELATION_FIELDS system relation. Data Field Name Type Summary Description RDB$RELATION_NAME char 31 The name of the relation that contains the field represented by this record. RDB$FIELD_NAME char 31 The name of the field represented by this record within the relation. Each RDB$RELATION_FIELDS record that has the same RDB$RELATION_NAME must have a unique RDB$FIELD_NAME. RDB$FIELD_SOURCE char 31 The name of the generic field (from the RDB$FIELD_ NAME field within the RDB$FIELDS relation) that supplies the definition for this field. RDB$FIELD_ID integer An identifier that can be used within the BLR to name the field represented by this record. Oracle Rdb assigns each field an ID that is permanent for as long as the field exists within the relation. RDB$FIELD_POSITION integer The ordinal position of the field represented by this record, relative to the other fields in the same relation. RDB$QUERY_NAME char 31 The query name of this field for use by DATATRIEVE. RDB$QUERY_ NAME can be null. RDB$UPDATE_FLAG integer A value that indicates whether a field can be updated: o 0 If field cannot be updated. o 1 If field can be updated. RDB$QUERY_HEADER byte The query header of varying this field for use by DATATRIEVE. DATATRIEVE field attributes in RDB$RELATION_FIELDS take precedence over RDB$FIELDS. If the attribute value is missing in RDB$RELATION_ FIELDS, DATATRIEVE uses the value from RDB$FIELDS. RDB$DESCRIPTION byte A user-supplied description varying of the contents of this record. RDB$VIEW_CONTEXT integer For view relations, this field identifies the context variable used to qualify the view field. This context variable must be defined within the record selection expression that defines the view. The context variable appears in the BLR represented by the field RDB$VIEW_BLR in RDB$RELATIONS. RDB$BASE_FIELD char 31 The local name of the field used as a component of a view. The name is qualified by the context variable identified in RDB$VIEW_ CONTEXT. RDB$DEFAULT_VALUE byte The default value used by varying DATATRIEVE when no value is specified for a field during a STORE statement. It differs from RDB$MISSING_VALUE in that it holds an actual field value. DATATRIEVE field attributes in RDB$RELATION_ FIELDS take precedence over attributes in RDB$FIELDS. If the attribute value is missing in RDB$RELATION_ FIELDS, DATATRIEVE uses the value from RDB$FIELDS. RDB$EDIT_STRING varchar The edit string to be 255 used by DATATRIEVE and interactive SQL when printing the field. RDB$EDIT_STRING can be null. RDB$EXTENSION_PARAMETERS byte Reserved for future use. varying RDB$ACCESS_CONTROL byte The access control list for varying the field. RDB$DEFAULT_VALUE2 byte The BLR for SQL default varying value. This value is used when no value is provided in an SQL INSERT statement. RDBVMS$SECURITY_AUDIT integer A bit mask that indicates the privileges that will be audited for the database, as specified in the RMU/SET AUDIT command. RDBVMS$SECURITY_ALARM integer A bit mask that indicates the privileges that will produce alarms for the database, as specified in the RMU/SET AUDIT command. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is a SERdb database. 3 RDB$RELATIONS Names all the relations and views within the database. There is one record for each relation or view. The RDB$CARDINALITY field is an Oracle Rdb extension. The RDB$EXTENSION_PARAMETERS field is a standard field. The following table provides information on the fields of the RDB$RELATIONS system relation. Data Field Name Type Summary Description RDB$RELATION_NAME char 31 The name of a relation within the database. Each record within RDB$RELATIONS must have a unique RDB$RELATION_NAME. RDB$RELATION_ID integer An identification number used within the BLR to identify a relation. RDB$STORAGE_ID integer A pointer to the database logical area where the data for this relation is stored. RDB$SYSTEM_FLAG integer A value that indicates whether a relation is a system relation or a user-defined relation: o 0 If a user relation. o 1 If a system relation. RDB$DBKEY_LENGTH integer The length in bytes of the database key. A database key for a record in a relation is 8 bytes, and "n times 8" for a view record, where "n" is the number of relations referred to in the view. If the view does not contain a dbkey, then RDB$DBKEY_LENGTH is 0. This occurs when the view uses GROUP BY, UNION, or returns a statistical value. RDB$MAX_VERSION integer The number of the current version of the relation definition. This value is matched with the RDB$VERSION field in RDB$FIELD_ VERSIONS to determine the current record format for the relation. RDB$CARDINALITY bigint The number of records in the relation (cardinality). RDB$FLAGS integer A bit mask where the bits have the following meaning when set: o Bit 0 This relation is a view. o Bit 1 This relation is not compressed. o Bit 2 The SQL clause, WITH CHECK OPTION, is used in this view definition. o Bit 3 Indicates a special internal system relation. o Bits 4 This view is not an ANSI updatable view. o Bits 5 through 31 Reserved for future use. RDB$VIEW_BLR byte The BLR that describes the varying record selection expression used to select the records for the view. If the relation is not a view, RDB$VIEW_BLR is missing. RDB$DESCRIPTION byte A user-supplied description varying of the contents of this record. RDB$VIEW_SOURCE byte The user's source text for varying the view definition. RDB$ACCESS_CONTROL byte The access control policy varying for the relation. RDB$EXTENSION_PARAMETERS byte Reserved for future use. varying RDB$CDD_NAME byte The fully qualified name varying of the dictionary entity upon which the relation definition is based, as specified in the FROM PATHNAME clause. RDBVMS$SECURITY_AUDIT integer A bit mask that indicates the privileges that will be audited for the relation, as specified in the RMU/SET AUDIT command. RDBVMS$SECURITY_ALARM integer A bit mask that indicates the privileges that will produce alarms for relation, as specified in the RMU/SET AUDIT command. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. RDBVMS$SECURITY_AUDIT2 integer Reserved for future use. RDBVMS$SECURITY_ALARM2 integer Reserved for future use. RDB$CREATED date vms Set when the table is created (for system tables it will be the same as the database creation timestamp) RDB$LAST_ALTERED date vms Set when ALTER TABLE, CREATE/ALTER STORAGE MAP, or ALTER DOMAIN cause changes to the table RDB$RELATION_CREATOR char 31 Creator of this table RDB$ROW_CLUSTER_FACTOR bigint(7) The ratio of the number of clump changes that occur when you sequentially read the rows to the number of rows in a table. If a row is fragmented and part of its fragment is located in a clump different than the current one or immediate next one then it should be counted as a clump change. 3 RDB$ROUTINES Describes each routine that is part of a stored procedure or an external function. An external function can either be part of a module or standalone (outside the context of a module). The following table provides information on the fields of the RDB$ROUTINES system relation. Data Field Name Type Summary Description RDB$ROUTINE_NAME char 31 Name of the routine. RDB$MODULE_ID integer The identifier of the module that contains this routine. If routine is standalone, value is 0. RDB$ROUTINE_ID integer Unique identifier assigned to this routine. RDB$ROUTINE_VERSION char 16 Routine version and checksum. Allows runtime validation of the routine with respect to the database. RDB$PARAMETER_COUNT integer The number of parameters for this routine. RDB$ROUTINE_BLR byte The BLR for this routine. varying If the routine is an external function, then this column is set to NULL. RDB$ROUTINE_SOURCE byte Source of the routine as varying provided by the definer. RDB$FLAGS integer A bit mask indicating the type and state of the routine as follows: o 0 Routine is a function. (Call returns a result.) o 1 Routine is not valid. (Invalidated by a metadata change.) o 2 Routine is a variant. (A second invocation of the function with identical parameters to the first is expected to return different results.) o 3 Routine can change the transaction state. o 4 Routine is a secured sharable image. o 5 Routine is an external routine that uses the SQL parameter passing style. (Includes NULL indicators for each parameter and result.) o 6 Routine is not valid. Invalidated by a metadata change to the object upon which this routine depends. This dependency is a language semantics dependency. o Other bits are reserved for future use. RDB$SOURCE_LANGUAGE integer The source language of the routine. The RDB$SOURCE_ LANGUAGE HELP topic lists the values for the RDB$SOURCE_LANGUAGE field. RDB$DESCRIPTION byte Description of the routine. varying RDB$ACCESS_CONTROL byte The access control list varying (ACL) to control access to the routine. This value can be NULL. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. RDB$EXTENSION_PARAMETERS byte Stores interface varying information about the routine. This includes parameter mappings for procedures, and the shareable image name and entry point name for functions. RDB$TYPE_ID integer Reserved for future use. RDB$ROUTINE_OWNER char 31 Owner of the external function. This column is only used when the routine is standalone (when RDB$MODULE_ID is zero); otherwise the value is NULL. RDB$CREATED date vms Set when the routine is created (will be the same as the parent module's creation timestamp) RDB$LAST_ALTERED date vms Set when the ALTER FUNCTION or ALTER MODULE is used (future) RDB$ROUTINE_CREATOR char 31 Creator of this routine. Differentiates between AUTHORIZATION and OWNER 4 RDB$SOURCE_LANGUAGE The following table lists the values for the RDB$SOURCE_LANGAUGE field. Value Language 0 Language undefined 1 Ada 2 C 3 COBOL 4 FORTRAN 5 Pascal 6 Reserved for future use. 7 BASIC 8 GENERAL 9 PL/I 10 SQL (default for procedures) 3 RDB$STORAGE_MAPS The RDB$STORAGE_MAPS system relation contains information about each storage map. The following table provides information on the fields of the RDB$STORAGE_MAPS system relation. Data Field Name Type Summary Description RDB$MAP_NAME char 31 The name of the storage map. RDB$RELATION_NAME char 31 The name of the relation to which the storage map refers. RDB$INDEX_NAME char 31 The name of the index specified in the PLACEMENT VIA INDEX clause of the storage map. RDB$FLAGS integer A bit mask where the bits are set to indicate what the map is for, as follows: o Bit 0 If map is for a mixed format area. o Bit 1 If map enables compression. o Bits 15,31 If map is for an index. RDB$MAP_SOURCE byte The user's source text for varying the storage map definition. RDB$DESCRIPTION byte A user-supplied description varying of the contents of this record. RDB$EXTENSION_PARAMETERS byte Lists the column names varying for vertical record partitioning. RDB$VERTICAL_PARTITION_ integer A counter that indicates INDEX the number of vertical record partitions. If vertical record partitioning is used, there is one RDB$STORAGE_MAPS for each vertical partition. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is a SERdb database. 3 RDB$STORAGE_MAP_AREAS The RDB$STORAGE_MAP_AREAS system relation contains information about each storage area to which a storage map refers. The following table provides information on the fields of the RDB$STORAGE_MAP_AREAS system relation. Data Field Name Type Summary Description RDB$MAP_NAME char 31 The name of the storage map. RDB$AREA_NAME char 31 The name of the storage area referred to by the storage map. RDB$ROOT_DBK char 8 A pointer to the base of the SORTED index, if it is a SORTED index. RDB$ORDINAL_POSITION integer The order of the storage area represented by this record in the map. RDB$STORAGE_ID integer For a relation, a pointer to the database logical area. For a hashed index, a pointer to the system record. RDB$INDEX_ID integer A pointer to the index logical area. RDB$STORAGE_BLR byte The BLR that represents the varying WITH LIMIT OF clause in the storage map definition. RDB$DESCRIPTION byte Reserved for future use. varying RDB$EXTENSION_PARAMETERS byte Lists relation names and varying field names that are referenced by segmented string storage maps. RDB$VERTICAL_PARTITION_ integer For LIST storage maps: INDEX the value indicates the relationship between areas of a LIST storage map area set. RDB$FLAGS integer A bit mask where the following bits are set: If Bit 0 is clear, then the LIST storage area set is filled randomly. If Bit 0 is set, then the LIST storage area set is filled sequentially. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is a SERdb database. 3 RDB$SYNONYMS The RDB$SYNONYMS system relation connects the user-visible name of an object to the stored-name of an object. The user-visible name of an object might be replicated in multiple schemas, whereas the stored-name of an object is unique across all schemas and catalogs. This relation is present only in databases that have the SQL multischema feature enabled. Unlike records in other system tables, the records in the RDB$SYNONYMS table are compressed. The following table provides information on the fields of the RDB$SYNONYMS system relation. Data Field Name Type Summary Description RDB$SCHEMA_ID integer The RDB$CATALOG_SCHEMA_ID of the schema to which this object belongs. RDB$USER_VISIBLE_NAME char 31 The name of an object as it appears to the user. RDB$OBJECT_TYPE integer A value that represents the type of an object, as follows: o 8 A constraint o 19 A field (domain) o 26 An index o 31 A relation (table) o 36 A view o 67 A storage map o 81 A trigger o 117 A collating sequence RDB$STORED_NAME char 31 The name of an object as is actually stored in the database. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is an SERdb database. 3 RDB$TRIGGERS The RDB$TRIGGERS system relation describes the definition of a trigger. Triggers cannot be modified. To change a trigger, delete it and redefine it. The following table provides information on the fields of the RDB$TRIGGERS system relation. Data Field Name Type Summary Description RDB$DESCRIPTION byte A user-supplied text varying string describing the trigger. Not used by the database system, but by the interfaces when displaying the trigger definition. RDB$FLAGS integer Reserved for future use. RDB$RELATION_NAME char 31 The name of the relation this trigger is defined for. The trigger may be selected on an update to the named relation (qualified by the fields described in the field name list). This relation is used as a subject relation for all contexts that refer to it. RDB$TRIGGER_ACTIONS byte A text string containing varying all the sets of triggered actions defined for this trigger. The string consists of one or more sets of clumplets, one set for each triggered action. RDB$TRIGGER_CONTEXTS integer The context number used within the triggered action BLR to map the triggered action BLR to the current context of the triggering update statement. RDB$TRIGGER_FIELD_NAME_ byte A text string composed of LIST varying a count field and one or more counted strings. The count is an unsigned word that represents the number of strings in the list. The counted strings are ASCIC names that represent field names. If the trigger is of event type UPDATE, it will be evaluated if one or more of the specified fields has been modified. RDB$TRIGGER_NAME char 31 The name of a trigger. This name must be a unique trigger name within the database. RDB$TRIGGER_NEW_CONTEXT integer A context number used within the triggered action's BLR to refer to the new row values for the subject relation for an UPDATE event. RDB$TRIGGER_OLD_CONTEXT integer A context number used within the triggered action's BLR to refer to the old row values of the subject relation that existed before an UPDATE event. RDB$TRIGGER_SOURCE byte An optional text string for varying the trigger definition. The string is not used by the database system. It should reflect the full definition of the trigger. This field is used by the interfaces to display the trigger definition. RDB$TRIGGER_TYPE integer The type of trigger, as defined by the combination of the trigger action time and the trigger event. Action times are BEFORE and AFTER, and events are INSERT, DELETE, and UPDATE. The values that represent the type of trigger are shown in the TRIGGER_TYPE_ VAL HELP topic. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is a SERdb database. RDB$CREATED date vms Set when the trigger is created RDB$LAST_ALTERED date vms Set when ALTER TRIGGER is used (future) RDB$TRIGGER_CREATOR char 31 Creator of this trigger 4 TRIGGER_TYPE_VAL The following table lists the values for the RDB$TRIGGER_TYPE field and the different types of triggers they represent. Numeric Value Symbolic Value Description 1 RDB$K_BEFORE_STORE Trigger is evaluated before an INSERT. 2 RDB$K_BEFORE_ERASE Trigger is evaluated before a DELETE. 3 RDB$K_BEFORE_MODIFY Trigger is evaluated before an INSERT. 4 RDB$K_AFTER_STORE Trigger is evaluated after an INSERT. 5 RDB$K_AFTER_ERASE Trigger is evaluated after a DELETE. 6 RDB$K_AFTER_MODIFY Trigger is evaluated after an UPDATE. 3 RDB$VIEW_RELATIONS The RDB$VIEW_RELATIONS system relation lists all the relations that participate in a given view. There is one record for each relation or view in a view definition. The following table provides information on the fields of the RDB$VIEW_RELATIONS system relation. Data Field Name Type Summary Description RDB$VIEW_NAME char 31 Names a view or relation that uses another relation. The value of RDB$VIEW_NAME is normally a view name, but might also be the name of a relation that includes a field computed using a statistical expression. RDB$RELATION_NAME char 31 The name of a relation used to form the view. RDB$VIEW_CONTEXT integer An identifier for the context variable used to identify a relation in the view. The context variable appears in the BLR represented by the field RDB$VIEW_BLR in RDB$RELATIONS. RDB$SECURITY_CLASS char 20 This field is created only when the database being created is a SERdb database. 3 RDB$WORKLOAD The RDB$WORKLOAD system relation is an optional system table (similar to RDB$SYNONYMS and RDB$CATALOG_SCHEMA). It is created when the database attribute WORKLOAD COLLECTION IS ENABLED is specified on a CREATE or ALTER DATABASE. Once created, this table can never be dropped. Optional system tables also have row compression enabled to conserve space. A unique index, RDB$WRKLD_ID_FLD_NDX is also created on the two columns (RDB$RELATION_ID, RDB$FIELD_GROUP). The following table provides information on the fields of the RDB$WORKLOAD system relation. Data Field Name Type Summary Description RDB$CREATED date vms Time profile entry was created RDB$LAST_ALTERED date vms Last time statistics were updated RDB$DUPLICITY_FACTOR bigint(7) Value ranges from 1.0 to table cardinatlity. Number of duplicate values for an interesting column group (RDB$FIELD_GROUP). The pathological case is when all rows have the same value for an interesting column group which will make the duplicity factor equal to table cardinality. (This extreme case should rarely happen.) RDB$NULL_FACTOR integer(7) Value ranges from 0.0 to 1.0. This is the proportion of table rows that have NULL in one or more columns of an interesting column group RDB$RELATION_ID integer Base table id RDB$FLAGS integer Reserved for future use RDB$FIELD_GROUP char 31 Contains up to 15 sorted field-ids RDB$SECURITY_CLASS char 20 Reserved for future use 2 Sample_Databases To help you learn and test Oracle Rdb features, Oracle Rdb provides online files that you can execute to create the following sample databases: o personnel The personnel database is a single-file database. o mf_personnel The mf_personnel database is a multifile database. o corporate_data The corporate_data database is a single-file database that demonstrates the use of the multischema feature of SQL. The personnel and mf_personnel databases contain the same domains, tables, and columns. The corporate_data database contains slightly different domains, tables, and columns. You use a single command procedure by Oracle Rdb to create the various forms of the sample databases. By default, the command procedure builds the single-file personnel database using SQL data definitions. Use the following command to build this database: $ @RDM$DEMO:PERSONNEL You can specify parameters when you invoke the command procedure to create alternative versions of the sample databases. The following shows the format of the command you enter to create a sample database: $ @RDM$DEMO:PERSONNEL interface-lang database-form reposit multischema dir The parameters specify the following choices: 1. Interface-lang: Enter SQL Specifies SQL as the data definition language. SQL is the default. There are slight differences between the SQL definitions of PERSONNEL and MF_PERSONNEL. 2. Database-form: Enter S or M. Specifies the creation of either a single-file (S) or multifile (M) database. A single-file database is the default. 3. Reposit: Enter CDD or NOCDD. Specifies whether or not to store data definitions in the repository. The default is not to store data definitions in the repository. 4. Multischema: Enter MSDB or omit parameter. Specifies the creation of a multischema database, CORPORATE_ DATA. Note that for the first three parameters you must specify the SQL interface, the single-file database form, and that data definitions not be stored in the repository. The following example demonstrates how to create the multischema database: $ @RDM$DEMO:PERSONNEL SQL S NOCDD MSDB 5. Dir: Enter a directory specification where you want the database created. If you do not specify this parameter, this procedure will prompt you for a directory specification. If you do not provide a directory specification at the prompt, your default directory will be used. If you run this procedure in batch and this parameter is empty, your default directory will be used. To specify the second, third, fourth, or fifth parameters, you must include any previous parameters. You can use uppercase or lowercase to specify the parameters. For example, to build the the multifile version using SQL data definitions and storing the data definitions in the repository, enter the following command: $ @RDM$DEMO:PERSONNEL SQL M CDD When you build the single-file personnel database, the command procedure builds a database called PERSONNEL.RDB, regardless of the interface language used. When you build the multifile mf_personnel database, the command procedure builds a database called MF_PERSONNEL.RDB (plus related storage area files), regardless of the interface language used. When you build the multischema corporate_data database, the command procedure builds a database called CORPORATE_DATA.RDB. You can use only SQL to build the database. NOTE When you create the personnel or mf_personnel database, the log of the database definition statements used in creating the database is placed in a file called PERSONNEL.LOG in the same directory as the database. When you create the corporate_data database, the log of the database definition statements used in creating the database is placed in a file called CORPORATE_DATA.LOG in the same directory as the database. The personnel command procedure consists of several smaller command files, executable files, and data files. These files are located in the directory RDM$DEMO. You might want to use these files as models in creating your databases. For additional information about creating the sample databases, read the online file ABOUT_SAMPLE_DATABASES.TXT. This file is located in the Samples directory. 2 Sample_Programs During installation, SQL installs a number of sample programs in a variety of languages in a Samples directory. On OpenVMS, these programs are located in the following Samples directory: SQL$SAMPLE A brief description of the sample programs can be found in the file about_sql_examples.txt in the Samples directory. 2 Interactive_SQL Interactive SQL lets you type SQL statements interactively and see the results immediately. You can use interactive SQL for: o Defining and maintaining your database o Learning about SQL o Testing and SQL applications and developing prototypes o Performing small-scale data manipulation operations To run interactive SQL, define a symbol as follows: $ SQL :== $SQL$ $ SQL Interactive SQL provides complete online help. 2 Precompiler Type HELP SQLPRE at the DCL prompt for information on SQL Precompiler. 2 Module_language Type HELP SQLMOD at the DCL prompt for information on SQL Module Language. 2 Linking_Programs When users link programs, they must somehow specify the SQL interface user library SQL$USER.OLB. If you define the logical name LNK$LIBRARY as the user library, you save users from having to explicitly specify that library each time they link their embedded SQL programs. To define LNK$LIBRARY, issue this command: $ DEFINE/SYSTEM/EXECUTIVE/NOLOG LNK$LIBRARY SQL$USER To make sure LNK$LIBRARY is defined each time the system starts up, add the previous command to the SYS$STARTUP:RMONSTART.COM command file. You must also check to see that the system logical name LNK$LIBRARY is not already being used. Your site or other products may have already defined the LNK$LIBRARY logical name. If so, you should add a numeric suffix to the LNK$LIBRARY definition you create and to the definition in RMONSTART.COM. See the Oracle Rdb7 Installation and Configuration Guide for more information about adding a suffix. If you do not define LNK$LIBRARY to specify the SQL user library, users must explicitly name it when they link programs with embedded SQL statements. For example: $ LINK my_prog, SQL$USER/LIBRARY See the OpenVMS documentation set for more information about the LINK command. 1 SQLMOD The SQL module language and SQL module processor allow procedures that contain SQL statements to be called from any host language, including those not supported by the SQL precompiler. The SQL module language provides a calling mechanism for host language programs to execute SQL statements contained in a separate file called an SQL module file. The file contains module language elements that specify a single SQL module. The module includes one or more procedures. A procedure can contain a: o Simple statement, which consists of a single SQL statement and optional parameter declarations o Compound statement, which can include local variable declarations, multiple SQL statements, flow control statements, and transaction management statements A procedure that contains a single SQL statement is called a simple-statement procedure. A procedure that contains a compound statement, which can contain multiple SQL statements, is called a multistatement procedure. The host language program uses call statements to specify a particular SQL module procedure and supplies a sequence of actual parameters that corresponds in number and in data type to the parameter declarations in the procedure. A call to a procedure in an SQL module causes the simple or compound statement in the procedure to be executed. Oracle Rdb recommends using SQL module language, rather than precompiled SQL, because module language offers the following advantages: o Module language allows procedures that contain SQL statements to be called from any host language. In contrast, the SQL precompiler only supports a subset of host languages: Ada, C, COBOL, FORTRAN, Pascal, and PL/I. o Programs that use the SQL module language can isolate all SQL statements in SQL modules to improve modularity and avoid using two languages in the same source file. o Programs can work around restrictions of the SQL precompiler by calling SQL modules: - Programs that support pointer variables can take full advantage of dynamic SQL and use the SQLDA and SQLDA2 with the SQL module language. - SQL module language does not restrict use of host language features not supported by the precompiler (such as pointer variables in C, block structure, macros, user-defined types, and references to array elements). o Programs written in languages for which there is an ANSI standard can avoid embedding code that does not conform to the standard by isolating noncompliant SQL statements in SQL modules. For a detailed discussion of programming considerations for the SQL module language, see the Oracle Rdb7 Guide to SQL Programming. 2 Environment SQL module language elements must be part of an SQL module file. 2 Format (B)0MODULE qqqqwqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqwqk m> <module-name> qj m> DIALECT environment qqqj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk mq> char-set-options qqqj x lqqqqqqqqqqqqqqqqqqqqqqqq LANGUAGE language-name qqwqqqqqqqqqqqqqqqqqqqqqqqqqwqk m> CATALOG <catalog-name> j x lqqqqqqqqqqqqqqqqqqqqqqqq SCHEMA <schema-name> qj m> AUTHORIZATION <auth-id> j x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqqqqwqk mqq> module-language-options qjmw> declare-statement qwqj x mqqqqqqqqqq procedure-clause qq> (B)0environment =    qqwqq> SQL92 qqqqqwq>   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 mqq> IDENTIFIER CHARACTER SET names-char-set qqqj x mqqqqqqqqqqqqqqqqqqqqqqqqqq declare-alias-statement qqqqqqqwqqqq> tq> declare-cursor-statement qqqqqqu tq> declare-statement qqqqqqqqqqqqqu tq> declare-table-statement qqqqqqqu mq> declare-transaction-statement qj (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> SQL92 qwqqqqqqqqu 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 mqqq> VIEW UPDATE RULES environment qqqqqqqqqqqqqj x mqqqqqqqqqqqqqqqqqqqqqqqq PROCEDURE <procedure-name> qqqqqqk x lqqqqqqqqqqqqqqq param-decl-list qqqqqwq> ; qqk x mq> ( param-decl-list ) qj x x lqqqqqqqqqqqqqqq simple-statement qqqwq> ; qwq> x mq> compound-statement qj x mqqqqqqqqqqqqqqqqq param-decl qqqwq> mqqwqqqqq <parameter-name> qqk x lqqqqqqqqqqqqqqqqqqqj x mwq> data-type qqqqqwwqqqqqqqqqqqqqqq>qqqqqqqqqqqqqwqqwq> x tq> <domain-name> qumq> BY DESCRIPTOR wqqqqq>qqqqwj x x mq> record-type qqqj mq> CHECK qj x tq> SQLCA qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu tq> SQLCODE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu tq> SQLSTATE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu mqwqqqqqqqqqqqqqqqqqqqqqwqqwq> SQLDA qqqqqqqqqqqqqqqqqqqqu mq> <parameter-name> qj mq> SQLDA2 qqqqqqqqqqqqqqqqqqqj (B)0record-type = q> RECORD qqqk lqqqqqqqqqqqqj mww> <item-name> w> data-type qqwwqqw> END RECORD q>  xx m> record-type jx x xmqqqqqqqqqwqqqqqqqwqqq FROM <path-name> qqqk  x  xlqqqqqqqqqqqqqqqqqqqqqqj x xmwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqu x tq> FIXED qqqqqqqqqqqqqqqqqqqqqu x  x tq> NULL TERMINATED BYTES qqqqqu x x mq> NULL TERMINATED CHARACTERS j x mq> INDICATOR ARRAY OF qqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqj x mqq> <array-length> qqqqqqk x lqqqqqqqqqqqqqqqqqqqqqqqqqj x mqq> exact-numeric-type qqqqqqqqqj (B)0exact-numeric-type =    qqqqqqwqq> SMALLINT qqqwqqqqwqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqwq>  tqq> BIGINT qqqqqu mq> <n> qj  x   tqq> TINYINT qqqqj  x   tqq> INTEGER qqwqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqu  x mq> <n> qj mq> IS qqwq> 4 qwq> BYTES qj x x mq> 8 qj x  tqq> DECIMAL qqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqj   mqq> NUMERIC qqj mq> ( qq> <n> qwqqqqqqqqqqwq> ) qj       mq> , <n> qj  (B)0language-name = qwq> ADA qqqqqwqq> tq> BASIC qqqu tq> C qqqqqqqu tq> COBOL qqqu tq> FORTRAN qu tq> PASCAL qqu tq> PLI qqqqqu mq> GENERAL qj (B)0data-type = qwq> char-data-types qqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> tq> TINYINT qqqqqqqqqqqqqqwqqqqqwqqqqqqqqqqqqwqqqu tq> SMALLINT qqqqqqqqqqqqqu mq> ( <n> ) qj x tq> INTEGER qqqqqqqqqqqqqqu x tq> BIGINT qqqqqqqqqqqqqqqu x tq> LIST OF BYTE VARYING qj x tq> DECIMAL qwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqu tq> NUMERIC qjmq> ( qq> <n> wqqqqqqqqqqwq> ) j x x mq> , <n> qj x tq> FLOAT qwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqu x mq> ( <n> ) qj x tq> REAL qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu tq> DOUBLE PRECISION qqqqqqqqqqqqqqqqqqqqqqqqqqqqu mq> date-time-data-types qqqqqqqqqqqqqqqqqqqqqqqqj (B)0char-data-types = qwq> CHAR qqqqqwwqqqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwwq> x xmq> ( <n> ) qjmq> CHARACTER SET character-set-name jx tq> CHARACTER j x tq> NCHAR qqqqqqqqqwqqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqu tq> NATIONAL CHAR qj x mq> ( <n> ) qj x tq> NATIONAL CHARACTER qj x tq> VARCHAR ( <n> ) qqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqu x mq> CHARACTER SET character-set-name qqqj x tq> NCHAR VARYING qqqqqqqqqqwqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqu tq> NATIONAL CHAR VARYING qqj x mq> ( <n> ) qj x tq> NATIONAL CHARACTER VARYING qj x mq> LONG VARCHAR qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj (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  2 Arguments 3 ALIAS Specifies the default alias for the module. If you do not specify a module alias, the default alias is the authorization identifier for the module. When the FIPS flagger is enabled, the ALIAS clause (by itself or used with the AUTHORIZATION clause) is flagged as nonstandard syntax. If the application needs to refer to only one database across multiple modules, it is good practice to use the same alias for the default database in all modules that will be linked to make up an executable image. If that image will include modules processed with the SQL precompiler, you should specify RDB$DBHANDLE in the AUTHORIZATION clause of all SQL modules in the image because the alias RDB$DBHANDLE always designates the default database in precompiled SQL programs. 3 AUTHORIZATION Specifies the authorization identifier for the module. If you do not specify a schema authorization, the authorization identifier is the user name of the user compiling the module. If you want to comply with the ANSI/ISO SQL89 standard, specify the AUTHORIZATION clause without the schema-name. Specify both the AUTHORIZATION clause and the schema name to comply with the entry-level ANSI/ISO SQL92 Standard. When you attach to a multischema database, the authorization identifier for each schema is the user name of the user compiling the module. This authorization identifier defines the default alias and schema. You can use the ALIAS and SCHEMA clauses to override the defaults. If you attach to a single-schema database or specify that MULTISCHEMA IS OFF in your ATTACH or DECLARE ALIAS statements and you specify both an AUTHORIZATION clause and an ALIAS clause, the authorization identifier is ignored by SQL unless you specify the RIGHTS clause in the module file. The RIGHTS clause causes SQL to use the authorization identifier specified in the module AUTHORIZATION clause for privilege checking. If procedures in the SQL module always qualify table names with an authorization identifier, the AUTHORIZATION clause has no effect on SQL statements in the procedures. When the FIPS flagger is enabled, the omission of an AUTHORIZATION clause is flagged as nonstandard ANSI syntax. 3 BY_DESCRIPTOR Specifies that the formal parameter will be passed to the calling program module by descriptor. The BY DESCRIPTOR clause is useful when: o You specify the GENERAL keyword in the LANGUAGE clause of an SQL module, but the default for the language is to pass parameters by descriptor. The default for GENERAL is to pass parameters by reference, but you can override that default passing mechanism by specifying BY DESCRIPTOR. o You want to take advantage of the CHECK option for parameter declarations. That option is available only for parameters declared with the BY DESCRIPTOR clause. o You need to override the default parameter passing mechanism for languages that pass parameters by reference. The BY DESCRIPTOR clause supports only OpenVMS static descriptors, which are fixed-length fields. For any language, the passing mechanism for SQL module formal parameters must be the same as the actual parameters in the host language module. Ada, BASIC, C, FORTRAN, Pascal, and PL/I do not support passing records by descriptor. You may construct a descriptor from elements in all these languages and pass the constructed descriptor to the SQL module language by reference. o When you construct a descriptor for a host language record when the module language is Ada, BASIC, C, FORTRAN, Pascal, PL/I, or GENERAL, use a fixed-length descriptor (CLASS_S) with a character string data type, and pass the length of the entire record. o If the language is Ada, BASIC, FORTRAN, or Pascal, pass indicator arrays using an array descriptor (CLASS_A) and the data type of all of the array elements. o If the language is COBOL, pass arrays using fixed-length (CLASS_S) descriptors and character string data types, regardless of the data types of the array elements. o If the language is C, the SQL module processor interprets CHAR fields one way when the data type is defined in the module, and another way when the definition is read from the dictionary. When the data type is defined in the module, the SQL module processor interprets character strings within records as null-terminated strings. In other words, if you declare a field specified as CHAR(9), the C module language interprets this as a field that is actually 10 characters long, with the tenth character being the null terminator. However, if you include a record in a C module from the data dictionary, you can specify any of three options for CHAR field interpretation. 3 CATALOG Specifies the default catalog for the module. Catalogs are groups of schemas within a multischema database. If you omit the catalog name when specifying an object in a multischema database, SQL uses the default catalog name RDB$CATALOG. Databases created without the multischema attribute do not have catalogs. You can use the SET CATALOG statement to change the current default catalog name in dynamic or interactive SQL. 3 char-data-types Refer to the Oracle Rdb7 SQL Reference Manual for information about the character data types that SQL supports. 3 CHARACTER_LENGTH Specifies whether the length of character string parameters, columns, and domains are interpreted as characters or octets. If the dialect is set to SQL89, SQL92, or MIA, the default is CHARACTERS. Otherwise, the default is OCTETS. 3 CHECK Specifies that SQL compares at run time the data type, length, and scale of the descriptor for an actual parameter to what was declared for the procedure parameter in the SQL module. If the two do not match, SQL returns an error. The CHECK clause works only with parameters passed by descriptor from the calling host language module. Because there is no connection between an SQL module and a calling host language program module when they are compiled, there is no way for SQL to check for agreement between formal parameter declarations and actual parameters in calls to the module. The CHECK clause provides a way to do such checking when the program runs. If a formal parameter declaration does not specify the CHECK clause, SQL assumes that procedure and calling parameters agree. If they do not, programs can give unpredictable results. However, you may choose not to use the CHECK clause because: o The CHECK clause is not part of ANSI-standard SQL. o There is a minor performance penalty for SQL to check parameters at run time. o Using CHECK can make host programs more complicated. The CHECK clause follows these rules in comparing formal parameters with call parameters: o If a formal parameter is TIMESTAMP data type, the CHECK clause accepts any corresponding actual parameter that is 8 bytes long. o If the language is C and the formal parameter is CHAR data type, the CHECK clause expects the descriptor to be 1 byte longer than the number of characters in the formal parameter. This occurs because character strings in C include a terminator character (they are in ASCIZ format) that is not included in the length of the formal parameter declaration. When you retrieve data definitions from the dictionary, however, you can change the default interpretation of character data by specifying FIXED or NULL TERMINATED CHARACTERS in the record-type clause of the FROM path-name clause. o The CHECK clause supports dynamic string descriptors (CLASS_D) in BASIC for procedure parameters declared with the CHARACTER data type. However, the CHECK clause does not compare the length of the descriptor with the length of the procedure parameter because the buffer to receive the data is allocated at run time. o If the formal parameter is VARCHAR data type, the descriptor that the CHECK clause accepts depends on the language. - If the language is PL/I or Pascal (languages that support varying character data type), the descriptor must be a varying string (CLASS_VS) descriptor, the data type must be varying text, and the length must be the same as the length of the formal parameter declaration. - If the language is not PL/I or Pascal, the CHECK clause accepts a varying string descriptor as in the preceding paragraph, or a fixed-length (CLASS_S) or unspecified (DTYPE_Z) descriptor with data type of text and a length 2 bytes longer than the length of the formal parameter declaration. For more detail on the different types of OpenVMS argument descriptors, see the OpenVMS programming documentation. 3 compound-statement Most commonly, includes multiple executable SQL statements, associated variable declarations, and control statements within a BEGIN . . . END block; however, each of these arguments is optional. For instance, you can create an empty BEGIN . . . END block (BEGIN END;). SQL executes the compound statement when the procedure in which it is embedded is called by a host language module. See the Oracle Rdb7 SQL Reference Manual for more complete information about a compound statement. 3 data-type You can specify the character set of parameters that are defined as character data types. SQL assumes the character set of parameters based on the following rules: o If a parameter is not qualified by a character set or defined as a national character data type, SQL considers the parameter to be of the default character set as specified in the DEFAULT CHARACTER SET clause. o If a parameter is defined as a national character data type (NCHAR, NCHAR VARYING), SQL considers the parameter to be of the national character set as specified in the NATIONAL CHARACTER SET clause. o If a parameter is defined as a data type qualified by a character set, SQL considers the parameter to be of that character set. See the Oracle Rdb7 SQL Reference Manual for information about data types and qualifying a data type with a character set. The Argument INTEGER topic describes the INTEGER data type with regard to the SQL module language. The SQL data type specified for the formal parameter in a module must be equivalent to the data type of the host language variable declaration for the actual parameter. If the formal parameter and actual parameter are not declared with equivalent data types, SQL can give unpredictable results. The data type for a database key is CHAR(n), where n equals the number of bytes of the database key. See the Oracle Rdb7 SQL Reference Manual for more information on database keys. 3 declare-statement Any of the following statements: o DECLARE ALIAS o DECLARE CURSOR o DECLARE STATEMENT o DECLARE TABLE o DECLARE TRANSACTION You must place all DECLARE statements in an SQL module together after the LANGUAGE clause of the module. All such DECLARE statements are optional. All the DECLARE statements except DECLARE TRANSACTION can be repeated. For each DECLARE CURSOR statement, however, there must be only one procedure in the SQL module that contains an OPEN statement that corresponds to the DECLARE CURSOR statement. Do not use any punctuation to separate DECLARE statements or to separate the declare-statement section from the procedure section. 3 DEFAULT_CHARACTER_SET Specifies the character set for parameters that are not qualified by a character set and are not defined as a national character data type. If you do not specify a character set in this clause or in the NAMES ARE clause, the default is DEC_MCS. This clause overrides the character set specified in the NAMES ARE clause. See the Oracle Rdb7 SQL Reference Manual for a list of the allowable character sets. 3 DEFAULT_DATE_FORMAT Controls the default interpretation for columns with the DATE or CURRENT_TIMESTAMP data type. The DATE and CURRENT_TIMESTAMP data types can be either VMS or SQL92 format. If you specify VMS, both data types are interpreted as VMS format. The VMS format DATE and CURRENT_TIMESTAMP contain YEAR to SECOND fields, like a TIMESTAMP. If you specify SQL92, both data types are interpreted as SQL92 format. The SQL92 format DATE contains only the YEAR to DAY fields. The default is VMS. Use the DEFAULT DATE FORMAT clause, rather than the ANSI_DATE qualifier, because the qualifier will be deprecated in a future release. 3 DIALECT Controls the following settings for the current connection: o Whether the length of character string parameters, columns, and domains are interpreted as characters or octets o Whether double quotation marks are interpreted as string literals or delimited identifiers o Whether or not identifiers may be keywords o Which views are read-only o Whether columns with the DATE or CURRENT_TIMESTAMP data type are interpreted as VMS or SQL92 format o Whether or not parameter names begin with a colon o Whether or not the session character sets change depending on the dialect specified The DIALECT clause lets you specify the settings with one clause, instead of specifying each setting individually. Because the module processor processes the module clauses sequentially, the DIALECT clause can override the settings of clauses (for example, QUOTING RULES) specified before it or be overridden by clauses specified after it. The following statements are specific to the SQL92 dialect: o The default constraint evaluation time setting changes from DEFERRABLE to NOT DEFERRABLE. o Conversions between character data types when storing data or retrieving data raise exceptions or warnings in certain situations. o You can specify DECIMAL or NUMERIC for formal parameters in SQL modules, and declare host language parameters with packed decimal or signed numeric storage format. SQL generates an error message if you attempt to exceed the precision specified. o The USER keyword specifies the current active user name for a request. o A warning is generated when a null value is eliminated from a SET function. o The WITH CHECK OPTION clause on views returns a discrete error code from an integrity constraint failure. o An exception is generated with non-null terminated C strings. See the Oracle Rdb7 SQL Reference Manual for more information on the settings for each option of the DIALECT clause. 3 domain-name You can specify an SQL data type directly or name a domain. If you name a domain, the parameter inherits the data type of the domain. 3 FIXED The FIXED, NULL TERMINATED BYTES, and NULL TERMINATED CHARACTERS clauses tell the module processor how to interpret C language text fields. Example 3 shows how the size of the text field you declare varies according to which of the three interpretation options you select. If you specify FIXED, the module processor interprets CHAR fields from the dictionary as fixed-length character strings. The FIXED clause is available only on OpenVMS platforms. 3 FROM_path_name Specifies the data dictionary path name of a data dictionary record definition. You can use this clause to retrieve data definitions from the dictionary. The data dictionary record definition that you specify cannot contain any OCCURS clauses or arrays. You must specify a data dictionary record definition that contains only valid SQL or Oracle Rdb data types. The FROM path-name clause cannot be used in a second-level record specification (a record-type that you specify within record- type). The FROM path-name clause is available only on OpenVMS platforms. 3 IDENTIFIER_CHARACTER_SET Specifies the character set used for object names such as cursor names and table names. If you do not specify a character set in this clause or in the NAMES ARE clause, the default is DEC_MCS. This clause overrides the character set specified in the NAMES ARE clause. The specified character set must contain ASCII. NOTE If the dialect or character sets are not specified in the module header, SQL uses the RDB$CHARACTER_SET logical name to determine the character sets to be used by the database. See the Oracle Rdb7 SQL Reference Manual for more detail regarding the RDB$CHARACTER_SET logical name. The RDB$CHARACTER_SET logical name is deprecated and will not be supported in a future release. 3 INDICATOR_ARRAY_OF Specifies a one-dimensional array of elements with one of the data types shown in the exact-numeric-type diagram. An indicator array provides indicator parameters for fields in the host structure. The indicator array must have at least as many elements in it as the record definition has. You cannot use an indicator array as a record or contain it within a record. In other words, the INDICATOR ARRAY OF clause cannot be used in a second-level record specification (a record- type that you specify within record-type). You cannot explicitly refer to individual elements in an indicator array. For this reason, you cannot use indicator arrays in UPDATE statements or WHERE clauses. 3 INTEGER_n_IS Syntax options: INTEGER n IS 4 BYTES INTEGER n IS 8 BYTES Explicitly specifies the precision of INTEGER module parameters in number of bytes. This syntax overrides the module default size for INTEGER parameters. When calling an SQL module procedure with parameters of type INTEGER, you must pass a host language variable of the correct size. The correct size depends on the operating system, language, and, on Digital UNIX, the -int32 and -int64 compiler qualifiers. The parameter size option is not specific to a calling language or operating system. It is most useful for host programs written in C on Digital UNIX where variables declared as "int" and "long" are a different size. 3 item-name Specifies the name of an item in a record. Do not give the same name for two record items at the same level in the same record declaration. When SQL statements within a procedure refer to an item name within a subrecord in the same procedure as a parameter declaration, they must fully qualify the item name with the record name and all intervening subrecord names. Separate record names from item names with periods. 3 KEYWORD_RULES Controls whether or not identifiers can be keywords. If you specify SQL92, SQL89, or MIA, you cannot use keywords as identifiers, unless you enclose them in double quotation marks. If you specify SQLV40, you can use keywords as identifiers. The default is SQLV40. Use the KEYWORD RULES clause, rather than the ANSI_IDENTIFIER qualifier, because the qualifier will be deprecated in a future release. 3 LANGUAGE A keyword that specifies the name of the host language in which the program is written. This program calls the procedures in the module. Specify GENERAL for languages that do not have a corresponding keyword in the LANGUAGE clause. The language identifier determines: o The kinds of data types that the SQL module processor considers valid in the module's formal parameter declarations. If a language does not support a data type equivalent to some SQL data type, the SQL module processor generates a warning message when it encounters the data type in a formal parameter. (A formal parameter is the name in an SQL module procedure declaration that represents the corresponding actual parameter in a host language call to the SQL module procedure.) For example, SQL supports the BIGINT data type, but PL/I does not. The module processor generates a warning message when it encounters a BIGINT formal parameter in an SQL module that specifies the PL/I language in the LANGUAGE section. o The default mechanism for passing parameters to and from a host language source file. Parameters are always passed by the default passing mechanism for the language specified in the language clause. Default Passing Mechanism for Host Languages to SQL Modules shows those defaults. Table 1 Default Passing Mechanism for Host Languages to SQL Modules Language Passing Mechanism Ada By reference BASIC CHAR by descriptor; all others by reference C By reference COBOL By reference FORTRAN CHAR, SQLCA, SQLDA by descriptor; all others by reference Pascal By reference PL/I By reference GENERAL By reference o The default data type that SQL expects for certain actual parameters. In COBOL, for example, if a DOUBLE PRECISION formal parameter is declared in an SQL module procedure, the procedure expects the parameter to be passed from the calling module as D_FLOAT rather than G_FLOAT because COBOL does not support G_FLOAT. Similarly, in C, if a CHAR(n) formal parameter is declared in an SQL module procedure, the procedure expects the parameter to be passed from the calling module as an ASCIZ string with a length of (n+1). 3 LITERAL_CHARACTER_SET Specifies the character set for literals that are not qualified by a character set or national character set. If you do not specify a character set in this clause or in the NAMES ARE clause, the default is DEC_MCS. This clause overrides the character set for unqualified literals specified in the NAMES ARE clause. See the Oracle Rdb7 SQL Reference Manual for a list of the allowable character sets. 3 MODULE An optional name for the module. If you do not supply a module name, the default name is SQL_MODULE. Use any valid operating system name. (See the Oracle Rdb7 SQL Reference Manual for more information on user-supplied names.) However, the name must be unique among the modules that are linked together to form an executable image. 3 NAMES_ARE Specifies the character set used for the default, identifier, and literal character sets for the module. This clause also specifies the character string parameters that are not qualified by a character set or national character set. If you do not specify a character set, the default is DEC_MCS. The character set specified in this clause must contain ASCII. 3 NATIONAL_CHARACTER_SET Specifies the character set for literals qualified by the national character set and for parameters defined as a national character data type (NCHAR, NCHAR VARYING). If you do not specify a character set in this clause, the default is DEC_MCS. See the Oracle Rdb7 SQL Reference Manual for a list of the allowable character sets. 3 NULL_TERMINATED_BYTES Specifies that text fields from the dictionary are null- terminated. The module processor interprets the length field in the dictionary as the number of bytes in the string. If n is the length in the dictionary, then the number of data bytes is n-1 and the length of the string is n bytes. In other words, the module processor assumes that the last character of the string is for the null terminator. Thus, a field that the dictionary lists as 10 characters can hold only a 9- character SQL field from the C module language. (Other module languages could fit a 10-character SQL field into it.) If you do not specify a character interpretation option, NULL TERMINATED BYTES is the default. The NULL TERMINATED BYTES clause is available only on OpenVMS platforms. 3 NULL_TERMINATED_CHARACTERS Specifies that CHAR fields from the dictionary are null- terminated, but the module processor interprets the length field as a character count. If n is the length in the dictionary, then the number of data bytes is n, and the length of the string is n+1 bytes. The NULL TERMINATED CHARACTERS clause is available only on OpenVMS platforms. 3 param-decl One or more parameter declarations, up to a limit of 255 parameters. Parameters in an SQL module procedure allow values to pass between a database, the SQL statement in a procedure, and variables in a host language program. Each parameter declared in an SQL procedure must correspond to a variable specified as an actual parameter in the host language call to the procedure: o The number of formal parameters must be the same as the number of variables in the actual parameters. o The order of parameter declarations in the SQL module procedure must be the same as the order of the corresponding parameters in the host language call. o The passing mechanism for each parameter must match the passing mechanism specified for the corresponding actual parameter. You cannot reference an SQLMOD parameter of type SQLDA (including the SQLDA parameter itself) in a procedure except in the appropriate places in the following statements: o DESCRIBE o EXECUTE o FETCH o OPEN o PREPARE For example, the following statements are not allowed: SELECT COUNT(*) INTO SQLDA FROM EMPLOYEES EXECUTE DYN_STMT USING SQLDA However, the following statement is acceptable: EXECUTE DYN_STMT USING DESCRIPTOR SQLDA 3 parameter-name The name for a formal parameter. Use any valid SQL name. See the Oracle Rdb7 SQL Reference Manual for more information on user-supplied names. Formal parameter names do not have to be the same as the host language variables for the actual parameters to which they correspond. However, making the names the same is a useful convention for keeping track of which parameter corresponds to which host language variable. SQLCA, SQLCODE, SQLDA, SQLDA2, and SQLSTATE are special-purpose parameters and do not require user-supplied names (although you can optionally specify a parameter name with SQLDA or SQLDA2). There are three ways to specify a valid SQL data type for the formal parameter: o data-type o domain-name o record-type 3 PARAMETER_COLONS If you use the PARAMETER COLONS clause, all parameter names must begin with a colon (:). This rule applies to both declarations and references of module language procedure parameters. If you do not use this clause, no parameter name can begin with a colon. The current default behavior is no colons are used. However, this default is deprecated syntax. In the future, colons will be the default because it allows processing of ANSI-standard modules. Use the PARAMETER COLONS clause, rather than the ANSI_PARAMETERS qualifier, because the qualifier will be deprecated in a future release. 3 PROCEDURE Specifies the name of a procedure. Use any valid OpenVMS name. (See the Oracle Rdb7 SQL Reference Manual for more information on user-supplied names.) The procedure name is used in host language calls to specify a particular procedure. In addition to a procedure name, a procedure in an SQL module must contain one or more parameter declarations and an SQL statement. 3 QUOTING_RULES Controls whether double quotation marks are interpreted as string literals or delimited identifiers. If you specify SQL92, SQL89, or MIA, SQL interprets double quotation marks as delimited identifiers. If you specify SQLV40, SQL interprets double quotation marks as literals. The default is SQLV40. Use the QUOTING RULES clause, rather than the ANSI_QUOTING qualifier, because the qualifier will be deprecated in a future release. 3 record-type You can pass records and indicator arrays to SQL module language procedures using the record-type clause. You can also pass records and indicator arrays to SQL module language procedures and retrieve data dictionary record declarations using the record-type clause. If a record reference has an indicator, it must be an indicator array. Specify the INDICATOR ARRAY OF clause instead of an item name or path name. The following example shows the use of record structures and indicator arrays in an SQL module language program. Because parameters in the module are preceded by colons, you must include the PARAMETER COLONS clause in the module header. MODULE employee_module LANGUAGE pascal AUTHORIZATION pers PARAMETER COLONS DECLARE pers ALIAS FOR FILENAME mf_personnel DECLARE WORK_STATUS_CURSOR CURSOR FOR SELECT * FROM PERS.WORK_STATUS PROCEDURE OPEN_WORK_STATUS SQLCODE; OPEN WORK_STATUS_CURSOR; PROCEDURE CLOSE_WORK_STATUS SQLCODE; CLOSE WORK_STATUS_CURSOR; PROCEDURE FETCH_EMPS_TO_DEPS_CURSOR SQLCODE, :work_status_rec record status_code PERS.work_status.STATUS_CODE_DOM status_name PERS.work_status.STATUS_NAME_DOM status_type PERS.work_status.STATUS_DESC_DOM end record :ind_array record indicator array of 3 SMALLINT end record ; FETCH WORK_STATUS_CURSOR INTO :work_status_rec INDICATOR :ind_array; 3 RECORD...END_RECORD Specifies the beginning and end of the record that you are supplying in a module language parameter declaration. A record definition cannot contain an SQLDA, an SQLDA2, an SQLCODE, an SQLCA, or an SQLSTATE. 3 RIGHTS Specifies whether or not a module must be executed by a user whose authorization identifier matches the module authorization identifier. If you specify RESTRICT, SQL bases privilege checking on the default authorization identifier. The default authorization identifier is the authorization identifier of the user who compiles a module unless you specify a different authorization identifier using an AUTHORIZATION clause in the module. The RESTRICT option causes SQL to compare the user name of the person who executes a module with the default authorization identifier and prevent any user other than one with the correct authorization identifier from invoking that module. All applications that use multischema will be the invoker by default. If you specify INVOKER, SQL bases the privilege on the authorization identifier of the user running the module. The default is INVOKER. Use the RIGHTS clause, rather than the ANSI_AUTHORIZATION qualifier, because the qualifier will be deprecated in a future release. 3 SCHEMA Specifies the default schema name for the module. The default schema is the schema to which SQL statements refer if those statements do not qualify table and other schema names with an authorization identifier. If you do not specify a default schema name for a module, the default schema name is the same as the authorization identifier. Using the SCHEMA clause, separate SQL modules can each declare different schemas as default schemas. This can be convenient for an application that needs to refer to more than one schema. By putting SQL statements that refer to a schema in the appropriate module's procedures, you can minimize tedious qualification of schema element names in those statements. When you specify SCHEMA schema-name AUTHORIZATION authorization- name, you specify the schema name and the schema authorization identifier for the module. The schema authorization identifier is considered the owner and creator of the schema and everything in it. When the FIPS flagger is enabled for entry-level SQL92 or lower, the SCHEMA clause (by itself or used with the AUTHORIZATION clause) is flagged as nonstandard ANSI syntax. If procedures in the SQL module always qualify table names with an authorization identifier, the SCHEMA clause has no effect on SQL statements in the procedures. 3 simple-statement A single executable SQL statement. See information about which SQL statements are executable and for a complete description of a simple statement, see the Oracle Rdb7 SQL Reference Manual. 3 SQLCA A formal parameter for the SQLCA (see the Oracle Rdb7 SQL Reference Manual for more information on the SQLCA). The calling program module must declare a record that corresponds to the structure of the SQLCA and specify that record declaration as the calling parameter for the SQLCA formal parameter. Specifying SQLCA as a formal parameter is an alternative to specifying SQLCODE. Using SQLCA instead of SQLCODE lets the calling program module take advantage of the information SQL puts in the third element of the SQLERRD array in the SQLCA. Future versions of SQL may use the SQLCA for additional information. 3 SQLCODE A formal parameter that SQL uses to indicate the execution status of the SQL statement in the procedure. The SQLCODE formal parameter does not require a data type declaration; SQL automatically declares SQLCODE with an INTEGER data type. However, the calling program module must still declare an integer variable for the actual parameter that corresponds to SQLCODE. The SQLCODE parameter must be passed by reference. Oracle Rdb recommends that you use the SQLSTATE status parameter rather than SQLCODE. SQLSTATE complies with ANSI/ISO SQL standard and SQLCODE may be deprecated in a future release of Oracle Rdb. See the Oracle Rdb7 SQL Reference Manual for more information about SQLCODE. 3 SQLDA_SQLDA2 A formal parameter for the SQLDA or SQLDA2 (see the Oracle Rdb7 SQL Reference Manual for more information on the SQLDA and SQLDA2). The calling program module must declare a record that corresponds to the structure of the SQLDA or SQLDA2 and specify that record declaration as the calling parameter for the SQLDA or SQLDA2 formal parameter. You can optionally precede SQLDA or SQLDA2 in the parameter declaration with another name the SQL statement in the module procedure can use to refer to the SQLDA or SQLDA2. 3 SQLSTATE A formal parameter that SQL uses to indicate the execution status of the SQL statement in the procedure. The SQLSTATE formal parameter does not require a data type declaration; SQL automatically declares SQLSTATE with a CHAR(5) data type. However, the calling program module must still declare a character variable for the actual parameter that corresponds to SQLSTATE. The SQLSTATE parameter must be passed by reference. Oracle Rdb recommends that you use the SQLSTATE status parameter rather than SQLCODE. SQLSTATE complies with the ANSI/ISO SQL standard and SQLCODE may be deprecated in a future release of Oracle Rdb. 3 VIEW_UPDATE_RULES Specifies whether or not the SQL module processor applies the ANSI/ISO standard for updatable views to all views created during compilation. If you specify SQL92, SQL89, or MIA, the SQL module processor applies the ANSI/ISO standard for updatable views to all views created during compilation. Views that do not comply with the ANSI/ISO standard for updatable views cannot be updated. The default is SQLV40. The ANSI/ISO standard for updatable views requires the following conditions to be met in the SELECT statement: o The DISTINCT keyword is not specified. o Only column names can appear in the select list. Each column name can appear only once. Functions and expressions such as max(column_name) or column_name +1 cannot appear in the select list. o The FROM clause refers to only one table. This table must be either a base table or a derived table that can be updated. o The WHERE clause does not contain a subquery. o The GROUP BY clause is not specified. o The HAVING clause is not specified. If you specify SQLV40, SQL does not apply the ANSI/ISO standard for updatable views. Instead, SQL considers views that meet the following conditions to be updatable: o The DISTINCT keyword is not specified. o The FROM clause refers to only one table. This table must be either a base table or a view that can be updated. o The GROUP BY clause is not specified. o The HAVING clause is not specified. 2 Examples Example 1: Calling an SQL module procedure from a Pascal program The following example is a Pascal program that calls a procedure in an SQL module file: PROGRAM list_employees(OUTPUT); { Program to list employees' names whose last name matches a LIKE predicate. Note the following: 1) The input parameter (like_string) to the SELECT expression in the DECLARE CURSOR is supplied on the OPEN_CURSOR call. 2) The output parameters are returned on each FETCH_INTO call. 3) The cursor is closed after the desired rows are processed, so that it will be positioned properly in subsequent operations. } TYPE LAST_NAME = PACKED ARRAY[1..14] OF CHAR; FIRST_NAME = PACKED ARRAY[1..10] OF CHAR; VAR { Variable data } sqlcode : INTEGER := 0; emp_last : LAST_NAME; emp_first: FIRST_NAME; like_string : LAST_NAME := 'T_ _ _ _ _ _ _ _ _ _ _ _ _'; { Declarations of entry points in the SQL module } PROCEDURE SET_TRANS (VAR sqlcode : INTEGER); EXTERNAL; PROCEDURE OPEN_CURSOR (VAR sqlcode: INTEGER; name : LAST_NAME); EXTERNAL; PROCEDURE FETCH_INTO (VAR sqlcode : INTEGER; VAR last : LAST_NAME; VAR first : FIRST_NAME); EXTERNAL; PROCEDURE CLOSE_CURSOR (VAR sqlcode : INTEGER); EXTERNAL; PROCEDURE ROLLBACK_TRANS (VAR sqlcode : INTEGER); EXTERNAL; BEGIN SET_TRANS (sqlcode); { Start a read-only transaction.} OPEN_CURSOR (sqlcode, like_string);{ Open the cursor, supplying } { the string to match against. } WRITELN('Matching Employees:'); { Print header. } REPEAT { Iterate matching names. } BEGIN FETCH_INTO (sqlcode, emp_last, emp_first);{ Fetch the next name. } IF sqlcode = 0 THEN WRITELN(emp_first, emp_last); { Print employee information. } END UNTIL sqlcode <> 0; IF sqlcode <> 100 { Print any error information. } THEN WRITELN ('SQL error code = ', sqlcode); CLOSE_CURSOR (sqlcode); { Finish the cursor operation. } ROLLBACK_TRANS (sqlcode); { Finish the transaction. } END. Here is the SQL module file that this program calls: MODULE employees LANGUAGE PASCAL AUTHORIZATION SQL_USER ALIAS RDB$DBHANDLE DECLARE ALIAS FOR FILENAME PERSONNEL DECLARE names CURSOR FOR SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE match_string PROCEDURE SET_TRANS SQLCODE; SET TRANSACTION READ ONLY; PROCEDURE OPEN_CURSOR SQLCODE match_string CHAR(14); OPEN names; PROCEDURE FETCH_INTO SQLCODE l_name CHAR(14) f_name CHAR(10); FETCH names INTO l_name, f_name; PROCEDURE CLOSE_CURSOR SQLCODE; CLOSE names; PROCEDURE ROLLBACK_TRANS SQLCODE; ROLLBACK; Example 2: Calling an SQL module procedure from a C program The following example is a C program that calls a procedure that is in an SQL module file: /* C program to list employees' names where the last name matches a LIKE predicate. Note the following: 1) The input parameter (like_string) to the SELECT expression in the DECLARE CURSOR is supplied on the OPEN_CURSOR call. 2) The output parameters are returned on each FETCH_INTO call. 3) The cursor is closed after the desired rows are processed, so that it will be positioned properly in subsequent operations. */ #dictionary "name"; typedef struct name NAME_TYPE; extern void FETCH_INTO (int *sqlcode, NAME_TYPE *name_record); typedef char LAST_NAME[15]; typedef int *SQLCODE; /* Declarations of entry points in the SQL module */ extern void SET_TRANS (int *sqlcode); extern void OPEN_CURSOR (int *sqlcode, LAST_NAME name); extern void CLOSE_CURSOR (int *sqlcode); extern void ROLLBACK_TRANS (int *sqlcode); main() { int sqlcode = 0; NAME_TYPE name_record; LAST_NAME like_string = "T_ _ _ _ _ _ _ _ _ _ _ _ _"; SET_TRANS (&sqlcode); /* Start a read-only transaction. */ OPEN_CURSOR (&sqlcode, like_string); /* Open the cursor, supplying */ /* the string to match against. */ printf ("Matching Employees:\n"); /* Print header. */ do /* Iterate matching names. */ { FETCH_INTO (&sqlcode, &name_record);/* Fetch the next name. */ if (sqlcode == 0) printf ("%s%s\n", name_record.f_name, name_record.l_name); } /* Print employee information. */ while (sqlcode == 0); if (sqlcode != 100) /* Print any error information. */ printf ("SQL error code = %d\n", sqlcode); CLOSE_CURSOR (&sqlcode); /* Complete the cursor operation. */ ROLLBACK_TRANS (&sqlcode); /* Finish the transaction. */ } Here is the SQL module file that this program calls: MODULE employees LANGUAGE C AUTHORIZATION SQL_USER ALIAS RDB$DBHANDLE DECLARE ALIAS FOR PATHNAME 'MF_PERSONNEL' DECLARE names CURSOR FOR SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEES WHERE LAST_NAME LIKE match_string PROCEDURE SET_TRANS SQLCODE; SET TRANSACTION READ ONLY; PROCEDURE OPEN_CURSOR SQLCODE match_string CHAR(14); OPEN names; PROCEDURE FETCH_INTO SQLCODE, name_record RECORD FROM 'name' END RECORD; FETCH names INTO name_record; PROCEDURE CLOSE_CURSOR SQLCODE; CLOSE names; PROCEDURE ROLLBACK_TRANS SQLCODE; ROLLBACK; 2 Character_Parameters To ensure that you specify the length of character string parameters correctly, use the following guidelines: o For C host language programs that call SQL modules declared with LANGUAGE C, any character parameters that correspond to character data type columns must be defined as the length of the longest valid column value in octets, plus 1 octet to allow for the null terminator. o For other host language programs (or C host language programs that call SQL modules declared with LANGUAGE GENERAL), any character parameters that correspond to character data type columns must be defined as the length of the longest valid column value in octets. o When calculating the length of the longest valid column value, you must take into consideration the number of octets for each character in the character set of the column and whether the SQL module language interprets the length of columns in characters or octets. A program can control how the SQL module language interprets the length of columns in the following ways: - The CHARACTER LENGTH clause of the module header or DECLARE MODULE statement - The DIALECT clause of the module header or DECLARE MODULE statement - For dynamic SQL, the SET CHARACTER LENGTH statement 2 Host_Language_Data_Types The SQL data type specified for the formal parameter in a module must be equivalent to the data type of the host language variable declaration for the actual parameter. If the formal parameter and actual parameter are not declared with equivalent data types, SQL can give unpredictable results. However, host languages typically do not support the same set of data types that SQL supports. To work with a column in a database defined with a data type not supported in a host language, the module must declare formal parameters of a data type that the host language supports. SQL automatically converts between the data type of the database column and the formal parameter when it processes the SQL statement in a procedure. SQL and Corresponding OpenVMS Data Types for Module Language shows the OpenVMS data types that SQL requires for actual parameters when you declare formal parameters for each SQL data type. 2 Command_Line You can define a symbol to make invoking the SQL module processor easier. For example: $ SQLMOD == "$SQL$MOD" You then can invoke the SQL module processor with or without a module file specification: o If you invoke the SQL module processor without a module file specification, the module processor prompts you for it. For example: $ SQLMOD INPUT FILE> module-file-specification o If you invoke the SQL module processor with a module file specification as part of the DCL command line, SQL starts processing your module file immediately after you press the Return key. For example: $ SQLMOD module-file-specification Either way, there are several qualifiers you can specify with the file specification that control how SQL processes the module file. The syntax diagram shows the format for those qualifiers. 3 Format (B)0module-file-spec-qual =    SQLMOD qqqq> module-file-spec qwqqqqqqqqqqqqqqqqqqqqqqqqqwqq> tq> <context-file-name> qqu  tq> module-qualifiers-1 qqu   mq> module-qualifiers-2 qqj  (B)0module-qualifiers-1 =   qqwwqwqq> no-qualifiers-1 qqwqqqqqqqqqqqqqqqqqwwq> xx mqq> no-qualifiers-2 qqj xx  xtq> /C_STRING = c-string-options qqqqqqqqqqux   xtq> /CONSTRAINT_MODE = qwq> IMMEDIATE qqqqqux   xx tq> DEFERRED qqqqqqux   xx tq> OFF qqqqqqqqqqqux   xx mq> ON qqqqqqqqqqqqux   xtq> /CONTEXT = qwq> NONE qqqqqqqqqqqqqqqqqqux   xx   tq> ALL qqqqqqqqqqqqqqqqqqqux   xx   mq> procedure-list qqqqqqqqux  xtq> /USER_DEFAULT = qqqqqqqqqqqqux xmq> /PASSWORD_DEFAULT =  qqqqqqqqjx mqqqqqqqqqqqqqqqqqqqqq / wqqqqwqwq> ALIGN_RECORDS qqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>  m NO j tq> C_PROTOTYPES qwqqqqqqqqqqqqqqqqqqwqqqqqqqqu   x mq> = <file-name> qj x tq> CONNECT  qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> EXTERNAL_GLOBALS qqqqqqqqqqqqqqqqqqqqqqqqqu    tq> FLAG_NONSTANDARD  qqwqqqqqqqqqqqqqqqqqqqwu    x tq> =  SQL92_ENTRY qux      x tq> =  SQL89 qqqqqqqux      x mq> =  MIA qqqqqqqqqjx      tq> G_FLOAT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu     tq> INITIALIZE_HANDLES qqqqqqqqqqqqqqqqqqqqqqqu  tq> LOWERCASE_PROCEDURE_NAMES qqqqqqqqqqqqqqqqu tq> LIST qqqqwqqqqqqq>qqqqqqqqqwqqqqqqqqqqqqqqu     x mq> = <file-spec> j   x  tq> LONG_SQLCODE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   mq> MACHINE_CODE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   (B)0no-qualifiers-2 =        q> / wq>qqwqwq> OBJECT qqwqqqqqqq>qqqqqqqqqwqqqqqqqqqqqqqqwq>   m NO j x mq> = <file-spec> j   x   tq> PACKAGE_COMPILATION qqqqqqqqqqqqqqqqqqqqqqu    tq> PARAMETER_CHECK  qqqqqqqqqqqqqqqqqqqqqqqqqu tq> QUERY_ESTIMATES  qqqqqqqqqqqqqqqqqqqqqqqqqu tq> TRANSACTION_DEFAULT qwqqqqqqqqqqqqqqqqqqwqu x tq> = IMPLICIT qqqqu x x mq> = DISTRIBUTED qj x mq> WARN qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqj     m> = ( w> warning-option w> ) qqj        mqqqqq ,  WARNING qqqqqqwqqwq> x tq> NOWARNING qqqqu x  x tq> DEPRECATE  qqqu x  x mq> NODEPRECATE qj x  mqqqqqqqqq ,  BLANK_FILL qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqw> xx m> NO qj x xmqqwqqqqqqw> FIXED_CDD_STRINGS qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x m> NO qj x m> ( wqqqqqw> BLANK_FILL q> , wqqqqqqw> FIXED_CDD_STRINGS q> ) j m> NO j m> NO qj (B)0module-qualifiers-2 =   qqqqwwq> /database-options qqqqqqqqqqqqqqqqqqqqqqqwwq>  xtq> /OPTIMIZATION_LEVEL = qwq> DEFAULT qqqqqqux  xx  tq> FAST_FIRST qqqux  xx  mq> TOTAL_TIME qqqux  xtq> /QUERY_TIME_LIMIT = qqqqqux  xtq> /QUERY_MAX_ROWS = qqqqqqqqqqux   xtq> /QUERY_CPU_TIME_LIMIT =  qux xmq> /ROLLBACK_ON_EXIT qqqqqqqqqqqqqqqqqqqqqqqjx   mqqqqqqqqqqqqqqqqqqqqq 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)0rdb-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 mq> RDB070 qqj (B)0procedure-list =   qq> ( qwq> <procedure> wqqqqqqqqqqqqqqqqqqqwwq> ) q>  x  mqq> : <entry-name> jx   mqqqqqqqqqqqqqqq , ATTACH 'FILENAME intro_personnel'; SQL> DECLARE MY_CURSOR cont> TABLE CURSOR FOR cont> SELECT * FROM EMPLOYEES; SQL> OPEN MY_CURSOR; SQL> SHOW SQLCA; SQLCA: SQLCAID: SQLCA SQLCABC: 128 SQLCODE: 0 SQLERRD: [0]: 0 [1]: 0 [2]: 100 [3]: 16 [4]: 0 [5]: 0 SQLWARN0: SQLWARN1: SQLWARN2: SQLWARN3: SQLWARN4: SQLWARN5: SQLWARN6: SQLWARN7: 4 QUERY_MAX_ROWS Limits the number of records returned during query processing by counting the number of rows returned by the query and returning an error message if the query exceeds the total number of rows specified. The default is an unlimited number of record fetches. Dynamic SQL options are inherited from the compilation qualifier. 4 QUERY_TIME_LIMIT Limits the number of records returned during query processing by counting the number of seconds used to process the query and returning an error message if the query exceeds the total number of seconds specified. The default is unlimited time for the query to compile. Dynamic SQL options are inherited from the compilation qualifier. 4 ROLLBACK_ON_EXIT Rolls back outstanding transactions when a program exits from SQL. On OpenVMS outstanding transactions are committed when a program exits from SQL by default. Therefore, if you want to roll back changes, specify this qualifier on the command line. 4 TRANSACTION_DEFAULT Syntax options: TRANSACTION_DEFAULT=IMPLICIT TRANSACTION_DEFAULT=DISTRIBUTED NOTRANSACTION_DEFAULT Specifies when SQL starts a transaction and how SQL handles default distributed transactions. You can specify the following options: o TRANSACTION_DEFAULT=IMPLICIT Causes SQL to start a transaction when you issue either a SET TRANSACTION statement or the first executable SQL statement in a session. o TRANSACTION_DEFAULT=DISTRIBUTED Causes SQL to use the distributed transaction identifier (TID) for the default distributed transaction established by the DECdtm system service SYS$START_TRANS. Using this option eliminates the need to declare context structures in host language programs and to pass context structures to SQL module procedures. Because it closes all cursors, it also eliminates the need to call the SQL_CLOSE_CURSORS routine. You must explicitly call the DECdtm system services when you use this option. This option provides support for the Structured Transaction Definition Language (STDL) of the Multivendor Integration Architecture (MIA) standard. If you specify the TRANSACTION_DEFAULT=DISTRIBUTED option with the CONTEXT qualifier, you must declare a context structure and pass the context structure to the statements named in the CONTEXT qualifier or, if you specify CONTEXT=ALL, to most executable statements involved in the distributed transaction. See the Oracle Rdb7 SQL Reference Manual for information about which executable statements do not require a context structure. o NOTRANSACTION_DEFAULT Prevents SQL from starting a transaction unless you execute a SET TRANSACTION statement. If you use this qualifier and issue an executable statement without first issuing a SET TRANSACTION statement, SQL returns an error. The default is TRANSACTION_DEFAULT=IMPLICIT. 4 USER_DEFAULT Specifies the user name at compile time. If you use the USER DEFAULT clause of the DECLARE ALIAS statement, you use this qualifier to pass the compile-time user name to the program. 4 WARNING Syntax options: WARNING NOWARNING You can use combinations of the warning options to specify which warning messages the SQL module processor writes. If you specify only a single warning option, you do not need the parentheses. The WARNING and NOWARNING qualifiers specify whether or not the SQL module processor writes informational and warning messages. 4 warning-option Specifies whether the SQL module processor writes informational and warning messages to your terminal, a list file, or both. The WARN qualifier is the default. You can specify two warning options with the WARN qualifier to customize message output. You cannot specify warning options if you specify the NOWARN qualifier. 3 Example Example 1: Compiling and linking a program with an SQL module $ SQLMOD :== $SQL$MOD $ SQLMOD LIST_EMP_PASMOD.SQLMOD $ PASCAL LIST_EMP.PAS $ ! This LINK command requires that the logical name $ ! LNK$LIBRARY is defined as SYS$LIBRARY:SQL$USER.OLB $ LINK LIST_EMP.OBJ, LIST_EMP_PASMOD.OBJ $ RUN LIST_EMP.EXE Matching Employees: Alvin Toliver Louis Tarbassian 1 SQLPRE The SQL precompiler provides special keywords and syntax that allow you to include (embed) simple and compound statements directly into host language programs. Then you can use the SQL precompiler to process the combined embedded statements and host language code to produce an object file for linking and execution. 2 Environment You can use SQL precompiler syntax only in Ada, C, COBOL, FORTRAN, Pascal, and PL/I host language source files. The SQL precompiler supports no other host languages. If you use a host language other than the ones mentioned for embedded SQL and you want to use the SQL interface with it, you must use the SQL module processor. 2 Format (B)0EXEC SQL qqwqq> simple-statement qqqqwqqwqqqqqqqqqqqqqqqqqqqqqwqqq>  mqq> compound-statement qqj mqq> ending-symbol qqqj   (B)0ending-symbol = qqqqqwq> ; qqqqqqqqwq> mq> END-EXEC qj (B)0simple-statement = qqqq> SQL statement qqqq> (B)0compound-statement = qqwqqqqqqqqqqqqqqqqqqqqqqwq> BEGIN qqwqqqqqqqqqqqqqqqqqqqqqqwqk mq> <beginning-label:> j mq> ON ALIAS <alias> qqj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq qqqqqqqqqqqqqqqqqqwqqqqqqqqqqk tq> ATOMIC qqqqqqu mqwq> variable-declaration qwj x mq> NOT ATOMIC qqj mqqqqqqqqq  END qqwqqqqqqqqqqqqqqqqqqwq> mqwq> compound-use-statement qwj mq> <ending-label> j mqqqqqqqqqqqq  pre-host-file-spec o If you invoke the SQL precompiler with a host language program file as part of the DCL command line, SQL starts processing your file immediately after you press the Return key. For example: $ SADA pre-host-file-spec pre-qualifiers Whichever method you choose to invoke the precompiler, you have the option to specify a wide range of qualifiers that control how the SQL precompiler processes the module file. The syntax diagrams show the format for the qualifiers that you can include with the host language program file specification. 3 Format (B)0pre-host-file-qual = SQLPRE qq> pre-host-file-spec qwqqqqqqqqqqqqqqqqqqqqqqqqwqk mq> <context-file-name> qj x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj mq> pre-lang-qualifiers qqqqwqqqqqqqqqqqqqqqqqqqqqwqqqqqq> mqq> pre-qualifiers qqj (B)0pre-lang-qualifiers = qqq> / qwq> ADA qqqqqqqqqqqqqqqqqqqqwqq>  tq> CC qqwqqqqqqqqqqqqqqqqwqu   x tq> =VAXC qqqqqqqu x x mq> =DECC qqqqqqqj x tq> COBOL qqqqqqqqqqqqqqqqqqu   tq> FORTRAN qqqqqqqqqqqqqqqqu   tq> PASCAL qqqqqqqqqqqqqqqqqu   mq> PLI qqqqqqqqqqqqqqqqqqqqj       (B)0pre-qualifiers =    qqqwwq> / qwqqqqqqwqqwq> ANSI_FORMAT qqqqqqqqqqqqqqqqqqqqqqwwqq>   xx mq NO qj tq> EXTEND_SOURCE qqqqqqqqqqqqqqqqqqqqux  xx tq> G_FLOAT qqqqqqqqqqqqqqqqqqqqqqqqqqux xx tq> LIST qqqqqqqqqqqqqqqqqqqqqqqqqqqqqux xx tq> MACHINE_CODE qqqqqqqqqqqqqqqqqqqqqux xx mq> OBJECT qwqqqqqqqqqqqqqqqqqqwqqqqqqux  xx  mq> = <file-spec> qj xx   xmq> / SQLOPTIONS= ( qwwq> opt-no-qualifiers qwwq> ) qqqjx     x  xmq> opt-qualifiers qqqqjx  x   x  mqqqqqqqqq ,  CONNECT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   mq> NO qqj tq> DECLARE_MESSAGE_VECTOR qqqqqqqqqqqqqqqqqqqqqu  tq> EXTERNAL_GLOBALS qqqqqqqqqqqqqqqqqqqqqqqqqqqu  tq> FLAG_NONSTANDARD qqqwqqqqqqqqqqqqqqqqqqqwqqu  x tqq> = SQL92_ENTRY qu x  x tqq> = SQL89 qqqqqqqu x   x mqq> = MIA qqqqqqqqqj x   tq> INITIALIZE_HANDLES qqqqqqqqqqqqqqqqqqqqqqqqqu   tq> QUERY_ESTIMATES qqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> TRANSACTION_DEFAULT qwqqqqqqqqqqqqqqqqqqwqqqu   x tq> = IMPLICIT qqqqu x   x mq> = DISTRIBUTED qj x   mq> WARN  wqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqj   m> = ( qwq> warning-option qwq> ) qj   mqqqqqq ,  WARNING qqqqqqwqqwq> x tq> NOWARNING qqqqu x  x tq> DEPRECATE  qqqu x  x mq> NODEPRECATE qj x  mqqqqqqqqq ,  C_STRING = c-string-options qqqqqqqqqqqqqqqqqqqqqqqqwqq>  tq> constraint-options qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> database-options qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> optimization-options qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> QUERY_TIME_LIMIT = qqqqqqqqqqqqqqqqqu   tq> QUERY_MAX_ROWS = qqqqqqqqqqqqqqqqqqqqqqu   tq> QUERY_CPU_TIME_LIMIT = qqqqqqqqqqqqqu  tq> USER_DEFAULT =  qqqqqqqqqqqqqqqqqqqqqqqqqqu  tq> PASSWORD_DEFAULT =  qqqqqqqqqqqqqqqqqqqqqqu mq> ROLLBACK_ON_EXIT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj (B)0c-string-options = qwwqqwqqqqqqw> BLANK_FILL qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqw> xx m> NO qj x xmqqwqqqqqqw> FIXED_CDD_STRINGS qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x m> NO qj x m> ( wqqqqqw> BLANK_FILL q> , wqqqqqqw> FIXED_CDD_STRINGS q> ) j m> NO j m> NO qj (B)0constraint-options =  qqqqq> CONSTRAINT_MODE = qwq> IMMEDIATE qqqqqqqqqqqqqqqqqwq>    tq> DEFERRED qqqqqqqqqqqqqqqqqqu     tq> ON qqqqqqqqqqqqqqqqqqqqqqqqu     mq> OFF qqqqqqqqqqqqqqqqqqqqqqqj (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)0rdb-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 mq> RDB070 qqj (B)0optimization-options = qqqqq> OPTIMIZATION_LEVEL = qwq> DEFAULT qqqqqwqqq> tq> FAST_FIRST qqu mq> TOTAL_TIME qqj 3 Arguments 4 ANSI_FORMAT Syntax options: ANSI_FORMAT NOANSI_FORMAT Specifies whether the SQL precompiler accepts terminal-format COBOL or ANSI-format COBOL. The default is the terminal format COBOL NOANSI_FORMAT qualifier. 4 C_STRING Syntax options: SQLOPTIONS= (C_STRING=[NO]BLANK_FILL) SQLOPTIONS= (C_STRING=[NO]FIXED_CDD_STRINGS) SQLOPTIONS= (C_STRING=([NO]BLANK_FILL, [NO]FIXED_CDD_STRINGS)) Specifies how to handle C host language character strings: o [NO]BLANK_FILL (default: BLANK_FILL) Controls whether or not C character strings are filled with blanks as required by the SQL89 and ANSI/ISO SQL standards or if the null terminator is placed after the last data byte of the source string. o [NO]FIXED_CDD_STRINGS (default: NOFIXED_CDD_STRINGS) Controls whether or not SQL treats C character strings from CDD/Repository record definitions as fixed-length character strings or C null-terminated strings. 4 c-string-options Controls how SQL handles C host language character strings. Use either or both of the [NO]BLANK_FILL and [NO]FIXED_CDD_ STRINGS options with the C_STRING keyword to control C string characteristics. 4 CONNECT Syntax options: SQLOPTIONS= (CONNECT) SQLOPTIONS= (NOCONNECT) Specifies whether or not SQL allows multiple user connections and access to global databases across modules. All SQL modules in an application must be compiled with connections enabled or disabled. The SQLOPTIONS=NOCONNECT qualifier is the default. 4 CONSTRAINT_MODE Syntax options: SQLOPTIONS= (CONSTRAINT_MODE=IMMEDIATE) SQLOPTIONS= (CONSTRAINT_MODE=DEFERRED) SQLOPTIONS= (CONSTRAINT_MODE=ON) SQLOPTIONS= (CONSTRAINT_MODE=OFF) You can optionally specify either the SQLOPTIONS=(CONSTRAINT_ MODE=IMMEDIATE) or SQLOPTIONS=(CONSTRAINT_MODE=DEFERRED) qualifier on the SQL precompiler command line to set the default constraint evaluation mode for commit-time constraints. (This qualifier does not affect the evaluation of verb-time constraints.) The default is DEFERRED; that is, commit-time constraints are evaluated at commit time. Setting constraints IMMEDIATE causes each affected constraint to be evaluated immediately, as well as at the end of each statement, until the SET ALL CONSTRAINTS DEFERRED statement is issued, or until the transaction completes with a commit or rollback operation. The SET ALL CONSTRAINTS statement overrides the constraint evaluation mode specified in the SQLOPTIONS qualifier. For more information about the default constraint mode, see the Oracle Rdb7 SQL Reference Manual. SQL users who require ANSI-standard SQL compatibility should set constraints as IMMEDIATE. The default (CONSTRAINT_MODE=DEFERRED) is acceptable for most other users. The qualifiers CONSTRAINT_MODE=ON and CONSTRAINT_MODE=OFF duplicate the behavior of the qualifiers CONSTRAINT_ MODE=IMMEDIATE and CONSTRAINT_MODE=DEFERRED, respectively. 4 context-file-name An SQL command procedure containing DECLARE statements that you want to apply when your program compiles and executes. See the Oracle Rdb7 SQL Reference Manual for information about context- file-name. 4 database-options Specifies that the SQL precompiler correctly processes a program for access to the specified database type. For more information regarding database options, see the Oracle Rdb7 SQL Reference Manual. The precompiler database option can in turn be overridden by an attach to a database at run time. On the DECLARE statement, SQL sets the database options of the specified database. By default, the SQL precompiler determines the valid database from the database used to compile the program. If no database is used to compile the program, the precompiler processes the program for a database created with the most recent version of Oracle Rdb. 4 DECLARE_MESSAGE_VECTOR Syntax options: SQLOPTIONS= (DECLARE_MESSAGE_VECTOR) SQLOPTIONS= (NODECLARE_MESSAGE_VECTOR) Specifies that the RDB$MESSAGE_VECTOR structure be declared in the host language as part of the SQLCA during SQLPRE processing. You can use this switch with language compilers that support the '$' special character. The default is the SQLOPTIONS=(DECLARE_MESSAGE_VECTOR) qualifier. 4 EXTEND_SOURCE Syntax options: EXTEND_SOURCE NOEXTEND_SOURCE Allows the SQL precompiler to view 132 columns of FORTRAN source rather than the default of 72 columns. 4 EXTERNAL_GLOBALS Syntax options: SQLOPTIONS= (EXTERNAL_GLOBALS) SQLOPTIONS= (NOEXTERNAL_GLOBALS) Specifies whether or not alias references are coerced into alias definitions. An alias definition is an alias declared with the GLOBAL keyword (the default) in the DECLARE ALIAS statement. An alias reference is an alias declared with the EXTERNAL keyword in the DECLARE ALIAS statement. The EXTERNAL_GLOBALS qualifier treats alias references as alias definitions. This qualifier provides compatibility with versions prior to V7.0. The NOEXTERNAL_GLOBALS qualifier treats alias references as alias references. The NOEXTERNAL_GLOBALS qualifier may be useful on OpenVMS if your application shares an alias between multiple shareable images. The default on OpenVMS is the SQLOPTIONS=(EXTERNAL_GLOBALS) qualifier. For information on using aliases and shareable images, see the Oracle Rdb7 Guide to SQL Programming. 4 FLAG_NONSTANDARD Syntax options: SQLOPTIONS= (FLAG_NONSTANDARD) SQLOPTIONS= (FLAG_NONSTANDARD=SQL92_ENTRY) SQLOPTIONS= (FLAG_NONSTANDARD=SQL89) SQLOPTIONS= (FLAG_NONSTANDARD=MIA) SQLOPTIONS= (NOFLAG_NONSTANDARD) Specifies whether or not SQL identifies nonstandard syntax. Nonstandard syntax, called an extension, refers to syntax that is not part of the ANSI/ISO SQL standard or the Multivendor Integration Architecture (MIA) standard. You can specify the following options: o (FLAG_NONSTANDARD) Notifies you of syntax that is an extension to the ANSI/ISO SQL standard. o (FLAG_NONSTANDARD=SQL92_ENTRY) Notifies you of syntax that is an extension to the ANSI/ISO SQL standard. This qualifier has the same effect on flagging as does the (FLAG_NONSTANDARD) qualifier. o (FLAG_NONSTANDARD=SQL89) Notifies you of syntax that is an extension to the ANSI/ISO 1989 standard. o (FLAG_NONSTANDARD=MIA) Notifies you of syntax that is an extension to the MIA standard. o (NOFLAG_NONSTANDARD) Prevents notification of extensions. The default is the SQLOPTIONS=(NOFLAG_NONSTANDARD) qualifier. 4 G_FLOAT Syntax options: G_FLOAT NOG_FLOAT Specifies whether the SQL precompiler assigns a G-floating or D-floating interpretation to the DOUBLE data type in a formal parameter list. SQL always receives values from and passes values to the database in G-floating format. Oracle Rdb database systems do not support the D-floating interpretation of double-precision, floating-point numbers. Therefore, if you specify the NOG_FLOAT qualifier, you are asking SQL to convert double-precision, floating-point values between the host language module and the database. You can specify the G_FLOAT qualifier for any language except Ada and COBOL. The G_FLOAT qualifier is the default for all languages except COBOL. 4 INITIALIZE_HANDLES Syntax options: SQLOPTIONS= (INITIALIZE_HANDLES) SQLOPTIONS= (NOINITIALIZE_HANDLES) Specifies whether or not alias definitions are coerced into alias references. The NOINITIALIZE_HANDLES qualifier causes all alias declarations to be treated as alias references. An alias definition is an alias declared with the GLOBAL keyword (the default) in the DECLARE ALIAS statement. An alias reference is an alias declared with the EXTERNAL keyword in the DECLARE ALIAS statement. The NOINITIALIZE_HANDLES qualifier may be useful for existing source code on OpenVMS in coercing alias definitions into alias references. Because there is usually no distinction between a definition and a reference on OpenVMS, your application might declare an alias definition where an alias reference is needed. If you reorganize your application into multiple images that share aliases, you must distinguish the alias definition from the alias reference. In this case, use the NOINITIALIZE_HANDLES qualifier to coerce a definition into a reference without changing your source code. If your application correctly declares alias references with the EXTERNAL keyword, use the NOEXTERNAL_GLOBALS qualifier, instead of [NO]INITIALIZE_HANDLES to override the default on OpenVMS and cause SQL to treat alias references properly as references. The default is the SQLOPTIONS=INITIALIZE_HANDLES qualifier. This qualifier overrides the [NO]EXTERNAL_GLOBALS qualifier. The SQLOPTIONS=[NO]INITIALIZE_HANDLES qualifier is maintained for compatibility with previous versions of Oracle Rdb. For V7.0 and higher, use the [NO]EXTERNAL_GLOBALS qualifier, which provides more precise control over alias definition. For information on using aliases and shareable images, see the Oracle Rdb7 Guide to SQL Programming. 4 LIST Syntax options: LIST NOLIST With Oracle Rdb for OpenVMS Alpha, determines whether or not the SQL precompiler generates a list file (default file extension .lis) that contains information about the SQL compilation and the host language compilation. In addition, if the logical name SQL$KEEP_PREP_FILES is defined, the SQL precompiler retains an intermediate module list file (file extension .mli), which contains information about the SQL compilation only. The NOLIST qualifier is the default. 4 MACHINE_CODE Syntax options: MACHINE_CODE NOMACHINE_CODE With Oracle Rdb for OpenVMS Alpha, specifies whether or not the SQL precompiler includes machine code in the list file; however, to generate the list file with the machine code in it, you must also specify the LIST qualifier. The NOMACHINE_CODE qualifier is the default. 4 OBJECT Syntax options: OBJECT NOOBJECT Specifies whether or not the SQL precompiler creates an object file when compiling the source file if the compilation does not generate fatal errors; and, if an object file is produced, what the file is named. If you specify the OBJECT qualifier and do not include a file specification, the precompiler creates an object file with the same file name as the source file and the file extension .obj. You can specify the OBJECT qualifier for any language except Ada. The OBJECT qualifier is the default. 4 OPTIMIZATION_LEVEL SQLOPTIONS= (OPTIMIZATION_LEVEL=DEFAULT) SQLOPTIONS= (OPTIMIZATION_LEVEL=FAST_FIRST) SQLOPTIONS= (OPTIMIZATION_LEVEL=TOTAL_TIME) When you specify an optimizer strategy, select the: o DEFAULT option if you can accept either optimizer strategy. o FAST_FIRST option if you want your program to return data to the user as quickly as possible, even at the expense of total throughput. o TOTAL_TIME option if you want your program to run at the fastest possible rate, returning all the data as quickly as possible. If your application runs in batch, accesses all the records in a query, and performs updates or writes reports, you should specify TOTAL_TIME. You affect the optimizer strategy of static SQL queries with the optimization level qualifier; however, the default optimizer strategy set by the OPTIMIZATION_LEVEL option can be overriden by the default optimizer strategy set in a top-level SELECT statement. In contrast, the SET OPTIMIZATION LEVEL statement specifies the query optimization level for dynamic SQL query compilation only; the statement does not affect the SQL compile-time environment nor does it affect the run-time environment of static queries. 4 PASSWORD_DEFAULT Syntax options: SQLOPTIONS= (PASSWORD_DEFAULT=password) Specifies the user's password at compile time. If you use the USING DEFAULT clause of the DECLARE ALIAS statement, you use this qualifier to pass the compile-time user's password to the program. 4 pre-host-file-spec The file specification for a host language source file that contains embedded SQL statements. The default file extension for the source file depends on the host language specified in the language qualifier. Language Default File Extension Ada .sqlada C .sc COBOL .sco FORTRAN .sfo Pascal .spa PL/I .spl If the host language is Ada or COBOL, the file name (without the file extension) cannot be longer than 27 characters. The precompiler command line allows a list of host language source files in this argument, but only processes the first file specification it encounters. If you specify a list of files, the precompiler: o Gives a warning message that only the first file on the line will be precompiled o Ignores the other file specifications and passes them along to the host language compiler in the same order as they appeared on the precompiler command line For instance, the following command lines are valid, but only the MY_FILE host language file is precompiled: $ SQLPRE/PLI/LIS/DEB MY_FILE+MY_TLB_1/LIB+MY_TLB_2/LIB $ SQLPRE/PASCAL MY_FILE,MY_OTHER_FILE $ SQLPRE/COB/DEB MY_FILE,MY_NODB_FILE $ SQLPRE/CC MY_FILE+REST_OF_APPL+APPL_TLB/LIB For the previous command lines, the precompiler passes the following corresponding command lines to the host language compiler: $ PLI/LIS/DEB MY_FILE.PLI;n+MY_TLB_1/LIB+MY_TLB_2/LIB/NOG_FLOAT $ PAS MY_FILE.PAS;n,MY_OTHER_FILE $ COB/DEB MY_FILE.COB;n,MY_NODB_FILE $ CC MY_FILE.C;n+REST_OF_APPL+APPL_TLB/LIB/NOG_FLOAT The ;n notation signifies the version number of the host language file generated by the SQL precompiler. 4 pre-lang-qualifiers Refers to the host language in which the program containing embedded SQL procedures is written. You must supply a language qualifier. The host language qualifier values are ADA, CC, CC=VAXC, CC=DECC, COBOL, FORTRAN, PASCAL, and PLI. The following statements apply to the CC SQL precompiler switch: o The CC=VAXC switch instructs the precompiler to compile the source as a VAXC source. If the VAXC compiler is not installed, the DECC compiler is used with the /STANDARD=VAXC host language compiler switch. o The CC=DECC switch instructs the precompiler to compile the source as a DECC source. If the DECC compiler is not installed, you will get a DCL error. o The default keyword, either VAXC or DECC, is based on your system configuration. If the VAXC compiler is installed on your system, VAXC is the default keyword. If the DECC compiler is installed, DECC is the default keyword. If both compilers are installed, the default is based on whichever C compiler your system manager has specified. 4 pre-qualifiers Refers to the optional qualifiers allowed on the SQL precompiler command line. 4 QUERY_CPU_TIME_LIMIT Syntax options: SQLOPTIONS= (QUERY_CPU_TIME_LIMIT=total-seconds) Limits the amount of CPU time used to optimize a query for execution. If the query is not optimized and prepared for execution before the CPU time limit is reached, an error message is returned. The default is unlimited time for the query to compile. Dynamic SQL options are inherited from the compilation qualifier. 4 QUERY_ESTIMATES Syntax options: SQLOPTIONS= (QUERY_ESTIMATES) SQLOPTIONS= (NOQUERY_ESTIMATES) Specifies whether or not SQL returns the estimated number of rows and estimated number of disk I/O operations in the SQLCA structure. If you specify the QUERY_ESTIMATES keyword, SQL returns the estimated number of rows in the field SQLCA.SQLERRD[2] and the estimated number of disk I/O operations in the field SQLCA.SQLERRD[3]. The value of SQLCA.SQLERRD[2] and SQLCA.SQLERRD[3] is normally 0 after you execute an OPEN statement for a table. The SQLOPTIONS=QUERY_ESTIMATES qualifier is the default. 4 QUERY_MAX_ROWS Syntax options: SQLOPTIONS= (QUERY_MAX_ROWS=total-rows) Limits the number of records returned during query processing by counting the number of rows returned by the query and returning an error message if the query exceeds the total number of rows specified. The default is an unlimited number of record fetches. Dynamic SQL options are inherited from the compilation qualifier. 4 QUERY_TIME_LIMIT Syntax options: SQLOPTIONS= (QUERY_TIME_LIMIT=total-seconds) Limits the number of records returned during query processing by counting the number of seconds used to process the query and returning an error message if the query exceeds the total number of seconds specified. The default is unlimited time for the query to compile. Dynamic SQL options are inherited from the compilation qualifier. 4 ROLLBACK_ON_EXIT Syntax options: SQLOPTIONS= (ROLLBACK_ON_EXIT) Rolls back outstanding transactions when a program exits from SQL. On OpenVMS, outstanding transactions are committed when a program exits from SQL by default. Therefore, if you want to roll back changes, specify this qualifier on the command line. 4 TRANSACTION_DEFAULT Syntax options: SQLOPTIONS= (TRANSACTION_DEFAULT = IMPLICIT) SQLOPTIONS= (TRANSACTION_DEFAULT = DISTRIBUTED) SQLOPTIONS= (NOTRANSACTION_DEFAULT) Specifies when SQL starts a transaction and how SQL handles default distributed transactions. You can specify the following options: o SQLOPTIONS = (TRANSACTION_DEFAULT = IMPLICIT) Causes SQL to start a transaction when you issue either a SET TRANSACTION statement or the first executable SQL statement in a session. o SQLOPTIONS = (TRANSACTION_DEFAULT = DISTRIBUTED) Causes SQL to use the distributed transaction identifier (TID) for the default distributed transaction established by the DECdtm system service SYS$START_TRANS. Using this option eliminates the need to declare context structures in SQL precompiled programs and to use the USING CONTEXT clause in embedded SQL statements. Because it closes all cursors, it also eliminates the need to call the SQL_CLOSE_CURSORS routine. You must explicitly call the DECdtm system services when you use this option. This option provides support for the Structured Transaction Definition Language (STDL) of the Multivendor Integration Architecture (MIA) standard. If you specify the USING CONTEXT clause in embedded SQL statements, you must declare a context structure. o SQLOPTIONS=(NOTRANSACTION_DEFAULT) Causes SQL not to start a transaction unless you execute a SET TRANSACTION statement. If you use this qualifier and issue an executable statement without first issuing a SET TRANSACTION statement, SQL returns an error. The default is SQLOPTIONS = (TRANSACTION_DEFAULT = IMPLICIT). 4 USER_DEFAULT Syntax options: SQLOPTIONS= (USER_DEFAULT=username) Specifies the user name at compile time. If you use the USER DEFAULT clause of the DECLARE ALIAS statement, you use this qualifier to pass the compile-time user name to the program. 4 WARN Syntax options: SQLOPTIONS= (WARN) SQLOPTIONS= (NOWARN) Specifies whether or not the SQL precompiler writes informational and warning messages to the preprocessed host language source file and to SYS$ERROR and SYS$OUTPUT (if different from SYS$ERROR). You can specify two warning options with the WARN qualifier to customize message output. The SQLOPTIONS=WARN qualifier is equivalent to SQLOPTIONS=WARN=(WARNING, DEPRECATE) qualifier. The SQLOPTIONS=NOWARN qualifier is equivalent to the SQLOPTIONS=WARN=(NOWARNING, NODEPRECATE) qualifier. The SQLOPTIONS=WARN qualifier is the default. 4 warning-option Specifies which warning messages the SQL precompiler writes to your terminal, a list file, or both. If you specify only a single warning option, you do not need the parentheses. The WARNING and NOWARNING qualifiers specify whether or not the SQL precompiler writes informational and warning messages to your terminal, a list file, or both. The DEPRECATE and NODEPRECATE qualifiers specify whether or not the SQL precompiler writes diagnostic messages about deprecated features. Deprecated features are features that are currently allowed but will not be allowed in future versions of SQL; that is, they will be obsolete. For example, one deprecated feature is the use of obsolete keywords such as VERB_TIME instead of VERB TIME. A complete list of deprecated features appears on line in the interactive SQL Help utility. You can specify the SQLOPTIONS=WARN=WARNING qualifier if you prefer to have all warning messages except those about deprecated features. You can specify the SQLOPTIONS=WARN=(NOWARNING, DEPRECATE) qualifier if you prefer the deprecated feature messages only. 2 Host_Language_Variable_Declarations The SQL precompiler recognizes only a subset of valid host language variable declarations. If you refer to a variable declaration that SQL does not recognize in an embedded SQL statement, the precompiler generates a fatal error when it encounters that reference. Oracle Rdb databases and the various host languages supported by the SQL precompiler do not necessarily support the same set of data types. The precompiler recognizes host language variable declarations that are equivalent to SQL data types plus a subset of other host language variable declarations. o For host language variable declarations of data types that are equivalent to SQL data types, the precompiler passes values directly between the database and the host language variable. o For each host language, the precompiler also supports a limited number of host language variable declarations that do not correspond to SQL data types. SQL converts database values to the host language data type and host language values to the supported data type. SQL makes this conversion only for a subset of valid host language declarations. Precompiler Data Type Mapping shows the date-time data types that the precompiler supplies. Table 4 Precompiler Data Type Mapping Module Language and Interactive SQL Precompiler DATE SQL_DATE DATE_ANSI SQL_DATE_ANSI DATE_VMS SQL_DATE_VMS TIME SQL_TIME TIMESTAMP SQL_TIMESTAMP INTERVAL YEAR SQL_INTERVAL (YEAR) INTERVAL YEAR TO SQL_INTERVAL (YEAR TO MONTH) MONTH INTERVAL MONTH SQL_INTERVAL (MONTH) INTERVAL DAY SQL_INTERVAL (DAY) INTERVAL DAY TO SQL_INTERVAL (DAY TO HOUR) HOUR INTERVAL DAY TO SQL_INTERVAL (DAY TO MINUTE) MINUTE INTERVAL DAY TO SQL_INTERVAL (DAY TO SECOND) SECOND INTERVAL HOUR SQL_INTERVAL (HOUR) INTERVAL HOUR TO SQL_INTERVAL (HOUR TO MINUTE) MINUTE INTERVAL HOUR TO SQL_INTERVAL (HOUR TO SECOND) SECOND INTERVAL MINUTE SQL_INTERVAL (MINUTE) INTERVAL MINUTE SQL_INTERVAL (MINUTE TO SECOND) TO SECOND INTERVAL SECOND SQL_INTERVAL (SECOND) o For all other host language variable declarations, the precompiler generates an error when it encounters a reference to them in embedded SQL statements. NOTE The ANSI/ISO SQL standard specifies that variables used in embedded SQL statements must be declared within a pair of embedded SQL BEGIN DECLARE . . . END DECLARE statements. The Oracle Rdb SQL precompiler does not enforce this restriction. If you use the BEGIN DECLARE . . . END DECLARE statements, SQL generates a warning message when it encounters a variable declared outside of a BEGIN DECLARE . . . END DECLARE block. If ANSI/ISO SQL compliance is important for your application, you should include all declarations for variables used in embedded SQL statements within a BEGIN DECLARE . . . END DECLARE block. See the Oracle Rdb7 SQL Reference Manual on the SQL module language for more information on the BEGIN DECLARE statement. If you do not declare character variables using syntax that specifies a character set or by defining the RDB$CHARACTER_SET logical name, the SQL precompiler uses the UNSPECIFIED character set. When you use the UNSPECIFIED character set, the precompiler does not check to see if the character set of the variables matches the character sets of the database. For more information regarding the logical name, see the Oracle Rdb7 SQL Reference Manual. The RDB$CHARACTER_SET logical name is deprecated and will not be supported in a future release. 3 Character_Parameters To ensure that you specify the length of character variables correctly, use the following guidelines: o For the C language, any character variables that correspond to character data type columns must be defined as the length of the longest valid column value in octets, plus 1 octet to allow for the null terminator. o For other languages supported by the SQL precompiler, any character variables that correspond to character data type columns must be defined as the length of the longest valid column value in octets. o When calculating the length of the longest valid column value, you must take into consideration whether the SQL precompiler interprets the length of columns in characters or octets. A program can control how the SQL precompiler interprets the length of columns in the following ways: - The CHARACTER LENGTH clause of the DECLARE MODULE statement - The DIALECT clause of the DECLARE MODULE statement - For dynamic SQL, the SET CHARACTER LENGTH statement 3 Ada_Variables Ada is available only on the OpenVMS platforms. SQL lets you declare host language variables directly or by calling the Ada package, SQL_STANDARD. You must use the SQL_STANDARD package if you want to conform to the ANSI/ISO SQL standard. This package defines the data types that are supported by the ANSI/ISO SQL standard. To use the package, first copy the file SYS$COMMON:[SYSLIB]SQL$STANDARD.ADA to your own Ada library, and then compile the package. The package SQL_STANDARD declares the following ANSI-standard data types: o CHAR o SMALLINT The data type SMALLINT contains one subtype: INDICATOR_TYPE. o INT o REAL o DOUBLE_PRECISION o SQLCODE_TYPE The data type SQLCODE_TYPE contains two subtypes: NOT_FOUND and SQL_ERROR. o SQLSTATE_TYPE If ANSI/ISO SQL compliance is not important for your application, you can declare host language variables directly. The following list describes the variable declaration syntax that the SQL precompiler supports in Ada: o Standard package data types - STRING - CHARACTER - SHORT_SHORT_INTEGER - SHORT_INTEGER - INTEGER - FLOAT - LONG_FLOAT By default, Ada recognizes the LONG_FLOAT data type as a G-floating representation of floating-point data. However, Ada also allows you to override the default and specify that LONG_FLOAT denotes D-floating representation by using the LONG_FLOAT(D_FLOAT) pragma or using ACS CREATE LIBRARY or SET PRAGMA commands. SQL does not recognize whether or not you override the G-floating default for the LONG_FLOAT data type. If you do override the LONG_FLOAT default, you will get Ada compile-time errors. To avoid problems with the ambiguity in the LONG_FLOAT data type, use the SYSTEM package G_FLOAT and D_FLOAT data types. o Date-time data types The precompiler translates lines in a precompiled program that contain any of the date-time data types. NOTE Oracle Rdb reserves the right to change the code generated in translation of date-time data types at any time, without prior notice. - SQL_DATE, SQL_DATE_ANSI, SQL_DATE_VMS - SQL_TIME, SQL_TIMESTAMP - SQL_INTERVAL (DAY TO SECOND) Use this data type for variables that represent the difference between two dates or times. (Precompiler Data Type Mapping lists all the supported INTERVAL data types.) o SQL definition package The precompiler generates a package that includes definitions for the following data types if Ada object declarations refer to them: - SQL_VARCHAR_n Use this data type for variables that correspond to VARCHAR and LONG VARCHAR columns in a database, where n is the length specified in the definition of the columns (always 16,383 characters for LONG VARCHAR columns). SQL declares a two-field Ada record when it encounters SQL_VARCHAR_n, with one field, t, containing the character string, and the second field, l, containing an integer denoting the length of the string. You can refer to the l field to determine the actual length of a varying character string, and refer to the t field to refer to the string itself. This excerpt from the online sample program sql_all_datatypes.sqlada refers to the l field to see if the value in an SQL_VARCHAR_n field is null. . . . -- Variables for main program use type ALL_DATATYPES_RECORD_TYPE IS record . . . VARCHAR_VAR : sql_varchar_40; end record; . . . -- The following if statements evaluate the contents of main variables -- and then set indicators as appropriate. . . . if all_datatypes_record.varchar_var.l = 0 then indicator_group(7) := -1; end if; - SQLDA_ACCESS Specifying this data type declares an SQLDA structure. It offers an advantage over an embedded INCLUDE SQLDA statement because you can use it in more than one declaration to declare multiple SQLDA structures. o CDD_TYPES package data types (must specify WITH CDD_TYPES) - DATE_TIME_DATATYPE (Oracle Rdb recommends that you use SQL_TIMESTAMP) - SHORT_INTEGER_ARRAY (for indicator arrays only) o SYSTEM package data types (must specify WITH SYSTEM) - D_FLOAT - G_FLOAT - F_FLOAT o Arrays Single-dimension arrays are supported to declare an indicator array to refer to a structure in SQL statements. The elements of the array must be declared as word integers (SHORT_ INTEGER). Character arrays are supported as types or subtypes but cannot refer to derived types. SQL does not allow references to unconstrained arrays. o Types The precompiler recognizes types for all the preceding data types plus records, derived types, and arrays. - Records can refer to any recognized type. - Derived types (NEW keyword) can refer to any recognized type. SQL allows but ignores range constraints in derived types. SQL does not allow references to types that use discriminants in any way or to access types. SQL does not allow references to integer (RANGE keyword), floating-point (DIGITS keyword), or fixed-point (DELTA keyword) types. o Subtypes Subtypes can refer to any recognized type. SQL allows but ignores range constraints in subtypes. o Assignments from expressions in declarations o Context structure types When you write applications for the Ada precompiler, you should declare a context structure by declaring a variable of data type SQLCONTEXT_REC instead of declaring a structure. When you declare a variable with the data type SQLCONTEXT_REC, the Ada precompiler generates a context structure for you. For example, you declare the variable using the following code: context_struc.sqlcontext_ver := 1; context_struc.sqlcontext_tid.sqlcontext_tid_type := 1 context_struc.sqlcontext_tid.sqlcontext_tid_len := 16; context_struc.sqlcontext_tid.sqlcontext_tid_value(1) := 0; context_struc.sqlcontext_tid.sqlcontext_tid_value(2) := 0; context_struc.sqlcontext_tid.sqlcontext_tid_value(3) := 0; context_struc.sqlcontext_tid.sqlcontext_tid_value(4) := 0; context_struc.sqlcontext_end := 0; 3 C_Variables NOTE C is a case-sensitive language. The names of objects declared in a C program are case sensitive, but the names of SQL tables and other names are not case sensitive. Therefore, you must be careful about C constructs that you specify in SQL statements. These constructs include variable names and labels of program sections. See the Oracle Rdb7 Guide to SQL Programming for more information about declaring C variables. The following list describes the variable declaration syntax for character data types that the SQL precompiler supports in C: o char x (but not char *x or char x[n]) o char CHARACTER SET character-set-name clause The CHARACTER SET character-set-name clause is optional. o $SQL_VARCHAR (n) o $SQL_VARCHAR (n) CHARACTER SET character-set-name The CHARACTER SET clause is optional. For information about the supported character sets, see the Oracle Rdb7 SQL Reference Manual. The following list describes the variable declaration syntax that the SQL precompiler supports in C: o Data type keywords - char x (but not char *x or char x[n]) - char - $SQL_VARCHAR (n) - short, short int, int, long, long int (However, you cannot specify these as unsigned.) - SQL_DATE, SQL_DATE_ANSI, SQL_DATE_VMS - SQL_TIME, SQL_TIMESTAMP - SQL_INTERVAL (DAY TO SECOND) Use this data type for variables that represent the difference between two dates or times. (Precompiler Data Type Mapping lists all the supported INTERVAL data types.) - float, double - User-defined types o Storage class identifiers and modifiers o struct o union o type def o Initial value assignments o Arrays Only single-dimension arrays are supported and only to declare an indicator array for use with a reference to a structure in SQL statements. Furthermore, the size of the array must be specified explicitly. Although you can use any data type for indicator array elements, Oracle Rdb recommends that you use variables of the INTEGER data type. o Pointers Only a single level of pointer variables are supported and only those that point to elementary data types. Because C pointer variables cannot specify length attributes, SQL sometimes must allocate the largest possible piece of memory to process statements that refer to char pointer variables. SQL cannot determine the length of char pointer variables and allocates 16,383 bytes of memory for each variable in the following cases: - The SQL statement contains a concatenated value expression or a substring. - The SQL statement refers to the char pointer variable in a predicate, such as WHERE EMP_ID = :POINTER_VAR. - The SQL statement converts the contents of the char pointer variable to a numeric data type in the database. Avoid the use of char pointer variables in these cases because such a large memory allocation for char pointer variables wastes memory and degrades performance, especially for remote database access. o Valid declaration syntax The following are examples of valid declaration syntax: a_var[10]; $SQL_VARCHAR(10) x,y,z; int SQLCODE; struct { char b_var[5]; short int c_var; } a_record; union { char string_date[17]; struct { char year_var1[2]; char year_var2[2]; char month_var[2]; char day_var[2]; char hour_var[2]; char minute_var[2]; char second_var[2]; char hundredth_var[2]; } date_group; } date_union; int indicator_item[2]; globaldef double c_var; static d_var; char *x; o Invalid declaration syntax 3 COBOL_Variables The following list describes the variable declaration syntax for character data types that the SQL precompiler supports in COBOL: o PICTURE IS can be abbreviated as PICTURE or PIC. o CHARACTER SET character-set-name PICTURE IS. o PICTURE clauses for numeric variables must begin with S (must be signed) and cannot include P characters. o PICTURE clauses cannot include editing characters. For information about the supported character sets, see the Oracle Rdb7 SQL Reference Manual. The following list describes the variable declaration syntax that the SQL precompiler supports in COBOL: o PICTURE IS clause - PICTURE IS can be abbreviated as PICTURE or PIC. - PICTURE clauses for numeric variables must begin with S (must be signed) and cannot include P characters. - PICTURE clauses cannot include editing characters. o USAGE IS clause - USAGE IS must immediately follow a PICTURE clause. - USAGE IS can be abbreviated as USAGE or omitted completely. - USAGE IS must have as an argument BINARY, COMPUTATIONAL, COMPUTATIONAL-1, COMPUTATIONAL-2, or COMPUTATIONAL-3. COMPUTATIONAL can be abbreviated as COMP in all USAGE IS or DISPLAY declarations. BINARY is a synonym for COMPUTATIONAL or COMP. o VALUE IS clause VALUE IS can be abbreviated as VALUE and is allowed without restriction. o IS EXTERNAL clause IS EXTERNAL can be abbreviated as EXTERNAL and is allowed without restriction. o IS GLOBAL clause IS GLOBAL can be abbreviated as GLOBAL and is allowed without restriction. o SIGN clause SIGN is allowed but must immediately follow a PICTURE clause or a USAGE IS clause. o Group data items - Group data items are allowed without restriction. - Variables associated with the SQL VARCHAR and LONG VARCHAR data types must be declared as group data items with two elementary items at level 49. The first elementary item must be a small integer to contain the actual length of the character string. The second elementary item must be a character string long enough to contain the string itself. * Declaration for an SQL column * defined as VARCHAR (80): * 01 VARYING_STRING. 49 STRING_LENGTH PIC S9(4) USAGE IS COMP. 49 STRING_TEXT PIC X(80). o OCCURS n TIMES clause - OCCURS clauses are permitted only for declarations of indicator arrays. Although you can use any data type for indicator array elements, Oracle Rdb recommends that you declare them as integers (PIC S9(9) COMP). - Multidimension tables (nested OCCURS clauses) and variable- occurrence data items (OCCURS DEPENDING ON clause) are not supported. o REDEFINES clauses You can refer to host language variables that have a REDEFINES clause or that are subordinate to a REDEFINES clause. o SQL date-time data types - SQL_DATE, SQL_DATE_ANSI, SQL_DATE_VMS - SQL_TIME, SQL_TIMESTAMP - SQL_INTERVAL (DAY TO SECOND) Use this data type for variables that represent the difference between two dates or times. (Precompiler Data Type Mapping lists all the supported INTERVAL data types.) The precompiler replaces these data types with host language data declarations that are supported in the compilers themselves. 3 FORTRAN_Variables The following list describes the variable declaration syntax for character data types that the SQL precompiler supports in FORTRAN: o CHARACTER o CHARACTER character-set-name For information about the supported character sets, see the Oracle Rdb7 SQL Reference Manual. The following list describes the variable declaration syntax that the SQL precompiler supports in FORTRAN: o Declarations Declarations can include only the following FORTRAN data types: - CHARACTER - LOGICAL, LOGICAL*1, LOGICAL*2, LOGICAL*4 - INTEGER, INTEGER*2, INTEGER*4 - REAL, REAL*4, REAL*8 - DOUBLE PRECISION - SQL_DATE, SQL_DATE_ANSI, SQL_DATE_VMS - SQL_TIME, SQL_TIMESTAMP - SQL_INTERVAL (DAY TO SECOND) Use this data type for variables that represent the difference between two dates or times. (Precompiler Data Type Mapping lists all the supported INTERVAL data types.) o Initial values assigned in the declaration o STRUCTURE declarations o UNION declarations within structures o RECORD statements o DIMENSION statements - DIMENSION statements are permitted only for declarations of indicator arrays. Although you can use any data type for indicator array elements, Oracle Rdb recommends that you use variables of the INTEGER data type. - Multidimension arrays and dynamic-sized arrays are not supported. Implicit declarations are not supported. SQL generates a "host variable was not declared" error when it encounters an implicitly declared variable in an SQL statement. 3 Pascal_Variables The following list describes the variable declaration syntax that the SQL precompiler supports in Pascal: o Data type keywords Declarations can include only the following Pascal data types: - INTEGER8, INTEGER16, INTEGER32, and INTEGER64 - REAL - SINGLE - DOUBLE - CHAR - PACKED ARRAY [1..n] OF CHAR; - VARYING [u] OF CHAR - [BYTE] -128..127; - [WORD] -32768..32767; - Date-time data types (Precompiler Data Type Mapping lists these data types.) In addition, the SQL Pascal precompiler provides the following data types: - SQL_LONG_VARCHAR - SQL_DATE - SQL_SMALLINT - SQL_INDICATOR - SQL_BIGINT - SQL_QUAD - SQL_DATE, SQL_DATE_ANSI, SQL_DATE_VMS - SQL_TIME, SQL_TIMESTAMP - SQL_INTERVAL (DAY TO SECOND) Use this data type for variables that represent the difference between two dates or times. (Precompiler Data Type Mapping lists all the supported INTERVAL data types.) o Records The SQL precompiler supports Pascal record definitions. It also supports nested records such as the following: type_record_type = record employee_id : employee_id_str; last_name : last_name_str; first_name : first_name_str; middle_init : middle_init_str; address_dat1: address_str; address_dat2: address_str; city : city_str; state : state_str; postal_code : postal_code_str; sex : sex_str; status_code : status_code_str; end; name_rec = record last_name : last_name_str; first_name : first_name_str; middle_init : middle_init_str; end; address_rec = record address_dat1 : address_str; address_dat2 : address_str; city : city_str; state : state_str; postal_code : postal_code_str; end; rec_in_rec = record employee_id : employee_id_str; emp_name : name_rec; emp_addr : address_rec; sex : sex_str; status_code : status_code_str; end; rec_in_rec_in_rec = record nested_again : rec_in_rec; end; A record that is used in an SQL statement cannot contain a pointer to another record. The SQL precompiler does not support variant records. o Initial value assignments The SQL precompiler supports initial values assigned in the declaration: dateind : SQL_INDICATOR:=0; o Arrays Packed arrays are supported to declare SQL character strings. Single-dimension arrays are supported to declare an indicator array to refer to a structure in SQL statements. The elements of the array must be declared as word integers [WORD]- 32768..32767 or SQL_INDICATOR. o Pointers The SQL precompiler for Pascal supports one level of pointers. type a = ^integer; var b : a; (* the use of the variable b is supported *) c : ^a; (* do not use any form of variable c in an SQL statement) NOTE The Pascal precompiler for SQL gives an incorrect %SQL-I- UNMATEND error when it parses a declaration of an array of records. It does not associate the END with the record definition, and the resulting confusion in host variable scoping causes a fatal error. To avoid the problem, declare the record as a type and then define your array of that type. For example: main.spa: program main (input,output); type exec sql include 'bad_def.pin'; !gives error exec sql include 'good_def.pin'; !ok var a : char; begin end. --------------------------------------------------------------- bad_def.pin x_record = record y : char; variable_a: array [1..50] of record a_fld1 : char; b_fld2 : record; t : record v : integer; end; end; end; end; --------------------------------------------------------------- good_def.pin good_rec = record a_fld1 : char; b_fld2 : record t : record v: integer; end; end; end; x_record = record y : char variable_a : array [1..50] of good_rec; end; 3 PLI_Variables PL/I is available only on the OpenVMS platforms. The following list describes the variable declaration syntax that the SQL precompiler supports in PL/I: o Declarations Declarations can include only the following PL/I data types: - CHARACTER CHARACTER can be abbreviated as CHAR. - CHARACTER VARYING CHARACTER VARYING can be abbreviated as CHAR VAR. - Date-time data types (Precompiler Data Type Mapping lists these data types.) - TINYINT TINYINT is FIXED BINARY(7). - FIXED BINARY, FIXED DECIMAL BINARY can be abbreviated as BIN, and DECIMAL can be abbreviated as DEC. Scale factors are not allowed on FIXED BINARY declarations. - FLOAT BINARY, FLOAT DECIMAL - SQL_DATE, SQL_DATE_ANSI, SQL_DATE_VMS - SQL_TIME, SQL_TIMESTAMP - SQL_INTERVAL (DAY TO SECOND) Use this data type for variables that represent the difference between two dates or times. (Precompiler Data Type Mapping lists all the supported INTERVAL data types.) o Storage class attributes Any of the storage class attributes (BASED, AUTOMATIC, DEFINED, STATIC, variable, EXTERNAL, and INTERNAL) is allowed. The BASED attribute declarations must include a location reference. o INITIAL attribute o Structures Structures are allowed without restriction. o Arrays Arrays are permitted only for declarations of indicator arrays. Although you can use any data type for indicator array elements, Oracle Rdb recommends that you declare them as INTEGER variables. Multidimension array items are not supported. Arrays of structures are not supported. Arrays that are in a group that is itself an array are not supported. Dynamic-sized arrays are not supported.