1 SQL72 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:RDB072.RELEASE_NOTES 2 System_Tables Oracle Rdb stores information about the database as a set of tables called system tables. The system tables 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 table and the fields that can index that table. The definitions of most system tables are standard and are likely to remain constant in future versions of Oracle Rdb. Under each Help topic for a particular system table, 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 tables with respect to particular versions of Oracle Rdb or in table to other database constructs, operations, or products. 4 Using_Data_Dictionary Although you can store your data definitions in the data dictionary, the database system refers only to the system tables in the database file itself for these definitions. In a sense, the system tables 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 tables. As the user performs data definition operations on the database, Oracle Rdb reads and modifies the system tables to reflect those operations. You should not modify any of the Oracle Rdb system tables using data manipulation language, nor should you define any domains based on system table fields. However, you can use regular Oracle Rdb data manipulation statements to retrieve the contents of the system tables. This means that your program can determine the structure and characteristics of the database by retrieving the fields of the system tables. See the Example Help subtopic under System_ Tables in this Help library for an example of querying system tables. 4 Updating_Metadata When you use the SQL SET TRANSACTION . . . RESERVING statement to lock a set of tables for an Oracle Rdb operation, you normally exclude from the transaction all the tables not listed in the RESERVING clause. However, Oracle Rdb accesses and updates system tables as necessary, no matter which tables you have locked with the SQL SET TRANSACTION statement. When your transaction updates database metadata, Oracle Rdb reserves the system tables involved in the update in the EXCLUSIVE share mode. Other users are unable to perform data definition operations on these tables 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 table, RDB$RELATION_FIELDS. No other users can refer to the same domain until you commit your transaction. o When you change a system table or domain definition, Oracle Rdb locks an index in the system table, RDB$FIELD_VERSIONS. No other users can change table or global field definitions until you commit your transaction. o When you change a table definition, Oracle Rdb locks an index in the system table, RDB$RELATION_FIELDS. No other users can change tables in the same index node until you commit your transaction. 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. An SQL CREATE TRIGGER or CREATE MODULE statement could fail due to this restriction. This limit was 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 (using the RMU Convert command, RMU Restore command, or SQL EXPORT/IMPORT statements) 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 values 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 is a list of fields of various system tables that are set to read-only access. o RDB$ACCESS_CONTROL o RDB$FLAGS o RDB$MODULE_OWNER o RDB$ROUTINE_OWNER 3 Example The following BASIC program uses an SQL Module to query system tables PROGRAM SYSTEM_RELATION ! This BASIC program interactively prompts a user to enter a name ! of a system table (table). Next, the program calls an SQL ! Module which uses a cursor to read the system table that the ! user entered. Upon reading the fields (domains) of the system ! table, the program displays a message to the user as to whether ! the fields in a system table 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 Table ----------------------------------------------------------------- -- 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_Tables The Oracle Rdb system tables are as follows: RDB$CATALOG_SCHEMA Contains the name and definition of each SQL catalog and schema. This table 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 table that participates in a given constraint. RDB$DATABASE Database-specific information. RDB$FIELD_VERSIONS One row for each version of each column definition in the database. RDB$FIELDS Characteristics of each domain in the database. RDB$GRANTED_PROFILES Description of roles and profiles granted to users and other roles. RDB$INDEX_SEGMENTS Columns that make up an index. RDB$INDICES Characteristics of the indexes for each table. 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$OBJECT_SYNONYMS When synonyms are enabled, this system table is created to describe the synonym name, type, and target. 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 objects. RDB$PROFILES Description of any profiles, roles or users in the database. RDB$QUERY_OUTLINES Query outline definitions used by the optimizer to retrieve known query outlines prior to optimization. RDB$RELATION_CONSTRAINTS Lists all table-specific constraints. RDB$RELATION_CONSTRAINT_ Lists the columns that participate FLDS in unique, primary, or foreign key declarations for table-specific constraints. RDB$RELATION_FIELDS Columns defined for each table. RDB$RELATIONS Tables and views in the database. RDB$ROUTINES Description of each function and procedure in the database. The routine may be standalone or part of a module. RDB$SEQUENCES Characteristics of any sequences defined for the database. RDB$STORAGE_MAPS Characteristics of each storage map. RDB$STORAGE_MAP_AREAS Characteristics of each partition of a storage map. RDB$SYNONYMS Connects an object's user-specified name to its internal database name. This table is only present in databases with the SQL multischema feature enabled. RDB$TRIGGERS Definition of a trigger. RDB$VIEW_RELATIONS Interdependencies of tables used in views. RDB$WORKLOAD Collects workload information. 3 RDB$CATALOG_SCHEMA The RDB$CATALOG_SCHEMA system table contains the name and definition of each SQL catalog and schema. This table is present only in databases that have the SQL multischema feature enabled. The following table provides information on the columns of the RDB$CATALOG_SCHEMA system table. Data Column 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 column is always 0. RDB$CATALOG_SCHEMA_NAME char(31) The name of the catalog or schema. RDB$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 list A user-supplied description of byte of the catalog or schema. varying RDB$SCHEMA_AUTH_ID char(31) The authorization identifier of the creator of the schema. 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 SQL ALTER CATALOG or ALTER SCHEMA statement is used (future). RDB$CATALOG_SCHEMA_ char(31) Creator of this schema or CREATOR catalog. 3 RDB$COLLATIONS The RDB$COLLATIONS system table describes the collating sequence to be used in the database. The following table provides information on the columns of the RDB$COLLATIONS system table. Data Column 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 list Internal representation of of byte the collating sequence. varying RDB$DESCRIPTION list A user-supplied description of byte of the collating sequence. varying 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 DEC_MCS collating sequence. RDB$SECURITY_CLASS char(20) Reserved for future use. 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 table contains the name and definition of each constraint. The following table provides information on the columns of the RDB$CONSTRAINTS system table. Data Column Name Type Summary Description RDB$CONSTRAINT_NAME char(31) The system-wide unique name of the constraint. RDB$CONSTRAINT_BLR list The BLR that defines the of byte constraint. varying RDB$CONSTRAINT_SOURCE list The user's source for the of byte constraint. varying RDB$DESCRIPTION list A user-supplied description of byte of this constraint. varying RDB$EVALUATION_TIME integer A value that represents when a constraint is evaluated, as follows: o 0 At commit time (deferred initially deferred). o 1 At verb time (deferrable initially immediate). o 2 At verb time (not deferrable). RDB$EXTENSION_PARAMETERS list Reserved for future use. of byte varying RDB$SECURITY_CLASS char(20) Reserved for future use. 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. RDB$FLAGS integer Flags. 4 RDB$FLAGS Represents flags for RDB$CONSTRAINTS system table. Bit Position Description 0 Currently disabled. 1 Currently enabled without validation. 3 RDB$CONSTRAINT_RELATIONS The RDB$CONSTRAINT_RELATIONS system table lists all tables that participate in a given constraint. The following table provides information on the columns of the RDB$CONSTRAINT_RELATIONS system table. Data Column 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 table involved in the constraint. RDB$FLAGS integer Flags. RDB$CONSTRAINT_CONTEXT integer The context variable of the table involved in the constraint. RDB$SECURITY_CLASS char(20) Reserved for future use. 4 RDB$FLAGS Represents flags for RDB$CONSTRAINT_RELATIONS system table. Bit Position Description 0 Reserved for future use. 1 Reserved for future use. 2 If the constraint is on the specified table. 3 If the constraint evaluates with optimization by dbkey lookup. 4 If the constraint checks for existence. 5 If the constraint checks for uniqueness. 6 If the constraint needs to evaluate on store of specified table row. 7 If the constraint need not evaluate on store of specified table row. 8 If the constraint needs to evaluate on erase of specified table row. 9 If the constraint need not evaluate on erase of specified table row. 3 RDB$DATABASE The RDB$DATABASE system table contains information that pertains to the overall database. This table can contain only one row. The following table provides information on the columns of the RDB$DATABASE system table. Data Column 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 table identifier assigned. Oracle Rdb assigns the next table an ID of MAX_RELATION_ID + 1. RDB$RELATION_ID integer The unique identifier of the RDB$RELATIONS table. If you drop a table, that identifier is not assigned to any other table. 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 column 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 column RDB$RELATION_NAME. RDB$FIELD_ID integer The identifier of the RDB$FIELD_VERSIONS table. RDB$FIELD_REL_FLD_ROOT_ char(8) A pointer (dbkey) to the DBK base of the RDB$VER_REL_ ID_VER_NDX index on columns RDB$RELATION_ID and RDB$VERSION. RDB$INDEX_ID integer The identifier of the RDB$INDICES table. RDB$INDEX_NDX_ROOT_DBK char(8) A pointer (dbkey) to the base of the RDB$NDX_NDX_ NAME_NDX index on column 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 column RDB$RELATION_ID. RDB$INDEX_SEG_ID integer The identifier of the RDB$INDEX_SEGMENTS table. 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 columns RDB$INDEX_NAME and RDB$FIELD_POSITION. RDB$SEGMENTED_STRING_ID integer The logical area ID that contains the segmented strings. RDB$ACCESS_CONTROL list The access control policy of byte for the database. varying RDB$DESCRIPTION list A user-supplied description of byte of the database. varying RDB$DATABASE_PARAMETERS list Reserved for future use. of byte varying RDB$EXTENSION_PARAMETERS list Reserved for future use. of byte varying RDB$FLAGS integer Flags. RDBVMS$MAX_VIEW_ID integer The largest view identifier assigned to the RDB$RELATION_ID column in the RDB$RELATIONS system 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 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. RDBVMS$SECURITY_USERS list An access control list of byte that identifies users varying who will be audited or who will produce alarms for DAC (discretionary access control) events when DACCESS (discretionary access) auditing is enabled for specific database objects. RDB$SECURITY_CLASS char(20) Reserved for future use. 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. RDB$MAX_ROUTINE_ID integer Maintains a count of the modules and routines added to the database. Value is 0 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 SQL ALTER DATABASE statement 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 command with the Areas qualifier 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 SQL EXPORT and IMPORT statements. The following ALTER DATABASE clauses modify the RDB$LAST_ALTERED column in the RDB$DATABASE system table: o CARDINALITY COLLECTION IS {ENABLED | DISABLED} o DICTIONARY IS [NOT] REQUIRED o DICTIONARY IS NOT USED o METADATA CHANGES ARE {ENABLED | DISABLED} o MULTISCHEMA IS {ON | OFF} o SECURITY CHECKING IS EXTERNAL (PERSONA SUPPORT IS {ENABLED | DISABLED}) o SECURITY CHECKING IS INTERNAL (ACCOUNT CHECK IS {ENABLED | DISABLED}) o SYNONYMS ARE ENABLED o WORKLOAD COLLECTION IS {ENABLED | DISABLED} The following SQL statements modify the RDB$LAST_ALTERED column in the RDB$DATABASE system table: o GRANT statement o REVOKE statement o COMMENT ON DATABASE statement 4 RDB$FLAGS Represents flags for RDB$DATABASE system table. Bit Position Description 0 If dictionary required. 1 If ANSI protection used. 2 If database file is a CDD$DATABASE database. 3 Reserved for future use. 4 Reserved for future use. 5 Reserved for future use. 6 Multischema is enabled. 7 Reserved for future use. 8 System indexes use run length compression. 9 The optimizer saves workload in RDB$WORKLOAD system table. 10 The optimizer is not updating table and index cardinalities. 11 All metadata changes are disabled. 12 Oracle Rdb uses database for user and role names. 13 If security is internal, validate the UIC. If security is external then this indicates that persona support is enabled. 14 Synonyms are supported. 15 Prefix cardinalities are not collected for system indexes. 16 If collecting, use full algorithm for system indexes. 17 Use sorted ranked index for system indexes. 3 RDB$FIELD_VERSIONS The RDB$FIELD_VERSIONS system table is an Oracle Rdb extension. This table contains one row for each version of each column definition in the database. The following table provides information on the columns of the RDB$FIELD_VERSIONS system table. Data Column Name Type Summary Description RDB$RELATION_ID integer The identifier for a table within the database. RDB$FIELD_ID integer An identifier used internally to name the column represented by this row. RDB$FIELD_NAME char(31) The name of the column. RDB$VERSION integer The version number for the table definition to which this column belongs. RDB$FIELD_TYPE integer The data type of the column represented by this row. 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 column represented by this row. 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 column from the beginning of the row. RDB$FIELD_SCALE integer For numeric data types, the scale factor to be applied when interpreting the contents of the column represented by this row. 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 non- numeric data types, RDB$FIELD_SCALE is 0. RDB$FLAGS integer Flags. RDB$VALIDATION_BLR list The BLR that represents the of byte SQL check constraint clause varying defined in this version of the column. RDB$COMPUTED_BLR list The BLR that represents the of byte SQL clause, COMPUTED BY, varying defined in this version of the column. RDB$MISSING_VALUE list The BLR that represents the of byte SQL clause, MISSING_VALUE, varying defined in this version of the column. 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 column. RDB$ACCESS_CONTROL list The access control list for of byte the column. varying RDB$DEFAULT_VALUE2 list The SQL default value. of byte varying 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) Reserved for future use. 4 RDB$FLAGS Represents flags for RDB$FIELD_VERSIONS system table. Bit Position Description 0 Not used. 1 Not used. 2 Not used. 3 Used by Oracle Rdb internally. 4 Set if column references a local temporary table (usually a COMPUTED BY column). 5 Use SQL semantics for check constraint processing. 6 AUTOMATIC set on insert. 7 AUTOMATIC set on update. 8 If check constraint fails, use name in message. 9 Column is computed by an IDENTITY sequence. 10 View column is based on a read-only, or dbkey column. 3 RDB$PARAMETER_SUB_TYPE For details, see the Help topic RDB$FIELD_SUB_TYPE. 3 RDB$FIELD_SUB_TYPE The following table lists the values for the RDB$FIELD_SUB_TYPE and the RDB$PARAMETER_SUB_TYPE columns. 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 DEC_MCS character set. Greater than 0 Character set other than ASCII or DEC_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 (query) type. Equal to 2 Character type. Equal to 3 MBLR (definition) type. Equal to 4 Binary type. Equal to 5 OBLR (outline) type. Greater than 5 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 table describes the global (generic) characteristics of each domain in the database. There is one row for each domain in the database. The following table provides information on the columns of the RDB$FIELDS system table. Data Column Name Type Summary Description RDB$FIELD_NAME char(31) The name of the domain represented by this row. Each row within RDB$FIELDS must have a unique RDB$FIELD_NAME value. RDB$FIELD_TYPE integer The data type of the domain represented by this row. 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 row. 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 row. 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 non-numeric 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, this is a user-defined domain. o If Bit 0 is set, this is a system domain. RDB$VALIDATION_BLR list The BLR that represents the of byte validation expression to be varying checked each time a column based on this domain is updated. RDB$COMPUTED_BLR list The BLR that represents of byte the expression used to varying calculate a value for the column based on this domain. RDB$EDIT_STRING varchar(25The edit string used by interactive SQL when printing the column based on this domain. RDB$EDIT_ STRING can be null. RDB$MISSING_VALUE list The value used when the of byte missing value of the column varying based on this domain is retrieved or displayed. RDB$MISSING_VALUE does not store any value in a column; instead, it flags the column value as missing. RDB$FIELD_SUB_TYPE integer A value that describes the data subtype of RDB$FIELD_ TYPE as shown in the RDB$FIELD_SUB_TYPE Help topic. RDB$DESCRIPTION list A user-supplied description of byte of this domain. varying RDB$VALIDATION_SOURCE list The user's source text for of byte the validation criteria. varying RDB$COMPUTED_SOURCE list The user's source used of byte to calculate a value at varying execution time. RDB$QUERY_NAME char(31) The query name of this domain. Column attributes in RDB$RELATION_FIELDS take precedence over attributes in RDB$FIELDS. If the attribute value is missing in RDB$RELATION_FIELDS, the value from RDB$FIELDS is used. RDB$QUERY_NAME can be null. RDB$QUERY_HEADER list The query header of of byte the domain is used by varying interactive SQL. Column attributes in RDB$RELATION_FIELDS take precedence over attributes in RDB$FIELDS. If the attribute value is missing in RDB$RELATION_FIELDS, the value from RDB$FIELDS is used. RDB$DEFAULT_VALUE list The default value used by of byte non-SQL interfaces when varying no value is specified for a column during a STORE clause. It differs from RDB$MISSING_VALUE in that it holds an actual column value. Column attributes in RDB$RELATION_FIELDS take precedence over attributes in RDB$FIELDS. If the attribute value is missing in RDB$RELATION_FIELDS, the value from RDB$FIELDS is used. RDB$SEGMENT_LENGTH integer The length of a segmented string segment. For date- time interval fields, the interval leading field precision. RDB$EXTENSION_PARAMETERS list Reserved for future use. of byte varying RDB$CDD_NAME list The fully qualified name of of byte the dictionary entity upon varying which the domain definition is based, as specified in the SQL clause, FROM PATHNAME. RDBVMS$COLLATION_NAME char(31) The name of the collating sequence for the domain. RDB$DEFAULT_VALUE2 list The BLR for the SQL default of byte value. This value is used varying when no value is provided in an SQL INSERT statement. RDB$SECURITY_CLASS char(20) Reserved for future use. RDB$FLAGS integer Flags. RDB$CREATED date vms Set when the domain is created. RDB$LAST_ALTERED date vms Set when SQL ALTER DOMAIN statement used. RDB$FIELD_CREATOR char(31) Creator of this domain. 4 RDB$FLAGS Represents flags for RDB$FIELDS system table. Bit Position Description 0 A SQL CHECK constraint is defined on this domain. 1 AUTOMATIC set on insert. 2 AUTOMATIC set on update. 3 If check constraint fails, use name in message. 4 Column is computed an IDENTITY sequence. 5 View column is based on a read-only, or dbkey column. 3 RDB$GRANTED_PROFILES The RDB$GRANTED_PROFILES system table contains information about each profile, and role granted to other roles and users. The following table provides information on the columns of the RDB$GRANTED_PROFILES system table. See also the related RDB$PROFILES system table. Data Column Name Type Summary Description RDB$GRANTEE_PROFILE_ID integer This is a unique identifier generated for the parent RDB$PROFILES row. RDB$PROFILE_TYPE integer Class of profile information: role (1), default role (2), profile (0). RDB$PROFILE_ID integer Identification of the profile or role granted to this user. 3 RDB$INDEX_SEGMENTS The RDB$INDEX_SEGMENTS system table describes the columns that make up an index's key. Each index must have at least one column within the key. The following table provides information on the columns of the RDB$INDEX_SEGMENTS system table. Data Column Name Type Summary Description RDB$INDEX_NAME char(31) The name of the index of which this row is a segment. RDB$FIELD_NAME char(31) The name of a column that participates in the index key. This column name matches the name in the RDB$FIELD_NAME column of the RDB$RELATION_FIELDS table. 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) Reserved for future use. RDB$CARDINALITY bigint Prefix cardinality for this and all prior key segments (assumes sorting by ordinal position). 3 RDB$INDICES The RDB$INDICES system table contains information about indexes in the database. The following table provides information on the columns of the RDB$INDICES system table. Data Column Name Type Summary Description RDB$INDEX_NAME char(31) A unique index name. RDB$RELATION_NAME char(31) The name of the table 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 are allowed. o 1 If no duplicate values are 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 Flags. RDB$SEGMENT_COUNT integer The number of segments in the key. RDB$DESCRIPTION list A user-supplied description of byte of this index. varying RDB$EXTENSION_PARAMETERS list Stores NODE SIZE value, of byte PERCENT FILL value, varying 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) Reserved for future use. RDB$CREATED date vms Set when the index is created. RDB$LAST_ALTERED date vms Set when SQL ALTER INDEX statement 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 statistic 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 statistic 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 is left as 0. 4 RDB$FLAGS Represents flags for RDB$INDICES system table. Bit Position Description 0 Hashed index. 1 Index segments are numeric with mapping values compression. 2 Hashed ordered index. (If bit is clear, hashed scattered.) 3 Reserved for future use. 4 Run-length compression. 5 Index is disabled or enabled deferred. 6 Build pending (enabled deferred). 7 Reserved for future use. 8 Reserved for future use. 9 Reserved for future use. 10 Reserved for future use. 11 If on, duplicates are compressed. 12 Sorted ranked index. 13 Prefix cardinalities disabled. 14 Use the full collection algorithm for prefix cardinality. 15 Index generated for a constraint when SET FLAGS 'AUTO_ INDEX' was enabled. 3 RDB$INTERRELATIONS The RDB$INTERRELATIONS system table contains information that indicates the interdependencies of objects in the database. The RDB$INTERRELATIONS table can be used to determine if an object can be dropped or if some other object depends upon its existence in the database. The following table provides information on the columns of the RDB$INTERRELATIONS system table. Data Column Name Type Summary Description RDB$OBJECT_NAME char(31) The name of the object that cannot be dropped or altered because it is used by some other entity in the database. RDB$SUBOBJECT_NAME char(31) The name of the associated sub-object that cannot be dropped or altered because it 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 object identified by the RDB$OBJECT_NAME and RDB$SUBOBJECT_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 object specified in RDB$OBJECT_NAME and RDB$SUBOBJECT_NAME. RDB$USAGE char(31) The relationship among RDB$OBJECT_NAME, RDB$SUBOBJECT_NAME, RDB$ENTITY_NAME1, and RDB$ENTITY_NAME2. RDB$USAGE contains a short description. RDB$FLAGS integer Flags. RDB$CONSTRAINT_NAME char(31) This column is the name of a constraint that is referred to from another system table. The value in this column equates to a value for the same column in the RDB$CONSTRAINTS system table. RDB$SECURITY_CLASS char(20) Reserved for future use. 4 RDB$FLAGS Represents flags for RDB$INTERRELATIONS system table. Bit Position Description 0 Entity is a module. 1 Object is a module. 2 Entity is a routine. 3 Object is a routine. 4 Entity is a trigger. 5 Object is a trigger. 6 Entity is a constraint. 7 Object is a constraint. 8 Reserved. 9 Reserved. 10 Reserved. 11 Reserved. 12 Reserved. 13 Reserved. 14 Entity is a sequence. 15 Object is a sequence. 3 RDB$MODULES The RDB$MODULES system table 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 row is stored in the RDB$MODULES table for each module that is defined by a user. The following table provides information on the columns of the RDB$MODULES system table. Data Column 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 invoker rights module, this column 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 list Encoded information for of byte module level declarations. varying RDB$MODULE_HDR_SOURCE list Source of the module header of byte as provided by the definer. varying RDB$DESCRIPTION list Description of the module. of byte varying RDB$ACCESS_CONTROL list Access Control List (ACL) of byte to control access to the varying module. This value can be NULL. RDB$SECURITY_CLASS char(20) Reserved for future use. RDB$CREATED date vms Set when the module is created. RDB$LAST_ALTERED date vms Set when module is altered by the ALTER, RENAME, DROP, GRANT and REVOKE statements. RDB$MODULE_CREATOR char(31) Creator of this module. Differentiates between OWNER and AUTHORIZATION. RDB$VARIABLE_COUNT integer Number of global variables. 3 RDB$OBJECT_SYNONYMS The RDB$OBJECT_SYNONYMS system table is created with synonyms are enabled to record the synonym name, type, and target. The following table provides information on the columns of the RDB$OBJECT_SYNONYMS system table. Data Column Name Type Summary Description RDB$CREATED date vms Time and date when synonym entry was created. RDB$LAST_ALTERED date vms Time and date when synonym entry was last altered. RDB$DESCRIPTION list A user-supplied description of byte of the synonym. varying RDB$EXTENSION_PARAMETERS list Reserved for future use. of byte varying RDB$FLAGS integer Flags. RDB$OBJECT_TYPE integer The type of synonym. RDB$SYNONYM_NAME char(31) The synonym to be used by queries. This name is unique within the RDB$OBJECT_SYNONYMS system table. RDB$SYNONYM_VALUE char(31) name of the object for which the synonym is defined. RDB$SYNONYM_CREATOR char(31) Creator of the synonym entry. 4 RDB$FLAGS Represents flags for RDB$OBJECT_SYNONYMS system table. Bit Position Description 0 When set, this bit indicates that this synonym references another synonym. 1 Reserved for future use. 2 Indicates that the synonym was created by RENAME statement. 3 RDB$PARAMETERS The RDB$PARAMETERS system table 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 columns of the RDB$PARAMETERS system table. Data Column 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_SUB_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$DEFAULT_VALUE2 list Parameter default. of byte varying RDB$FLAGS integer Flags. RDB$DESCRIPTION list Description of the of byte parameter. varying RDB$SECURITY_CLASS char(20) Reserved for future use. 4 RDB$FLAGS Represents flags for RDB$PARAMETERS system table. Bit Position Description 0 IN (read) INOUT (modify). 1 OUT (write) INOUT (modify). 2 Reserved for future use. 3 BY DESCRIPTOR (default is BY REFERENCE). 4 BY VALUE (Bit number 3 is ignored). 5 Reserved for future use. 6 Set if parameter mode is undefined. If Bits 0 and 1 are both clear, then the parameter is the RETURN TYPE of a function. 3 RDB$PRIVILEGES The RDB$PRIVILEGES system table describes the protection for the database objects. There is one row per grantor, grantee, and privileges combination per entity in the database. A row is stored in the RDB$PRIVILEGES table for each user who grants another user privileges for a database object. If the privilege for a database object was granted without the SQL GRANT option, the row of the grantor and grantee is modified. The privilege change takes effect at commit time of the command. NOTE The RDB$PRIVILEGES system table is used only in ANSI databases. The following table provides information on the columns of the RDB$PRIVILEGES system table. Data Column Name Type Summary Description RDB$SUBOBJECT_ID integer The id of the column or routine for which protection is defined. If protection is on a database, module, table, or view, this field is NULL. The value stored in this column must be unique within the database. RDB$OBJECT_ID integer The id of the module, table, sequence, or view for which protection is defined. The column is NULL if the protection is defined for the database. The value stored in this column 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 SQL IMPORT statement, the UIC is that of the person who originally defined the protection for the user; not necessarily the person who performed the SQL IMPORT statement. RDB$GRANTEE list The binary format of the of byte UICs of the persons who varying hold privileges on the database object. 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 Flags. RDB$SECURITY_CLASS char(20) Reserved for future use. 4 RDB$FLAGS Represents flags for RDB$PRIVILEGES system table. Bit Position Description 0 Privilege is defined for a module and procedure. 1 The data is related to sequences. 3 RDB$PROFILES The RDB$PROFILES system table contains information about each profile, user and role defined for the database. The following table provides information on the columns of the RDB$PROFILES system table. See also the related RDB$GRANTED_PROFILES system table. Data Column Name Type Summary Description RDB$CREATED date vms time and date when profile entry was created. RDB$LAST_ALTERED date vms time and date when profile entry was last altered. RDB$DESCRIPTION list Comment for this entry. of byte varying RDB$EXTENSION_ list Extra definitions such as PARAMETERS of byte default transaction. varying RDB$SYSTEM_FLAG integer Set to TRUE (1) if this is a system define role or user, otherwise it is set to FALSE (0). When the RDB$SYSTEM_FLAG is set these entries may not be deleted by a DROP statement. RDB$FLAGS integer Flags. RDB$DEFINE_ACCESS integer Reserved for future use. RDB$CHANGE_ACCESS integer Reserved for future use. RDB$DELETE_ACCESS integer Reserved for future use. RDB$PROFILE_ID integer This is a unique identifier generated for each USER, PROFILE and ROLE added to the database. RDB$PROFILE_TYPE integer Class of profile information: role (1), user (3), profile (0). RDB$PROFILE_NAME char(31) Name of the user, profile or role. This name is unique within the RDB$PROFILES table. RDB$PROFILE_CREATOR char(31) Creator of entry. 4 RDB$FLAGS Represents flags for RDB$PROFILES system table. Bit Position Description 0 The user entry is disabled (ACCOUNT LOCK). 1 Means that the user/role is identified externally. 2 Reserved for future use. 3 This is a system role. 4 Means the user is assigned a profile. 3 RDB$QUERY_OUTLINES The RDB$QUERY_OUTLINES system table 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 columns of the RDB$QUERY_OUTLINES system table. Data Column 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 Flags. RDB$DESCRIPTION list A user-supplied description of byte of this outline. varying RDB$OUTLINE_BLR list The compiled query outline. of byte varying RDB$SECURITY_CLASS char(20) Reserved for future use. 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. 4 RDB$FLAGS Represents flags for RDB$QUERY_OUTLINES system table. Bit Position Description 0 This outline has been invalidated by some action, such as dropping a required table or index. 3 RDB$RELATION_CONSTRAINTS The RDB$RELATION_CONSTRAINTS system table lists all table- specific constraints. The following table provides information on the columns of the RDB$RELATION_CONSTRAINTS system table. Data Column Name Type Summary Description RDB$CONSTRAINT_MATCH_TYPE integer The match type associated with a referential integrity table-specific constraint. This column is reserved for future use. The value is always 0. RDB$CONSTRAINT_NAME char(31) The name of the constraint defined by the table specified by RDB$RELATION_ NAME. The value in this column equates to a value for the same column in the RDB$CONSTRAINTS system table. RDB$CONSTRAINT_SOURCE list This text string contains of byte the source of the varying constraint from the table definition. RDB$CONSTRAINT_TYPE integer The type of table-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 column is reserved for future use. The value is always 0. RDB$FIELD_NAME char(31) The name of the column for which a column-level, table-specific constraint is defined. The column is blank for a table-level constraint. RDB$FLAGS integer Flags. RDB$MODIFY_ACTION integer The type of referential integrity modify action specified. This column is reserved for future use. The value is always 0. 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 column will be null. Otherwise, the value in this column will equate to a value for the same columns in the RDB$CONSTRAINTS and RDB$RELATION_CONSTRAINT_ FLDS system tables. This column is used to determine the foreign key referenced table name and referenced column names. RDB$RELATION_NAME char(31) The name of the table on which the specified constraint is defined. The value in this column equates to a value for the same column in the RDB$RELATIONS system table. RDB$SECURITY_CLASS char(20) Reserved for future use. 4 RDB$FLAGS Represents flags for RDB$RELATION_CONSTRAINTS system table. Bit Position Description 0 This is SQL standard UNIQUE constraint which allows unique values and ignores NULL. 4 RDB$CONSTRAINT_TYPE The following table lists the values for the RDB$CONSTRAINT_TYPE column. 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. 3 RDB$RELATION_CONSTRAINT_FLDS The RDB$RELATION_CONSTRAINT_FLDS system table lists the columns that participate in unique, primary, or foreign key declarations for table-specific constraints. There is one row for each column that represents all or part of a unique, primary, or foreign key constraint. The following table provides information on the columns of the RDB$RELATION_CONSTRAINT_FLDS system table. Data Column Name Type Summary Description RDB$CONSTRAINT_NAME char(31) The name of a constraint for which the specified column participates. RDB$FIELD_NAME char(31) The name of the column that is all or part of the specified constraint. The value in this column is the same as that stored in the RDB$RELATION_FIELDS system table. RDB$FIELD_POSITION integer The ordinal position of the specified column within the column list that declares the unique, primary or foreign key constraint. For column-level constraints, there will always be only one column in the list. The first column 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) Reserved for future use. 3 RDB$RELATION_FIELDS The RDB$RELATION_FIELDS system table contains one row for each column in each table. The following table provides information on the columns of the RDB$RELATION_FIELDS system table. Data Column Name Type Summary Description RDB$RELATION_NAME char(31) The name of the table that contains the column represented by this row. RDB$FIELD_NAME char(31) The name of the column represented by this row within the table. Each RDB$RELATION_FIELDS row that has the same RDB$RELATION_NAME must have a unique RDB$FIELD_ NAME. RDB$FIELD_SOURCE char(31) The name of the domain (from the RDB$FIELD_NAME column within the RDB$FIELDS table) that supplies the definition for this column. RDB$FIELD_ID integer An identifier that can be used within the BLR to name the column represented by this row. Oracle Rdb assigns each column an id that is permanent for as long as the column exists within the table. RDB$FIELD_POSITION integer The ordinal position of the column represented by this row, relative to the other columns in the same table. RDB$QUERY_NAME char(31) The query name of this column. RDB$QUERY_NAME can be null. RDB$UPDATE_FLAG integer A value that indicates whether a column can be updated: o 0 If column cannot be updated. o 1 If column can be updated. RDB$QUERY_HEADER list The query header of this of byte column for use by SQL. varying Column attributes in RDB$RELATION_FIELDS take precedence over RDB$FIELDS. If the attribute value is missing in RDB$RELATION_FIELDS, SQL uses the value from RDB$FIELDS. RDB$DESCRIPTION list A user-supplied description of byte of the contents of this varying row. RDB$VIEW_CONTEXT integer For view tables, this column identifies the context variable used to qualify the view column. This context variable must be defined within the row selection expression that defines the view. The context variable appears in the BLR represented by the column RDB$VIEW_BLR in RDB$RELATIONS. RDB$BASE_FIELD char(31) The local name of the column used as a component of a view. The name is qualified by the context variable identified in RDB$VIEW_CONTEXT. RDB$DEFAULT_VALUE list The default value used by of byte non-SQL interfaces when varying no value is specified for a column during a STORE clause. It differs from RDB$MISSING_VALUE in that it holds an actual column value. Column attributes in RDB$RELATION_FIELDS take precedence over attributes in RDB$FIELDS. If the attribute value is missing in RDB$RELATION_FIELDS, the value from RDB$FIELDS is used. RDB$EDIT_STRING varchar(25The edit string to be used by interactive SQL when printing the column. RDB$EDIT_STRING can be null. RDB$EXTENSION_PARAMETERS list Reserved for future use. of byte varying RDB$ACCESS_CONTROL list The access control list for of byte the column. varying RDB$DEFAULT_VALUE2 list The BLR for SQL default of byte value. This value is used varying 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) Reserved for future use. 3 RDB$RELATIONS The RDB$RELATIONS system table names all the tables and views within the database. There is one row for each table or view. The following table provides information on the columns of the RDB$RELATIONS system table. Data Column Name Type Summary Description RDB$RELATION_NAME char(31) The name of a table within the database. Each row within RBB$RELATIONS must have a unique RDB$RELATION_ NAME. RDB$RELATION_ID integer An identification number used within the BLR to identify a table. RDB$STORAGE_ID integer A pointer to the database logical area where the data for this table is stored. RDB$SYSTEM_FLAG integer A value that indicates whether a table is a system table or a user-defined table: o 0 If a user table. o 1 If a system table. RDB$DBKEY_LENGTH integer The length in bytes of the database key. A database key for a row in a table is 8 bytes, and "n times 8 " for a view row, where "n" is the number of tables referred to in the view. If the view does not contain a dbkey, 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 table definition. This value is matched with the RDB$VERSION column in RDB$FIELD_VERSIONS to determine the current row format for the table. RDB$CARDINALITY bigint The number of rows in the table (cardinality). RDB$FLAGS integer Flags. RDB$VIEW_BLR list The BLR that describes the of byte row selection expression varying used to select the rows for the view. If the table is not a view, RDB$VIEW_BLR is missing. RDB$DESCRIPTION list A user-supplied description of byte of this table or view. varying RDB$VIEW_SOURCE list The user's source text for of byte the view definition. varying RDB$ACCESS_CONTROL list The access control policy of byte for the table. varying RDB$EXTENSION_PARAMETERS list Reserved for future use. of byte varying RDB$CDD_NAME list The fully qualified name of of byte the dictionary entity upon varying which the table definition is based, as specified in the SQL clause, FROM PATHNAME. RDBVMS$SECURITY_AUDIT integer A bit mask that indicates the privileges that will be audited for the table, as specified in the RMU Set Audit command. RDBVMS$SECURITY_ALARM integer A bit mask that indicates the privileges that produce alarms for the table, as specified in the RMU Set Audit command. RDB$SECURITY_CLASS char(20) Reserved for future use. RDBVMS$SECURITY_AUDIT2 integer Reserved for future use. RDBVMS$SECURITY_ALARM2 integer Reserved for future use. RDB$CREATED date vms Set when the table or view is created (for system tables it will be the same as the database creation timestamp). RDB$LAST_ALTERED date vms Set when SQL ALTER TABLE, CREATE/ALTER STORAGE MAP, ALTER DOMAIN, GRANT, or REVOKE statements cause changes to this system table. RDB$RELATION_CREATOR char(31) Creator of this system 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. RDB$TYPE_ID integer Reserved for future use. 4 RDB$FLAGS Represents flags for RDB$RELATIONS system table. Bit Position Description 0 This table is a view. 1 This table is not compressed. 2 The SQL clause, WITH CHECK OPTION, is used in this view definition. 3 Indicates a special internal system table. 4 This view is not an ANSI updatable view. 5 Reserved for future use. 6 Reserved for future use. 7 Reserved for future use. 8 Ignore Bit 1 and use RDB$STORAGE_MAPS for compression information. 9 Set for temporary table. 10 Set for global temporary table; clear for local temporary table. 11 Set for delete data on commit; clear for preserve data on commit. 12 Reserved for future use. 13 Set if view or table references a local temporary table. 14 Special read-only information table. 15 System table has storage map. 16 View references only temporary table. 3 RDB$ROUTINES The RDB$ROUTINES system table describes each routine that is part of a stored module or a standalone external routine. An external routine can either be part of a module or standalone (outside the context of a module). The following table provides information on the columns of the RDB$ROUTINES system table. Data Column Name Type Summary Description RDB$ROUTINE_NAME char(31) Name of the routine. RDB$GENERIC_ROUTINE_NAME char(31) Reserved for future use. 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$MIN_PARAMETER_COUNT integer Minimum number of parameters for this routine. RDB$ROUTINE_BLR list The BLR for this routine. of byte If the routine is external, varying this column is set to NULL. RDB$ROUTINE_SOURCE list Source of the routine as of byte provided by the definer. varying RDB$FLAGS integer Flags. RDB$SOURCE_LANGUAGE integer The RDB$SOURCE_LANGUAGE section lists the values for this column. RDB$DESCRIPTION list Description of the routine. of byte varying RDB$ACCESS_CONTROL list The access control list of byte (ACL) to control access to varying the routine. This value can be NULL. RDB$SECURITY_CLASS char(20) Reserved for future use. RDB$EXTENSION_PARAMETERS list Stores interface of byte information about the varying routine. This includes parameter mappings, the shareable image name, and entry point name. RDB$TYPE_ID integer Reserved for future use. RDB$ROUTINE_OWNER char(31) Owner of the routine. This column is only used when the routine is standalone (when RDB$MODULE_ID is 0) otherwise the value is NULL. RDB$CREATED date vms Set when the routine is created (the same as the parent module's creation timestamp). RDB$LAST_ALTERED date vms Set when the routine is modified by the ALTER, RENAME, GRANT, and REVOKE statements. RDB$ROUTINE_CREATOR char(31) Creator of this routine. Differentiates between AUTHORIZATION and OWNER. 4 RDB$FLAGS Represents flags for RDB$ROUTINES system table. Bit Position Description 0 Routine is a function. (Call returns a result.) 1 Routine is not valid. (Invalidated by a metadata change.) 2 The function is not deterministic (that is, the routine is variant). A subsequent invocation of the routine with identical parameters may return different results. 3 Routine can change the transaction state. 4 Routine is in a secured shareable image. 5 Reserved for future use. 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.) 7 Reserved for future use. 8 External function returns NULL when called with any NULL parameter. 9 Routine has been analyzed (used for trigger dependency tracking). 10 Routine inserts rows. 11 Routine modifies rows. 12 Routine deletes rows. 13 Routine selects rows. 14 Routine calls other routines. 15 Reserved for future use. 16 Routine created with USAGE IS LOCAL clause. 17 Reserved for future use. 18 Reserved for future use. 19 Routine is a SYSTEM routine. 20 Routine generated by Oracle Rdb. Other bits are reserved for future use. 4 RDB$SOURCE_LANGUAGE The following table lists the values for the RDB$SOURCE_LANGUAGE column. 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 stored functions and stored procedures 3 RDB$SEQUENCES The RDB$SEQUENCES system table contains information about each sequence. The following table provides information on the columns of the RDB$SEQUENCES system table. Data Column Name Type Summary Description RDB$CREATED date vms Time sequence was created. RDB$LAST_ALTERED date vms Last time sequence was altered. RDB$ACCESS_CONTROL list Access control list for of byte this sequence. varying RDB$DESCRIPTION list Description provided for of byte this sequence. varying RDB$START_VALUE bigint Starting value for the sequence. RDB$MINIMUM_SEQUENCE bigint Minimum value for the sequence. RDB$MAXIMUM_SEQUENCE bigint Maximum value for the sequence. RDB$NEXT_SEQUENCE_ bigint Next value available for VALUE use for the sequence. This column is a read only COMPUTED BY column. When the sequence is first defined this column returns NULL. RDB$INCREMENT_VALUE integer Increment value for the sequence. A positive value indicates an ascending sequence, and a negative value indicates a descending sequence. RDB$CACHE_SIZE integer Number of sequence numbers to allocate and hold in memory. If one (1), then NOCACHE was specified and the values will be allocated one at a time. RDB$FLAGS integer Flags. RDB$SEQUENCE_ID integer Unique number assigned to this sequence object. This value is for internal use only. RDB$SEQUENCE_NAME char(31) Unique name of the sequence. RDB$SEQUENCE_CREATOR char(31) Creator of this sequence. 4 RDB$FLAGS Represents flags for RDB$SEQUENCES system table. Bit Position Description 0 Sequence will cycle. 1 Sequence is ordered. 2 Sequence is random. 3 Indicates that this is a system sequence and may not be dropped. 4 Indicates that there was no minimum value specified. 5 Indicates that there was no maximum value specified. 6 Indicates that this is a column IDENTITY sequence. 7 Indicates that this sequence will wait for locks. 8 Indicates that this sequence will not wait for locks. 3 RDB$STORAGE_MAPS The RDB$STORAGE_MAPS system table contains information about each storage map. The following table provides information on the columns of the RDB$STORAGE_MAPS system table. Data Column Name Type Summary Description RDB$MAP_NAME char(31) The name of the storage map. RDB$RELATION_NAME char(31) The name of the table to which the storage map refers. RDB$INDEX_NAME char(31) The name of the index specified in the SQL clause, PLACEMENT VIA INDEX, of the storage map. RDB$FLAGS integer Flags. RDB$MAP_SOURCE list The user's source text for of byte the storage map definition. varying RDB$DESCRIPTION list A user-supplied description of byte of the storage map. varying RDB$EXTENSION_PARAMETERS list Lists the column names of byte for vertical record varying 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$VERTICAL_PARTITION_ char(31) Name of the vertical record NAME partition. RDB$SECURITY_CLASS char(20) Reserved for future use. 4 RDB$FLAGS Represents flags for RDB$STORAGE_MAPS system table. Bit Position Description 0 If map is for a mixed format area. 1 If map enables compression. 2 Partition key cannot be updated. 3 Reserved for future use. 4 User named this partition. 5 Override used for strict partitioning - NO REORGANIZE. 3 RDB$STORAGE_MAP_AREAS The RDB$STORAGE_MAP_AREAS system table contains information about each storage area to which a storage map refers. The following table provides information on the columns of the RDB$STORAGE_MAP_ AREAS system table. Data Column 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 root of the SORTED index, if it is a SORTED index. RDB$ORDINAL_POSITION integer The order of the storage area represented by this row in the map. RDB$STORAGE_ID integer For a table, 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 list The BLR that represents of byte the SQL clause, WITH LIMIT varying OF, in the storage map definition. RDB$DESCRIPTION list Description of this of byte partition. varying RDB$EXTENSION_PARAMETERS list Lists table names and of byte column names that are varying 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 Flags. RDB$SECURITY_CLASS char(20) Reserved for future use. RDB$PARTITION_NAME char(31) Name of the index or storage map partition. 4 RDB$FLAGS Represents flags for RDB$STORAGE_MAP_AREAS system table. Bit Position Description 0 If Bit 0 is clear, the LIST storage area set is filled randomly. If Bit 0 is set, the LIST storage area set is filled sequentially. 1 User named this partition. 2 BUILD PARTITION is required. 3 Deferred build using NOLOGGING. 3 RDB$SYNONYMS The RDB$SYNONYMS system table 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 table is present only in databases that have the SQL multischema feature enabled. Unlike rows in other system tables, the rows in the RDB$SYNONYMS system table are compressed. The following table provides information on the columns of the RDB$SYNONYMS system table. Data Column 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 domain (global field). o 26 An index. o 31 A system table. o 36 A view. o 60 A sequence. o 67 A storage map. o 81 A trigger. o 117 A collating sequence. o 180 An outline. o 192 A type. RDB$STORED_NAME char(31) The name of an object as is actually stored in the database. RDB$SECURITY_CLASS char(20) Reserved for future use. 3 RDB$TRIGGERS The RDB$TRIGGERS system table describes the definition of a trigger. The following table provides information on the columns of the RDB$TRIGGERS system table. Data Column Name Type Summary Description RDB$DESCRIPTION list A user-supplied text string of byte describing the trigger. varying RDB$FLAGS integer Flags. RDB$RELATION_NAME char(31) The name of the table for which this trigger is defined. The trigger may be selected on an update to the named table (qualified by the columns described in the RDB$TRIGGER_FIELD_NAME_ LIST). This table is used as a subject table for all contexts that refer to it. RDB$TRIGGER_ACTIONS list A text string containing of byte all the sets of triggered varying 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_ list A text string composed of LIST of byte a count field and one or varying 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 column names. If the trigger is of event type UPDATE, it will be evaluated if one or more of the specified columns 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 table 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 table that existed before an UPDATE event. RDB$TRIGGER_SOURCE list An optional text string for of byte the trigger definition. The varying string is not used by the database system. It should reflect the full definition of the trigger. This column 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) Reserved for future use. RDB$CREATED date vms Set when the trigger is created. RDB$LAST_ALTERED date vms Set when SQL ALTER TRIGGER statement is used. RDB$TRIGGER_CREATOR char(31) Creator of this trigger. RDB$EXTENSION_PARAMETERS list Extension parameters. of byte varying 4 RDB$FLAGS Represents flags for RDB$TRIGGERS system table. Bit Position Description 0 Trigger is currently disabled. 1 Invalid due to changed schema. 2 Referenced table was altered. 4 TRIGGER_TYPE_VAL The following table lists the values for the RDB$TRIGGER_TYPE column of the RDB$TRIGGERS system table 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 UPDATE. 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 table lists all the tables that participate in a given view. There is one row for each table or view in a view definition. The following table provides information on the columns of the RDB$VIEW_RELATIONS system table. Data Column Name Type Summary Description RDB$VIEW_NAME char(31) Names a view or table that uses another table. The value of RDB$VIEW_NAME is normally a view name, but might also be the name of a table that includes a column computed using a statistical expression. RDB$RELATION_NAME char(31) The name of a table used to form the view. RDB$VIEW_CONTEXT integer An identifier for the context variable used to identify a table in the view. The context variable appears in the BLR represented by the column RDB$VIEW_BLR in RDB$RELATIONS. RDB$SECURITY_CLASS char(20) Reserved for future use. 3 RDB$WORKLOAD The RDB$WORKLOAD system table 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 an SQL CREATE or ALTER DATABASE statement. Once created, this system table can never be dropped. The following table provides information on the columns of the RDB$WORKLOAD system table. Data Column Name Type Summary Description RDB$CREATED date vms Time workload 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 cardinality. Number of duplicate values for an interesting column group (RDB$FIELD_GROUP). 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 identifier. RDB$FLAGS integer Reserved for future use. RDB$FIELD_GROUP char(31) Contains up to 15 sorted column identifiers. RDB$SECURITY_CLASS char(20) Reserved for future use. 2 Information_Tables Information tables display internal information about storage areas, after-image journals, row caches, database users, the database root, and database character sets. Once the information tables are created, you can use the INFO_TABLES.SQL script to query the tables with the SQL interface. Information tables are special read-only tables that can be created in an Oracle Rdb database and used to retrieve database attributes that are not stored in the existing relational tables. Information tables allow interesting database information, which is currently stored in an internal format, to be displayed as a relational table. The script, INFO_TABLES.SQL, is supplied as a part of the Oracle Rdb kit in the SQL$SAMPLE directory. 3 All_Information_Tables The following information tables are supported: Table Name Description RDB$STORAGE_AREAS Displays information about the database storage areas. RDB$DATABASE_JOURNAL Displays information about the default journal information. RDB$CACHES Displays information about the database row caches. RDB$DATABASE_ROOT Displays information about the database root. RDB$JOURNALS Displays information about the database journal files. RDB$DATABASE_USERS Displays information about the database users. RDB$LOGICAL_AREAS Displays information about the logical areas. RDB$CHARACTER_SETS Displays information about the Oracle Rdb character sets. RDB$NLS_CHARACTER_SETS Displays the mapping of Oracle NLS character sets to Oracle Rdb character sets. 3 Example The following example shows how to query one of the information tables created by the INFO_TABLES.SQL script: SQL> SELECT * FROM RDB$LOGICAL_AREAS WHERE RDB$LOGICAL_AREA_NAME='JOBS'; RDB$LOGICAL_AREA_ID RDB$AREA_ID RDB$RECORD_LENGTH RDB$THRESHOLD1_PERCENT RDB$THRESHOLD2_PERCENT RDB$THRESHOLD3_PERCENT RDB$ORDERED_HASH_OFFSET RDB$RECORD_TYPE RDB$LOGICAL_AREA_NAME 95 7 41 0 0 0 0 1 JOBS 1 row selected 3 RDB$STORAGE_AREAS Displays information about the database storage areas. Data Column Name Type Description RDB$LAST_BACKUP date vms Date of last backup RDB$LAST_INCREMENT_ date vms Date of last restore RESTORE RDB$INITIAL_ALLOCATION integer Allocation RDB$CURRENT_ALLOCATION integer Maximum page number RDB$AREA_ACCESS_MODE integer Access mode RDB$THRESHOLD1_BYTES integer First threshold value RDB$THRESHOLD2_BYTES integer Second threshold value RDB$THRESHOLD3_BYTES integer Third threshold value RDB$PAGE_SIZE integer Page size RDB$AREA_MIN_EXTENT integer Minimum page extent RDB$AREA_MAX_EXTENT integer Maximum page extent RDB$AREA_PERCENT_EXTENT integer Percent growth RDB$EXTEND_COUNT integer Number of times area has been extended RDB$SPAM_INTERVAL integer Number of pages per SPAM page RDB$JOURNAL_SEQUENCE integer AIJ recovery version number RDB$MAX_PAGE_FREE_SPACE integer Maximum free space on new page RDB$MAX_ROW_LENGTH integer Largest segment that can be stored on a page RDB$SEQUENCE integer Sequence number RDB$AREA_ID integer Area ID RDB$LINKED_AREA_ID integer For live storage area, ID of snapshot area. For snapshot area, ID of live storage area RDB$FLAGS integer Flags RDB$THRESHOLD1_PERCENT integer First threshold percent value RDB$THRESHOLD2_PERCENT integer Second threshold percent value RDB$THRESHOLD3_PERCENT integer Third threshold percent value RDB$AREA_FILE char(255) Storage area file name RDB$CACHE_ID integer Row cache ID RDB$AREA_NAME char(31) Storage area name RDB$CLUMP_PAGE_COUNT integer The number of pages per logical area clump (UNIFORM area) RDB$PAGE_LENGTH integer Page length in bytes RDB$PAGES_PER_SPAM_PAGE_ integer Number of data pages per P1 SPAM page + 1 RDB$SPAM_PAGE_VECTOR_ integer SPAM page vector length in LENGTH bytes RDB$PAGE_PAD_LENGTH integer Page padding length in bytes RDB$MAX_SEGMENT_LENGTH integer Largest length to which an existing segment can grow RDB$BACKUP_STATISTICS bigint Backup-specific statistics RDB$SNAPS_ENABLED_TSN bigint TSN of oldest snapshots. If this is a live storage area that has snapshots allowed, this field contains the TSN of the most recent transaction that enabled snapshots for or performed exclusive updates to this area RDB$COMMIT_TSN bigint TSN to which this area is consistent. If the area is marked inconsistent, this is the TSN level to which the area is currently consistent RDB$BACKUP_TSN bigint TSN of last full area backup. If this field contains zero, this area has not been backed up RDB$INCR_BACKUP_TSN bigint TSN of last incremental area backup. If this field contains zero, this area has not been incrementally backed up RDB$LAST_EXTEND date vms Date of last area extend 4 RDB$FLAGS Represents flags for RDB$STORAGE_AREAS information table. Bit Position Description 0 Page format is mixed 1 Checksum calculation is enabled 2 This is a snapshot area 3 Area has snapshots 4 Snapshots are enabled 5 Area has space management pages 6 SPAM pages are enabled 7 Allow multi-volume disk extents 8 Extent is enabled 9 Area is corrupt 10 Area is inconsistent 11 WORM device is full 12 Page level locking 13 Write once 14 WORM area is corrupt 15 Row cache is enabled 16 Area has been restructured. If TRUE, this area has been restructured and may only be readied by a restructuring process 17 Roll forward quiet-point is enabled 18 Area is corrupt due to Hot Standby. If TRUE, this storage area may have been corrupted by an aborted batch update transaction 3 RDB$JOURNALS Displays information about the database journal files. Column Name Data Type Description RDB$LAST_BACKUP date vms Date of last AIJ backup RDB$STATUS integer AIJ file is inaccessible RDB$ALLOCATION integer Allocation RDB$EXTENT integer Extent RDB$FLAGS integer Flags RDB$JOURNAL_NAME char(31) Journal name RDB$LAST_BACKUP_SEQUENCE integer Last version number backed up RDB$LAST_ACTIVATED date vms Date last activated RDB$BACKUP_EDIT_STRING varchar(255) Backup editname file RDB$DEFAULT_FILENAME char(255) Default journal name RDB$FILENAME char(255) Journal name RDB$BACKUP_FILENAME char(255) Backup file name RDB$SEQUENCE_NUMBER integer Current AIJ sequence number RDB$STATE char(31) State ("Current" or "Latent") 4 RDB$FLAGS Represents flags for RDB$JOURNALS information table. Bit Position Description 0 Initialization in progress 1 Backup in progress 2 AIJ file has been modified 3 AIJ restored from existing file 4 Hard data loss resulted from fail over 5 Soft data loss resulted from fail over 6 New version of journal created 7 Journal has been overwritten 8 Backup failed prematurely 9 Journal created due to switch-over suspension 10 AIJ file block has been assigned 11 Journal created for recovery synchronization 3 RDB$CACHES Displays information about the database row caches. Data Column Name Type Description RDB$ALLOCATION integer Allocation RDB$EXTENT integer Extent RDB$CACHE_ID integer Cache ID RDB$FLAGS integer Flags RDB$ROW_LENGTH integer Row length RDB$CACHE_SIZE integer Cache size RDB$WINDOW_COUNT integer Window count RDB$NUM_RESERVED_ROWS integer Number of reserved rows RDB$NUM_SWEEP_ROWS integer Number of sweep rows RDB$CACHE_NAME char(31) Cache name RDB$LOCATION char(255) Cache location RDB$SNAP_CACHE_SIZE integer Number of snapshot record slots in cache RDB$PHYSICAL_MEMORY bigint Physical memory in bytes 4 RDB$FLAGS Represents flags for RDB$CACHES information table. Bit Position Description 0 Shared memory is system 1 Large memory is enabled 2 Row replacement is enabled 3 Device/directory is defined 4 Updated rows to database 5 Updated rows to backing file 6 All rows to backing file 7 Snapshots are enabled 3 RDB$DATABASE_ROOT Displays information about the database root. Data Column Name Type Description RDB$CREATED date vms Date of database creation RDB$LAST_FULL_BACKUP_TIME date vms Date of last complete full backup RDB$MAJOR_VERSION integer Major software version RDB$MINOR_VERSION integer Minor software version RDB$PRIOR_MAJOR_VERSION integer Major software version for database converted with /nocommit RDB$PRIOR_MINOR_VERSION integer Minor software version for database converted with /nocommit RDB$FLAGS integer Flags RDB$MAX_USERS integer Number of users RDB$MAX_CLUSTER_NODES integer Number of cluster nodes RDB$DEFAULT_NUM_OF_BUFFERS integer Number of buffers RDB$MAX_RECOVERY_BUFFERS integer Number of recover buffers RDB$BUFFER_SIZE integer Buffer size RDB$GLOBAL_BUFFER_COUNT integer Number of global buffers RDB$ALG_COUNT integer Adjustable lock granularity count RDB$LOCK_TIMEOUT_INTERVAL integer Lock timeout interval in seconds RDB$CHECKPOINT_INTERVAL_BLKS integer Checkpoint block interval RDB$CHECKPOINT_TIMED_SECONDS integer Checkpoint time interval RDB$TRANSACTION_INTERVAL integer Commit transaction interval RDB$CLOSE_MODE integer Close mode RDB$CLOSE_INTERVAL integer Close interval RDB$ASYNC_PREFETCH_DEPTH integer Async prefetch depth RDB$D_ASYNC_PREFETCH_DEPTH integer Detected async prefetch depth RDB$D_ASYNC_PREFETCH_THRESHOLD integer Detected async prefetch threshold RDB$CLEAN_BUFFER_COUNT integer Clean buffer count RDB$MAX_BUFFER_COUNT integer Maximum buffer count RDB$MIN_PAGE_SIZE_BLOCKS integer Minimum area page block count RDB$MAX_PAGE_SIZE_BLOCKS integer Maximum area page block count RDB$TRANSACTION_MODE_FLAGS integer Transaction mode RDB$ALG_FACTOR_0 integer Adjustable locking granularity factor 0 RDB$ALG_FACTOR_1 integer Adjustable locking granularity factor 1 RDB$ALG_FACTOR_2 integer Adjustable locking granularity factor 2 RDB$ALG_FACTOR_3 integer Adjustable locking granularity factor 3 RDB$ALG_FACTOR_4 integer Adjustable locking granularity factor 4 RDB$ALG_FACTOR_5 integer Adjustable locking granularity factor 5 RDB$ALG_FACTOR_6 integer Adjustable locking granularity factor 6 RDB$ALG_FACTOR_7 integer Adjustable locking granularity factor 7 RDB$AUDIT_FILENAME char(255) Audit journal file name RDB$ROOT_FILENAME char(255) Database root file name RDB$RUJ_LOCATION char(255) Default recovery-unit journal file name RDB$CACHE_LOCATION char(255) Default device/directory specification for record cache files RDB$MAX_PAGES_IN_BUFFER integer Maximum number of pages in a buffer RDB$RCS_SWEEP_INTERVAL integer Row cache server (RCS) sweep interval (in seconds) RDB$RCS_CKPT_TIME integer Time interval to force row cache server (RCS) to checkpoint. This field contains the number of seconds that pass before RCS is forced to perform another checkpoint RDB$LAST_FULL_RESTORE date vms Date of last complete full restore RDB$AIJ_ACTIVATION_ID bigint AIJ journaling activation identifier RDB$RCVR_ACTIVATION_ID bigint RCVR journaling activation identifier RDB$OPER_CLASS integer Operator notification classes RDB$PRESTART_TXN_TIMEOUT integer Seconds until prestarted transaction is abandoned. Zero means no abandon timer RDB$DB_REPLICATED integer AIJ log roll forward server started 4 RDB$FLAGS Represents flags for RDB$DATABASE_ROOT information table. Bit Position Description 0 Single file database 1 Open mode 2 Log server mode 3 Snapshots are deferred 4 Global buffers are enabled 5 Carryover locks are enabled 6 Statistics collection is enabled 7 Fast commit is enabled 8 AIJ commit optimization is enabled 9 RUJ is corrupt 10 Database is corrupt 11 Fast incremental backup is enabled 12 Async prefetch is enabled 13 Async batch writes are enabled 14 Lock partitioning is enabled 15 Page transfer via memory 16 Detected async prefetch is enabled 17 Shared memory is system 18 Database has been modified (TSN allocated) 19 Database conversion has been committed 20 Row cache server (RCS) checkpoints to database, by default 21 RCS checkpoints to backing store files, by default 22 RCS checkpoints marked and unmarked to RDC 23 Global buffers should be in VLM 24 Row cache RUJ global buffers are disabled 25 LogMiner feature is enabled 26 Prestarted transactions are enabled 27 VMS Galaxy shared memory is enabled 3 RDB$DATABASE_JOURNAL Displays information about the default journal information. Data Column Name Type Description RDB$CONDITION integer AIJ status RDB$DEFAULT_ALLOCATION integer Default allocation in blocks RDB$DEFAULT_EXTENT integer Default extension in blocks RDB$CURRENT_BACKUP_ integer Backup sequence number SEQUENCE RDB$CURR_RECOVERY_ integer Recovery sequence number SEQUENCE RDB$DATABASE_BACKUP_ integer Database backup sequence SEQUENCE number RDB$ALLOCATION integer Number of allocated AIJ file blocks RDB$SHUTDOWN_TIME_MIN integer Shutdown time in minutes RDB$OPERATOR_CLASSES integer Operator class RDB$FLAGS integer Flags RDB$DEFAULT_BACKUP_ char(255) Default backup file name FILENAME RDB$CACHE_FILENAME char(255) Cache file name RDB$STANDBY_FILENAME char(255) Standby database file name RDB$SERVER_NAME char(31) Server name RDB$BACKUP_EDIT_STRING varchar(25Backup editname file RDB$REMOTE_NODE_NAME char(31) Remote node name RDB$CUR_ACTIVE_AIJ integer Current active AIJ journal index RDB$MASTER_FILENAME char(255) When database replication is active on the standby database, this field contains the file name of the master database 4 RDB$FLAGS Represents flags for RDB$DATABASE_JOURNAL information table. Bit Position Description 0 Journaling is enabled 1 Overwrite is enabled 2 Backup mode 3 New journal version 4 ABS uses quiet-point AIJ backup 5 Replicated as master 6 Replicated as standby 7 Master replication database 8 Database replication online 9 Hot Standby quiet-point 10 Hot Standby is enabled 11 Database changes made when AIJ disabled 12 One or more journals overwritten 13 Hard data loss resulted from fail over 14 Full quiet-point AIJ backup required 3 RDB$DATABASE_USERS Displays information about the database users. Data Column Name Type Description RDB$PROCESS_ID integer Process ID number RDB$STREAM_ID integer Stream ID number RDB$MONITOR_ID integer Monitor ID number RDB$ATTACH_ID integer Attach ID number RDB$FLAGS integer Flags 4 RDB$FLAGS Represents flags for RDB$DATABASE_USERS information table. Bit Position Description 0 Client server process 1 AIJ log server 2 Process is being recovered 3 Database server process 4 Database utility process 5 Catch-up server 6 AIJ roll forward server 7 Row cache server 8 Log shipping server 9 Backup server 3 RDB$LOGICAL_AREAS Displays information about the logical areas. Data Column Name Type Description RDB$LOGICAL_AREA_ID integer Logical area ID RDB$AREA_ID integer Physical area ID RDB$FLAGS integer Flags RDB$RECORD_LENGTH integer Record length RDB$THRESHOLD1_PERCENT integer First threshold percent value RDB$THRESHOLD2_PERCENT integer Second threshold percent value RDB$THRESHOLD3_PERCENT integer Third threshold percent value RDB$ORDERED_HASH_OFFSET integer Ordered hash offset RDB$RECORD_TYPE integer AIP record type RDB$LOGICAL_AREA_NAME char(31) Logical area name 4 RDB$FLAGS Represents flags for RDB$LOGICAL_AREAS information table. Bit Position Description 0 Logical area uses hash ordered index 1 Logical area modified with unjournaled records 2 Nologging is enabled 4 RDB$RECORD_TYPE Represents AIP record types for RDB$LOGICAL_AREAS information table. AIP Record Types Description 0 Unknown 1 Table 2 Sorted index 3 Hashed index 4 System record 5 Segmented string 3 RDB$CHARACTER_SETS Displays information about the Oracle Rdb character sets. Data Column Name Type Description RDB$LOWCASE_MAPPING varbyte Segmented string containing list a 256 byte table used for lowercasing characters RDB$UPCASE_MAPPING varbyte Segmented string containing list a 256 byte table used for uppercasing characters RDB$ASSOCIATED_ integer Identifier of the associated CHARACTER_SET character set RDB$CHARACTER_SET_ID integer Character set identifier RDB$CHARACTER_SET_NAME char(31) Character set name RDB$CHARACTER_WILDCARD integer Character used as wildcard character RDB$FLAGS integer Character set flags RDB$FORM_OF_USE integer Character set form-of-use indicator RDB$IDENTIFIER_ integer Character set ID of the CHARACTER_SET indentifier character set RDB$MAXIMUM_OCTETS integer Maximum number of octets per character RDB$MINIMUM_OCTETS integer Minimum number of octets per character RDB$REPERTOIRE integer Character set repertoire RDB$SPACE_CHARACTER integer Character used as space RDB$STRING_WILDCARD integer Character used as string wildcard RDB$VERSION integer Version number of character set entry 4 RDB$REPERTOIRE Represents the repertoire values for RDB$CHARACTER_SETS information table. Value Name Description 0 OTHER Non-specific repertoire 1 LATIN Contains mainly Latin characters 2 JAPANESE Contains mainly Japanese characters 3 SIMPLE_ Contains mainly simplified Chinese CHINESE characters 4 KOREAN Contains mainly Korean characters 5 OLD_CHINESE Contains mainly traditional Chinese characters 6 UNIVERSAL Contains universal characters; for example, UNICODE 7 INDIAN Contains mainly Indian characters 8 ARABIC Contains mainly Arabic characters 9 GREEK Contains mainly Greek characters 10 CYRILLIC Contains mainly Cyrillic characters 11 HEBREW Contains mainly Hebrew characters 4 RDB$FORM_OF_USE Represents the form-of-use values for RDB$CHARACTER_SETS information table. Value Name Description 0 FIXED_OCTET Fixed octet 1 MIXED_OCTET Mixed octet with DEC_KANJI style encoding 2 FIXED_NO_UP Fixed octet, no uppercasing allowed 3 MIXED_SS2 As in MIXED_OCTET plus as introducer to alternate single octet encoding 4 FIXED_UP_G1 Fixed octet, uppercasing only 7-bit characters 5 MIXED_SS3 As in MIXED_SS2 plus as introducer to alternate double octet encoding 6 FIXED_OTHER Fixed octet, other 7 MIXED_C2CB As in MIXED_OCTET plus hex 'C2CB' as introducer to alternate double octet encoding 9 MIXED_TAG Mixed octet with leading tag, compound string 11 MIXED_SHIFT Mixed octet with coding table shifted; for example, SHIFT_JIS 4 RDB$FLAGS Represents flags for RDB$CHARACTER_SETS information table. Bit Position Name Description 0 CONTAINS_ASCII Character set contains 7-bit ASCII characters 1 SPACE_OCTET_REPEATS All octets of the multi-octet space character are the same value 3 RDB$NLS_CHARACTER_SETS Represents the mapping of Oracle NLS character sets to Oracle Rdb character sets. Data Column Name Type Description RDB$CHARACTER_SET_ integer Character set identifier ID RDB$NLS_ID integer Oracle NLS identifier of character set RDB$NLS_NAME char(31) Oracle NLS character set name 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 SQLPRE72 at the DCL prompt for information on SQL Precompiler. 2 Module_language Type HELP SQLMOD72 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 Rdb 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. 2 Logical_Names The following table lists the logical names that SQL recognizes for special purposes. Table 1 Summary of SQL Logical Names Logical Name Function RDB$CHARACTER_SET 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 also deprecated and will not be supported in a future release. RDB$LIBRARY 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_FLAGS Can be used to override the four event flag numbers that are assigned to RDB$SHARE at startup time by the LIB$GET_EF system service. RDB$REMOTE_BUFFER_SIZE Changes the default 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_MULTIPLEX_OFF Disables the multiplex feature. RDB$ROUTINES Specifies the location of an external routine image. If you do not specify a location clause in a CREATE FUNCTION, CREATE PROCEDURE, or CREATE MODULE statement, or if you specify the DEFAULT LOCATION clause, SQL uses the RDB$ROUTINES logical name as the default image location. RDMS$BIND_OUTLINE_MODE When multiple outlines exist for a query, this logical name is defined to select which outline to use. RDMS$BIND_QG_CPU_TIMEOUT Specifies the amount of CPU time used to optimize a query for execution. RDMS$BIND_QG_REC_LIMIT Specifies the number of rows that SQL fetches before the query governor stops output. RDMS$BIND_QG_TIMEOUT Specifies the number of seconds that SQL spends compiling a query before the query governor aborts that query. RDMS$BIND_SEGMENTED_ Allows you to reduce the overhead of STRING_BUFFER I/O operations at run time when you are manipulating a segmented string. RDMS$DEBUG_FLAGS Allows you to examine database access strategies and the estimated cost of those strategies when your program runs. RDMS$SET_FLAGS Allows you to examine database access strategies and the estimated cost of those strategies when your program runs. See the SET FLAGS statement for a list of valid keywords that can be used with this logical name. RDMS$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_PAGE_CNT Specifies the size of the shared memory area used to manipulate server site-bound, external routine parameter data and control data. RDMS$USE_ Allows applications to use the OLD_CONCURRENCY isolation-level behavior that was in effect for V4.1. RDMS$USE_OLD_SEGMENTED_ When defined to YES, the default STRING online format for lists (segmented strings) is chained. RDMS$VALIDATE_ROUTINE Controls the validation of routines. SQL$DATABASE Specifies the database that SQL declares if you do not explicitly declare a database. SQL$DISABLE_CONTEXT Disables the two-phase commit protocol. Useful for turning off distributed transactions when you want to run batch-update transactions. SQL$EDIT Specifies the editor that SQL invokes when you issue the EDIT statement in interactive SQL. SQLINI Specifies the command file that SQL executes when you invoke interactive SQL. SYS$CURRENCY Specifies the character that SQL substitutes for the dollar sign ($) symbol in an EDIT STRING clause of a column or domain definition, or the EDIT USING clause of a SELECT statement. SYS$DIGIT_SEP Specifies the character that SQL substitutes for the comma symbol (,) in an EDIT STRING clause of a column or domain definition, or the EDIT USING clause of a SELECT statement. SYS$LANGUAGE Specifies the language that SQL uses for date and time input and displays, or the EDIT USING clause of a SELECT statement. SYS$RADIX_POINT Specifies the character that SQL substitutes for the decimal point symbol (.) in an EDIT STRING clause of a column or domain definition, or the EDIT USING clause of a SELECT statement. The following table shows the valid equivalence names for the logical name RDB$CHARACTER_SET. Table 2 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. 1 SQLMOD72 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 Rdb 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   lqqqqqqqqqqqqqqqqqqqqqqqq PRAGMA (module-pragma-list) qj mqq> module-language-options qqj x  lqqqqqqqqqqqqqqqqqqqqqqqq procedure-clause qqwqqqqqq>   mwq> declare-statement qqwqj mqqqqqqqqqqq SQL99 qqqqqwq>  tqq> SQL92 qqqqqu   tqq> SQL89 qqqqqu   tqq> SQLV40 qqqqu   mqq> MIA qqqqqqqj    (B)0char-set-options =    qqwqqqqqqqqqqqqqqqq> qqqqqqqqqqqqqwqqqqqk   mqq> NAMES ARE names-char-set qqj x  lqqqqqqqqqqqqqqqqq qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>   x tqq> LITERAL CHARACTER SET support-char-set qqqqu x   x tqq> NATIONAL CHARACTER SET support-char-set qqqu x   x tqq> DEFAULT CHARACTER SET support-char-set qqqqu x   x tqq> IDENTIFIER CHARACTER SET names-char-set qqqu x  x mqq> DISPLAY CHARACTER SET names-char-set qqqqqqj x  mqqqqqqqqqqqqqqqqqqqqqqqqqqq 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-pragma-list =     qqqqqqqqqqqqwqqwq> DEC_ADA qqqqqqqqqqqqqqqwqqwqqqq> x  tq> GNAT_ADA qqqqqqqqqqqqqqu x x mq> IDENT string-literal qqqj x mqqqqqqqqqqqqqqqq ,  ALIAS <alias-name> qqqqqqqqqqqqqqqqqqqqqqqqwqwqq>  x tqqq> CHARACTER LENGTH qwq> CHARACTERS qwqqqqqqqqu x   x x mq> OCTETS qqqqqj x x   x tqqq> DEFAULT DATE FORMAT qqwqq> SQL99 qwqqqqqqqqu x   x x tqq> SQL92 qu x x  x x mqq> VMS qqqj x x  x tqqq> KEYWORD RULES environment qqqqqqqqqqqqqqqqqu x   x tqqq> PARAMETER COLONS qqqqqqqqqqqqqqqqqqqqqqqqqqu x   x tqqq> QUOTING RULES environment qqqqqqqqqqqqqqqqqu x   x tqqq> RIGHTS qqwqq> INVOKER qqqwqqqqqqqqqqqqqqqqqu x   x x mqq> RESTRICT qqj x x   x tqqq> VIEW UPDATE RULES environment qqqqqqqqqqqqqu x   x tqqq> QUIET COMMIT qwq> ON qqqwqqqqqqqqqqqqqqqqqqu x  x x mq> OFF qqj x x x mqqq> COMPOUND TRANSACTIONS  qwq> INTERNAL qwqqqqj x x  mq> EXTERNAL qj x mqqqqqqqqqqqqqqqqqqqqqqqqqqq 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 qqqwqqqqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqwq>  tqq> BIGINT qqqqqu mq> ( <n> ) qj   x   tqq> TINYINT qqqqj   x   tqq> INTEGER qqwqqqqqqqq qqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqu  x mq> ( <n> ) qj mq> IS qqwq> 4 qwq> BYTES qj x  x   mq> 8 qj x  tqq> DECIMAL qqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqj  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 =    qqwq> char-data-types qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   tq> TINYINT qqqqqqqqqqqqqqwqqqqqwqqqqqqqqqqwqqqqqqqqqqqu   tq> SMALLINT qqqqqqqqqqqqqu mq> (<n>) qj x   tq> BIGINT qqqqqqqqqqqqqqqu x   tq> LIST OF BYTE VARYING qj x   tq> INTEGER qwqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqu   x mq> (<n>) qj mq> IS qqwq> 4 qwq> BYTES qj x   x mq> 8 qj x   tq> DECIMAL qwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqu   tq> NUMERIC qjmq> ( qq> <n> wqqqqqqqqqqwq> ) j x   x mq> , <n> qj x   tq> FLOAT qwqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> (<n>) qj x  tq> NUMBER qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqu x mq> ( qwqq <p> qwqwqqqqqqqqqwq> ) qj x x mq> * qqqj mq> <d> qqj x  tq> REAL qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> DOUBLE PRECISION qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   mq> date-time-data-types qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  (B)0char-data-types =    qwq> CHAR qqqqqqqqqqqqqwwqqqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>  tq> CHARACTER qqqqqqqqumq> ( <n> ) qjmq> CHARACTER SET char-set-name qj x  tq> CHAR VARYING qqqqqu   x  tq> CHARACTER VARYING j x  tq> VARCHAR qqw> ( <n> ) qqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqu  tq> VARCHAR2 qj  mq> CHARACTER SET char-set-name qj  x  tq> LONG VARCHAR qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> NCHAR qqqqqqqqqqqqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> NATIONAL CHAR qqqqqqu mq> ( <n> ) qj    x   tq> NATIONAL CHARACTER qj    x   tq> NCHAR VARYING qqqqqqqqqqqqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqu  tq> NATIONAL CHAR VARYING qqqqqqu mq> ( <n> ) qj    x   tq> NATIONAL CHARACTER VARYING qj    x  tq> RAW q> ( <n> ) qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu mq> LONG qwqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj  mq> RAW qj (B)0date-time-data-types =    qqwq> DATE qwqqqqqqqqqqwqqqqqqqqqqqqqqqqqwqq>   x tq> ANSI qu x   x mq> VMS qqqj x   tq> TIME qqq> frac qqqqqqqqqqqqqqqqqqqqu   tq> TIMESTAMP qq> frac qqqqqqqqqqqqqqqqu   mq> INTERVAL qqq> interval-qualifier qqj    (B)0frac =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mqq> ( <numeric-literal> ) qj    (B)0interval-qualifier =    qqwq> YEAR qqq> prec qqwqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqwq>   x mq> TO MONTH qj x   tq> MONTH qq> prec qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> DAY qqqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO qwq> HOUR qqqqqqqqqqqqqqqu   x tq> MINUTE qqqqqqqqqqqqqu   x mq> SECOND q> frac qqqqqu   tq> HOUR qqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO qwq> MINUTE qqqqqqqqqqqqqu   x mq> SECOND q> frac qqqqqu   tq> MINUTE q> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   x mq> TO SECOND qqqqqq> frac qqqqqu   mq> SECOND q> seconds-prec qqqqqqqqqqqqqqqqqqqqqqqqqqj    (B)0prec =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mqq> ( <numeric-literal> ) qj    (B)0seconds-prec =    qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq>   mq> ( <numeric-literal-1> qqqk x   lqqqqqqqqqqqqqqqqqqqqqqqqqj x   mwqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qqj   m> , <numeric-literal-2> qqj    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 ANSI/ISO SQL99 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 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, SQL99 or MIA, the default is CHARACTERS. Otherwise, the default is OCTETS. 3 char-data-types Refer to the Oracle Rdb SQL Reference Manual for information about the character data types that SQL supports. 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 Rdb SQL Reference Manual for more complete information about a compound statement. 3 COMPOUND_TRANSACTIONS Syntax options: COMPOUND TRANSACTIONS INTERNAL COMPOUND TRANSACTIONS EXTERNAL Allows you to specify whether SQL should start a transaction before executing a compound statement or stored procedure. The COMPOUND TRANSACTIONS EXTERNAL clause instructs SQL to start a transaction before executing a procedure. The COMPOUND TRANSACTIONS INTERNAL clause instructs SQL to allow a procedure to start a transaction as required by the procedure execution. By default, SQL starts a transaction before executing a compound statement if there is no current transaction 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 Rdb 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 Rdb 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 Rdb 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 SQL 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 an SQL standard such as SQL99, both data types are interpreted as SQL format. The SQL 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 SQL99 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 and SQL99 dialects: 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 Rdb SQL Reference Manual for more information on the settings for each option of the DIALECT clause. 3 DISPLAY_CHARACTER_SET Specifies the character set used for automatic translation between applications and SQL. If you do not specify a character set the default is DEC_MCS. See the Oracle Rdb SQL Reference Manual for a list of allowable character sets. 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. 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). 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 Rdb 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 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, SQL99, 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. The following table shows those defaults. Table 3 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 Rdb 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 Rdb 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 Rdb 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. 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. 3 parameter-name The name for a formal parameter. Use any valid SQL name. See the Oracle Rdb 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 Rdb 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 QUIET_COMMIT Syntax options: QUIET COMMIT ON | QUIET COMMIT OFF The QUIET COMMIT ON clause disables error reporting for the COMMIT and ROLLBACK statements if either statement is executed when no transaction is active. The QUIET COMMIT OFF clause enables error reporting for the COMMIT and ROLLBACK statements if either statement is executed when no transaction is active: MODULE TXN_CONTROL LANGUAGE BASIC PARAMETER COLONS QUIET COMMIT ON PROCEDURE S_TXN (SQLCODE); SET TRANSACTION READ WRITE; PROCEDURE C_TXN (SQLCODE); COMMIT; The QUIET COMMIT OFF clause is the default. 3 QUOTING_RULES Controls whether double quotation marks are interpreted as string literals or delimited identifiers. If you specify SQL99, 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...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 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 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 SQLCA A formal parameter for the SQLCA (see the Oracle Rdb 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 Rdb SQL Reference Manual for more information about SQLCODE. 3 SQLDA_SQLDA2 A formal parameter for the SQLDA or SQLDA2 (see the Oracle Rdb 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, SQL99, 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. */ #include #pragma 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); void main () { int sqlcode = 0; NAME_TYPE name_record; LAST_NAME like_string = "T%"; SET_TRANS (&sqlcode); /* Start a read-only transaction. */ if (sqlcode != 0) /* Print any error information. */ printf ("SQL error code = %d\n", sqlcode); OPEN_CURSOR (&sqlcode, like_string); /* Open the cursor, supplying */ /* the string to match against. */ if (sqlcode != 0) /* Print any error information. */ printf ("SQL error code = %d\n", sqlcode); 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. */ if (sqlcode != 0) /* Print any error information. */ printf ("SQL error code = %d\n", sqlcode); ROLLBACK_TRANS (&sqlcode); /* Finish the transaction. */ if (sqlcode != 0) /* Print any error information. */ printf ("SQL error code = %d\n", sqlcode); } 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. The following table shows the OpenVMS data types that SQL requires for actual parameters when you declare formal parameters for each SQL data type. Table 4 SQL and Corresponding OpenVMS Data Types for Module Language Formal Parameter Data Type Requires Actual Parameter of OpenVMS Data Type CHAR (n) Character string (DSC$K_DTYPE_T) CHAR (n), Character string (DSC$K_DTYPE_T) qualified by character set NCHAR (n) Character string (DSC$K_DTYPE_T) VARCHAR (n) Varying character string (DSC$K_DTYPE_VT) VARCHAR (n), Varying character string (DSC$K_DTYPE_VT) qualified by character set NCHAR VARYING Varying character string (DSC$K_DTYPE_VT) (n) LONG VARCHAR Varying character string (DSC$K_DTYPE_VT) TINYINT [(n)] Signed byte integer (DSC$K_DTYPE_B) SMALLINT [(n)] Signed word integer (DSC$K_DTYPE_W) INTEGER [(n)] Signed longword integer (DSC$K_DTYPE_L) BIGINT [(n)] Signed quadword integer (DSC$K_DTYPE_Q) QUADWORD [(n)] Signed quadword integer (DSC$K_DTYPE_Q) DECIMAL Packed decimal string (DSC$K_DTYPE_P) [(n)[,(n)]] NUMERIC Numeric string, left separate sign (DSC$K_DTYPE_ [(n)[,(n)]] NL) FLOAT [(n)] Single- or double-precision, floating-point number, depending on n. For single-precision: DSC$K_DTYPE_F or DSC$K_DTYPE_FS and for double- precision: DSC$K_DTYPE_G, DSC$K_DTYPE_D, or DSC$K_DTYPE_FT. REAL Single-precision, floating-point number (DSC$K_ DTYPE_F or DSC$K_DTYPE_FS). DOUBLE Double-precision, floating-point number (DSC$K_ PRECISION DTYPE_G, DSC$K_DTYPE_D, or DSC$K_DTYPE_FT). (DATE) No equivalent OpenVMS data type; two-longword array DATE ANSI No equivalent OpenVMS data type; two-longword array DATE VMS Absolute date and time (DSC$K_DTYPE_ADT) TIME No equivalent OpenVMS data type; two-longword array TIMESTAMP No equivalent OpenVMS data type; two-longword array INTERVAL (Year- No equivalent OpenVMS data type; two-longword month) array INTERVAL (Day- No equivalent OpenVMS data type; two-longword time) array LIST OF BYTE Not supported VARYING 2 SQLMOD_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> /ARCHITECTURE = architecture_options qqux  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> /FLOAT = qwq> D_FLOAT qqqqqqqqqqqqqqqqqux xx   tq> G_FLOAT qqqqqqqqqqqqqqqqqux xx mq> IEEE_FLOAT qqqqqqqqqqqqqux  xtq> /USER_DEFAULT = qqqqqqqqqqqqux   xmq> /PASSWORD_DEFAULT = qqqqqqqqjx   mqqqqqqqqqqqqqqqqqqqqq / wqqqqwqwq> ALIGN_RECORDS qqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>  m NO j tq> C_PROTOTYPES qqwqqqqqqqqqqqqqqqqqqqqqwqqqqu x mq> = <file-name> qqj x  tq> CONNECT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> EXTERNAL_GLOBALS qqqqqqqqqqqqqqqqqqqqqqqqqu   tq> FLAG_NONSTANDARD qqwqqqqqqqqqqqqqqqqqqqwu   x tq> = MIA qqqqqqqqqux   x tq> = SQL89 qqqqqqqux   x mq> = SQL92_ENTRY qjx   tq> G_FLOAT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   tq> INITIALIZE_HANDLES qqqqqqqqqqqqqqqqqqqqqqqu   tq> LIST qqqqwqqqqqqq>qqqqqqqqqwqqqqqqqqqqqqqqu   x mq> = <file-spec> j x  tq> LOWERCASE_PROCEDURE_NAMES qqqqqqqqqqqqqqqqu  mq> MACHINE_CODE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj    (B)0no-qualifiers-2 =    q> / wq>qqwqwq> OBJECT qqwqqqqqqq>qqqqqqqqqwqqqqqqqqqqqqqqqqwqqq>  m NO j x mq> = <file-spec> j   x   tq> PACKAGE_COMPILATION qqqqqqqqqqqqqqqqqqqqqqqqu   tq> PARAMETER_CHECK qqqqqqqqqqqqqqqqqqqqqqqqqqqu  tq> PRAGMA = ( q> IDENT = string-literal q> ) qqu tq> PROTOTYPES qqwqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqu x mq> = <prototypesfile> qqj x  tq> QUERY_ESTIMATES qqqqqqqqqqqqqqqqqqqqqqqqqqqu  tq> QUIET_COMMIT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu  tq> TRANSACTION_DEFAULT qwqqqqqqqqqqqqqqqqqqwqqqu   x tq> = IMPLICIT qqqqu   x   x mq> = DISTRIBUTED qj   x   mq> WARN qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqj     m> = ( w> warning-option w> ) qqj    mqqqqqq , WARNING qqqqqqwqqwq>   x tq> NOWARNING qqqqu x   x tq> DEPRECATE qqqu x   x mq> NODEPRECATE qj x   mqqqqqqqqq , GENERIC qwqq>  tq> HOST qqu   tq> EV4 qqu   tq> EV5 qqu   tq> EV56 qqu  tq> PCA56 qqu tq> EV6  qqu tq> EV67 qqu tq> EV68 qqu mq> EV7 qqj       (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)0module-qualifiers-2 =    qqqqwwq> /database-options qqqqqqqqqqqqqqqqqqqqqqqwwq>   xtq> /optimization_options qqqqqqqqqqqqqqqqqqqux   xtq> /QUERY_TIME_LIMIT = qqqqqux   xtq> /QUERY_MAX_ROWS = qqqqqqqqqqux   xtq> /QUERY_CPU_TIME_LIMIT = qux   xmq> /ROLLBACK_ON_EXIT qqqqqqqqqqqqqqqqqqqqqqqjx   mqqqqqqqqqqqqqqqqqqqqq OPTIMIZATION_LEVEL= qwqqqq> DEFAULT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>  mqq> ( qwwq> AGGRESSIVE_SELECTIVITY qqwqqwq ) qj   xtq> FAST_FIRST  qqu x     xtq> SAMPLED_SELECTIVITY qqqqu x     xmq> TOTAL_TIME  qqqqqqqqqqqqj x   mqqqqqq , ELN qqqqqqqqqqqqqqqqqqqqqwqq>   tqq> NSDS qqqqqqqqqqqqqqqqqqqqu   tqq> rdb-options qqqqqqqqqqqqqu   tqq> VIDA qqqqqqqqqqqqqqqqqqqqu   tqq> VIDA=V1 qqqqqqqqqqqqqqqqqu   tqq> VIDA=V2 qqqqqqqqqqqqqqqqqu   tqq> VIDA=V2N qqqqqqqqqqqqqqqqu   tqq> NOVIDA qqqqqqqqqqqqqqqqqqu   tqq> DBIV1 qqqqqqqqqqqqqqqqqqqu   tqq> DBIV31 qqqqqqqqqqqqqqqqqqu   mqq> DBIV70 qqqqqqqqqqqqqqqqqqj    (B)0 rdb-options =    qwq> RDBVMS qqwqq>  tq> RDB030 qqu   tq> RDB031 qqu   tq> RDB040 qqu   tq> RDB041 qqu  tq> RDB042 qqu tq> RDB050 qqu tq> RDB051 qqu tq> RDB060 qqu tq> RDB061 qqu tq> RDB070 qqu mq> RDB071 qqj (B)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 Rdb 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 SQLPRE72 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 =     qqwqqqqqqqqqqqqqqqqqqqqqqqwq> BEGIN qqwqqqqqqqqqqqqqqqqqqqqwqqqk  mq> <beginning-label>: qj mq> pragma-clauses qqj x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq variable-declaration qwj x   mqqqqqqqqqqqqqqqqqqqqqqqqqqj x   lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj   mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> END qqwqqqqqqqqqqqqqqqqqqqqwqq>  mwq>  compound-use-statement qwj mq> <ending-label>: qj  mqqqqqqqqqq pragma-option qqwqqqqqqqqqqqqqqqqqqwqqq> x mqqqqqqq PRAGMA ( qwq> pragma-option qwq> ) qj mqqqqqqqq , ATOMIC qqqqqqqqqqqqqqqqqqqqqqwqq> tq> NOT ATOMIC qqqqqqqqqqqqqqqqqqu tq> ON ALIAS <alias-name> qqqqqqqu tq> with-clause qqqqqqqqqqqqqqqqqu mq> optimize-clause qqqqqqqqqqqqqj   (B)0with-clause =    qqq> WITH qq> HOLD qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>   mq> PRESERVE qqwq> ON COMMIT qqqu   tq> ON ROLLBACK qu   tq> ALL qqqqqqqqqu   mq> NONE qqqqqqqqj    (B)0optimize-clause =            qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq>   mq> OPTIMIZE qqwqwq> FOR qwq> FAST FIRST qqqqqqqqwqqqqqqqqqqwqwqj   x x tq> TOTAL TIME qqqqqqqqu   x x  x x mq> SEQUENTIAL ACCESS qj x x  x tq> USING <outline-name> qqqqqqqqqqqqqqqqqqu x  x tq> WITH qwq> DEFAULT qqwq> SELECTIVITY qu x x x tq> SAMPLED qqu x x x x mq> AGGRESSIVE j x x  x mq> AS <query-name> qqqqqqqqqqqqqqqqqqqqqqqj x   mqqqqqqqqqqqqqqqq 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> / ARCHITECTURE= architecture_options qqqqqqqqqqqqqqwwqq>   xtq> / qwqqqqqqwqqwq> ANSI_FORMAT qqqqqqqqqqqqqqqqqqqqqqux    xx mq NO qj tq> EXTEND_SOURCE qqqqqqqqqqqqqqqqqqqqux   xx tq> G_FLOAT qqqqqqqqqqqqqqqqqqqqqqqqqqux   xx tq> LIST qqwqqqqqqqqqqqqqqqqqqwqqqqqqqux  xx x mq> = <file-spec> qj xx  xx tq> MACHINE_CODE qqqqqqqqqqqqqqqqqqqqqux   xx mq> OBJECT qwqqqqqqqqqqqqqqqqqqwqqqqqqux   xx  mq> = <file-spec> qj xx  xtq> / FLOAT= qwq> D_FLOAT qqqwqqqqqqqqqqqqqqqqqqqqqqqux xx tq> G_FLOAT qqqqu xx xx mq> IEEE_FLOAT qqj xx xx xx  xmq> / SQLOPTIONS= ( qwwq> opt-no-qualifiers qwwq> ) qqqjx   x xmq> opt-qualifiers qqqqjx x   x mqqqqqqqqq , GENERIC qwqq>  tq> HOST qqu   tq> EV4 qqu   tq> EV5 qqu   tq> EV56 qqu  tq> PCA56 qqu tq> EV6  qqu tq> EV67 qqu tq> EV68 qqu mq> EV7 qqj       (B)0opt-no-qualifiers =    qwqqqqqqqqwqwq> 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> PRAGMA = ( qq> IDENT = string-literal q> ) qu tq> QUERY_EXTIMATES qqqqqqqqqqqqqqqqqqqqqqqqqqqqu  tq> QUIET_COMMIT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu   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)0 rdb-options =    qwq> RDBVMS qqwqq>  tq> RDB030 qqu   tq> RDB031 qqu   tq> RDB040 qqu   tq> RDB041 qqu  tq> RDB042 qqu tq> RDB050 qqu tq> RDB051 qqu tq> RDB060 qqu tq> RDB061 qqu tq> RDB070 qqu mq> RDB071 qqj (B)0       optimization-options=  qqqq> OPTIMIZATION_LEVEL= qwqqqq> DEFAULT qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>  mqq> ( qwwq> AGGRESSIVE_SELECTIVITY qqwqqwq ) qj   xtq> FAST_FIRST  qqu x     xtq> SAMPLED_SELECTIVITY qqqqu x     xmq> TOTAL_TIME  qqqqqqqqqqqqj x   mqqqqqq , int16 SMALLINT Requires #include __int16SMALLINT int32 INTEGER Requires #include __int32INTEGER int64 BIGINT Requires #include __int64BIGINT $SQL_VARCHAR (n) The CHARACTER SET clause is optional. SQL_DATE The SQL precompiler will transform the SQL_DATE_ANSI pseudo types in natvie C datatypes SQL_DATE_VMS SQL_TIME SQL_TIMESTAMP SQL_INTERVAL (DAY Use this data type for variables that TO SECOND) represent the difference between two dates or times. Precompiler Date-Time Data Mapping lists all the supported INTERVAL data types. 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 Rdb 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 Date- Time Data 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 Rdb SQL Reference Manual. The following list describes the variable declaration syntax that the SQL precompiler supports in FORTRAN: o Declarations - See the following table 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. Table 8 Supported FORTRAN Datatypes FORTRAN type SQL type Comments and Restrictions BYTE TINYINT CHARACTER*n CHAR The n represents a positive integer literal INTEGER INTEGER INTEGER*1 TINYINT INTEGER*2 SMALLINT INTEGER*4 INTEGER INTEGER*8 BIGINT LOGICAL INTEGER LOGICAL*1 TINYINT LOGICAL*2 SMALLINT LOGICAL*4 INTEGER LOGICAL*8 BIGINT REAL REAL REAL*4 REAL REAL*8 DOUBLE PRECISION STRUCTURE VARCHAR The named structure can be used /name/ to define other FORTRAN host integer*2 variables. The len component of len the structure must be set to the character*n correct length of the string before body use as a parameter to SQL. The END n represents a positive integer STRUCTURE literal SQL_DATE The SQL precompiler will transform SQL_DATE_ the pseudo types in native FORTRAN ANSI datatypes. SQL_DATE_VMS SQL_TIME SQL_ TIMESTAMP SQL_INTERVAL Use this data type for variables (DAY TO that represent the difference SECOND) between two dates or times. Precompiler Date-Time Data Mapping lists all the supported INTERVAL data types. 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 - F_FLOAT - D_FLOAT - G_FLOAT - S_FLOAT - T_FLOAT - CHAR - PACKED ARRAY [1..n] OF CHAR; - VARYING [u] OF CHAR - [BYTE] -128..127; - [WORD] -32768..32767; - Date-time data types (Precompiler Date-Time Data 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 Date- Time Data 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 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 Date-Time Data 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 Date- Time Data Mapping lists all the supported INTERVAL data types.) - DECIMAL data type is converted to FIXED - NUMERIC data type is converted to PACKED 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.