1 User_Supplied_Names You must supply names (identifiers) to satisfy the syntax of SQL statements that require user-supplied names. In statement syntax diagrams, user-supplied names are shown in lowercase type. User-supplied names must: o Be no more than 31 octets (8-bit characters). o Conform to one of the following rules: - If the identifier character set is MCS, the name must contain only alphanumeric characters and begin with an uppercase or lowercase letter. Alphanumeric characters are uppercase or lowercase letters (A, a), including letters with diacritical marks (À), digits, dollar signs ($), and underscores (_). Uppercase and lowercase letters are treated equally. Although dollar signs are valid characters in names, to avoid conflicts it is recommended that you do not use them. You cannot begin a user-supplied name with a numeric character. - If the identifier character set is other than DEC_MCS, it can contain only a valid sequence of characters as defined by the standard for that character set. - The name can be a delimited identifier. A delimited identifier is a user-supplied name enclosed in double quotation marks ("). It can start with and contain alphanumeric characters, special characters, control characters, and spaces. The quotation mark (") character can be included in a delimited identifier by typing two quotation marks together (see the following example). Trailing spaces are not significant. See the Oracle Rdb SQL Reference Manual for a list of special characters. The alphabetic characters can be uppercase or lowercase. The following example shows many variations of delimited identifiers: SQL> SET QUOTING RULES 'SQL99 SQL> CREATE TABLE "This is a Test" cont> ("""" CHAR(5), cont> "_NAME" CHAR(20), cont> " City" CHAR(20), cont> "1st_date" DATE, cont> "A ""B and C""" CHAR(10), cont> "$_Amount" INT, cont> """Test" CHAR(5) cont> ); SQL> SHOW TABLE (COLUMN) "This is a Test"; Information for table This is a Test Columns for table This is a Test: Column Name Data Type Domain ----------- --------- ------ " CHAR(5) _NAME CHAR(20) City CHAR(20) 1st_date DATE VMS A "B and C" CHAR(10) $_Amount INTEGER "Test CHAR(5) If you use a delimited identifier, SQL maintains the case of the identifier. That is, if you enclose the identifier Employee_ID in quotation marks ("Employee_ID"), SQL preserves the uppercase and lowercase letters. The delimited identifiers "Employee_ID", "EMPLOYEE_ID", and "employee_id" are distinct from each other. You must enable ANSI/ISO SQL quoting when using delimited identifiers. If you want to use a keyword as a user-supplied name, you must set the quoting rules or dialect to SQL99 and use the delimited identifier. SQL uses the identifier character set as the character set for database object names. However, because SQL interprets the names of some database objects as file names or path names, you must use only ASCII alphanumeric characters for the names of the following database objects: o Database file name o Database path name o Snapshot files o Storage areas o Journal files o Alias names 2 Aliases An alias is a name for a particular attachment to a database. Explicitly specifying an alias lets your program or interactive SQL statements refer to more than one database. Once you specified the alias, you must use it when referring to the database in subsequent SQL statements (unless those statements are within a CREATE DATABASE statement). You must use an alias when you declare more than one database so that SQL knows the database to which your statements refer. When you issue an ATTACH, CONNECT, CREATE DATABASE, CREATE DOMAIN, CREATE TABLE, DECLARE ALIAS, GRANT, GRANT (ANSI-style), IMPORT, REVOKE, or SET TRANSACTION statement, you can specify an alias in addition to a file specification or a repository path name. SQL allows you to specify an alias that declares the database as the default database. Specifying a default database means that subsequent statements that refer to the default database during the database attachment do not need to use an alias. 2 Authorization_Identifiers SQL uses an authorization identifier in a stored or nonstored module to convey to Oracle Rdb the concept of a user. These modules can be either definer's rights or invoker's rights. 3 Stored_Modules A stored module resides in the database as an object. You can store modules and their procedures and functions with the CREATE MODULE statement. The authorization identifier, specified by using the AUTHORIZATION clause, enables Oracle Rdb to identify the user under whom the module executes. When you specify an authorization identifier in the definition of a stored module, that stored module is called a definer's rights module. This type of module enables any user who has EXECUTE privilege on the module to execute any of the module's routines without privileges on any of the underlying schema objects that the routine references. The routines execute under the rights identifier of the module definer, not the rights identifier of the person executing the routine. This ability to allow users access to schema objects through a call to a stored routine without having direct access to those schema objects is a key benefit of stored modules. In contrast, when you omit the AUTHORIZATION clause in the definition of a stored module, that stored module is called an invoker's rights module. In this type of module, users who have EXECUTE privilege on a particular module must also have privileges to all the underlying schema objects associated with any of the routines in this module that they want to execute. The following examples relate to stored modules and procedures. Authorization and CURRENT_USER are handled the same for both types of stored routines. Consider the following stored module definition, Module M1 with Procedure P1 and Authorization Brown. For example: CREATE MODULE M1 LANGUAGE SQL AUTHORIZATION BROWN PROCEDURE P1 (); BEGIN TRACE CURRENT_USER; CALL P2 (); END; END MODULE; As you can see in the preceding example, P1 calls another stored procedure, P2. Procedure P2 is defined in Module M2 as the following example shows: CREATE MODULE M2 LANGUAGE SQL -- no authorization PROCEDURE P2 (); BEGIN TRACE CURRENT_USER; CALL P3 (); END; END MODULE; Procedure P2 calls another procedure, P3, from Module M3, which is shown in the following example: CREATE MODULE M3 LANGUAGE SQL -- no authorization PROCEDURE P3 (); BEGIN TRACE CURRENT_USER; . . . END; END MODULE; In each procedure you can trace the CURRENT_USER. Assume the following: o Smith is granted the EXECUTE privilege on Module M1; but not on M2 or on M3. o Brown is granted the EXECUTE privilege on Modules M1, M2, and M3. o Jones is granted the EXECUTE privilege on Modules M1 and M2. When P1 is executed, CURRENT_USER always returns Brown as defined by the AUTHORIZATION clause in Module M1. When P2 or P3 are executed, the CURRENT_USER is either: o Inherited from the calling routines AUTHORIZATION clause, or o The CURRENT_USER of the calling routine if no authorization was specified When there is no AUTHORIZATION clause for the first calling routine, then CURRENT_USER is inherited from the SESSION_USER. 3 Nonstored_Modules A nonstored module resides outside the database in an SQL module file. The AUTHORIZATION clause specifies the authorization identifier for the module. If you omit the authorization identifier, SQL selects the user name of the user compiling the module as the default authorization. Thus, if you use the RIGHTS clause, SQL compares the user name of the person who executes a module with the authorization identifier with which the module was compiled and prevents any user other than the one who compiled that module from invoking that module. When you use the RIGHTS clause, SQL bases privilege checking on the default authorization identifier in compliance with the ANSI/ISO standard. 2 Catalog_Names If you include the MULTISCHEMA IS ON clause in your CREATE DATABASE statement, you can store your metadata in multiple schemas. A database with multiple schemas must organize them within catalogs. A catalog is a group of schemas within one database. You name catalogs in CREATE CATALOG or CREATE DATABASE statements. You can also use catalog names to qualify the names of other database elements such as schemas, tables, and views. NOTE In syntax diagrams, the column-name syntax element refers to either the qualified or unqualified form of the name given to the catalog in the CREATE statement. That is, in syntax diagrams, the catalog-name is always defined as: catalog-name = -+------> --------------+-> | | +-> " -> . ->" -+ In each multischema database, SQL creates a catalog named RDB$CATALOG. SQL stores all schemas in RDB$CATALOG by default. A multischema database must contain at least one catalog, although you can create more than one catalog for each database. To store a schema in a catalog other than RDB$CATALOG, qualify the schema name with the other catalog's name in the CREATE SCHEMA statement, or use the SET CATALOG statement to change the default catalog before issuing a CREATE SCHEMA statement. 2 Column_Names You name columns in CREATE TABLE and ALTER TABLE statements. In other SQL statements, the names you give to columns in CREATE and ALTER statements can be qualified by table names, view names, or correlation names. NOTE In syntax diagrams, the column-name syntax element refers to either the qualified or unqualified form of the name given to the column in the CREATE TABLE or ALTER TABLE statement. That is, in syntax diagrams, column-name is always defined as: column-name = ---+----------->-----------------+-> --> +-> -------+> . -+ +-> --------+ +-> -+ The only time you must qualify column names is when they are ambiguous. Joining a table with itself and joining two tables with common column names are two cases that require qualified column names. Also, if you have a parameter without a colon with the same name as a column, you need to qualify references to that column. 3 Correlation_Names In addition to qualifying column names with table names or view names, you can qualify column names with correlation names. Correlation names are analogous to aliases, but they refer to tables instead of databases. Just as aliases provide temporary names for databases to qualify ambiguous table names, correlation names give temporary names to tables to qualify ambiguous column names. Specify a correlation name after a table name within the FROM clause of a select expression or DELETE statement, or in an UPDATE statement. Use any valid name that has not already been used in the FROM clause either as a correlation name or as a table name without a correlation name. You must use correlation names to qualify column names in statements that join a table with itself. As with table names and view names, however, you can always specify a correlation name for clarity 3 Outer_References You may have to qualify column names in an outer reference. An outer reference is a reference within a subquery to a table specified in an outer query that contains the subquery. An outer reference is also called a correlated reference. For example, the previous example that retrieved the names of employees who worked in the marketing department can be reformulated to use an outer reference. SQL> SELECT FIRST_NAME, -- cont> LAST_NAME -- cont> FROM EMPLOYEES -- cont> WHERE 'MKTG' IN -- cont> (SELECT DEPARTMENT_CODE -- -- Outer cont> FROM JOB_HISTORY -- -- Query cont> WHERE JOB_END IS NULL -- Sub- -- cont> AND -- query -- cont> EMPLOYEE_ID = cont> EMPLOYEES.EMPLOYEE_ID) -- -- cont> -- --------------------- cont> -- outer reference cont> ; FIRST_NAME LAST_NAME Chris Danzig Lawrence Hall Paul Belliveau 3 rows selected If the outer reference to EMPLOYEE_ID in this example were not qualified by the table name EMPLOYEES, it would refer to the EMPLOYEE_ID column in the subquery, not the outer query. The predicate EMPLOYEE_ID = EMPLOYEE_ID is true for all values of EMPLOYEE_ID that are not null, so the statement would not generate an error, but would give unexpected results. Instead of the three marketing employees, it would select all rows of the EMPLOYEES table with values in the EMPLOYEE_ID column that were not null. Although the outer reference is contained within a subquery, it receives its value from an outer query. Because of this, the subquery must be evaluated once for each value that the outer reference receives from the outer query. It is this characteristic that defines an outer reference. 2 Connection_Names When your application attaches to one or more databases, SQL associates the databases with a set of aliases (database handles). In CONNECT, DISCONNECT, or SET CONNECT statements, you refer to this association as the connection name. You can specify the connection name as a parameter marker from dynamic SQL, a host language variable from a precompiled SQL program, a parameter from an SQL module language module, or a string literal. The set of databases that you can attach or detach as one unit is called the database environment. Within an application, all of the databases declared in all the modules form the default database environment for that application at run time. For more information about connections, see the CONNECT. 2 Constraint_Names A constraint defines a condition that restricts the values that can be stored in a table. When you insert and update column values, the constraint checks the values against the conditions specified by the constraint. If a value violates the constraint, SQL generates an error message and the statement fails (either when the INSERT, UPDATE, or DELETE statement executes, or when the next COMMIT statement executes depending on when SQL evaluates the constraint). You specify constraints in CREATE and ALTER TABLE statements. Optionally, you supply a name for the constraints following the CONSTRAINT keyword. 2 Cursor_Names Cursors provide access to individual rows of a result table. A result table is a temporary collection of columns and rows from one or more tables or views. For cursors, the result table is specified by the select expression in the DECLARE CURSOR statement. Unlike other result tables, the result table for a cursor can exist throughout execution of more than one statement. Host language programs require cursors because programs must perform operations one row at a time, and therefore can execute statements more than once to process an entire result table. You name the result table for a cursor in the DECLARE CURSOR statement and refer to that name in OPEN, CLOSE, FETCH, UPDATE, and DELETE statements. You cannot qualify cursor names. 2 Database_Names A database consists of physical data storage characteristics, such as a root file and storage area specifications; metadata definitions, such as tables and domains; and user data. By default, a database contains a single schema and no catalogs. If you specify the multischema attribute when creating your database, you can group the data definitions within one or more schemas within one or more catalogs. See the CREATE DATABASE for information on how to create a multischema database. When you create a database, you name it by specifying a file name and an optional repository path name in the CREATE DATABASE statement. You can supply a complete file specification, a partial file specification, or use system-supplied default values. You must use ASCII alphanumeric characters for the database name. To perform operations on a database, the database name is referenced through an attachment to that database called an alias. When you first refer to a database in SQL, you must indicate the source of data definitions for the database and the location of database files by declaring an alias. You can declare an alias using one of three statements: o ATTACH o CONNECT o DECLARE ALIAS Choose a statement based on the interface that you are using (interactive SQL, SQL module language, or precompiled SQL) and your purpose (declaring a new alias or overriding the association between an alias and a database name). More information about aliases appears in Aliases. There are two ways to identify the source of data definitions: o With a file specification o With a repository path name (if the repository is installed on the system) 3 Oracle_Rdb_Attach_Specifications When you first create a database, you give file specifications for the files that contain all database definitions (metadata) and user data stored in the database. You must use ASCII alphanumeric characters for the file specification name. You can also use a file specification whenever you refer to a database in the CONNECT and DECLARE ALIAS statements, although Oracle Rdb recommends that you always use a repository path name when the repository is installed. A full file specification includes: o Network node name o Device name o Directory name or list o File name o File extension o File version number Note that if you are specifying a database name for a remote database, all logical names/file specifications referenced will be evaluated on the remote node, not on the local node. Therefore, the necessary logical names/file specifications must exist on the remote node. 3 Repository_Path_Names Unless you use the PATHNAME argument in the CREATE DATABASE statement, SQL does not use the repository to store data definitions. If you specify the PATHNAME argument when you first create a database, SQL creates a path name that contains copies of data definitions for the database. Because SQL treats a path name like a string literal, you must enclose a path name in single quotation marks. You must use ASCII alphanumeric characters for the repository path name. When you issue an ATTACH or a DECLARE ALIAS statement, you can either specify the repository path name for that database (which in turn points to the physical database files) or directly name the physical database file specification. If you do not use the PATHNAME argument in the CREATE DATABASE statement, you cannot specify a path name in ATTACH or DECLARE ALIAS statements for that database unless you first issue an INTEGRATE statement. Oracle Rdb recommends that you always use a repository path name in CREATE DATABASE, ATTACH, and DECLARE ALIAS statements, and that you use the DICTIONARY IS REQUIRED clause to ensure that the two copies are the same. A repository path name can be a: o Full path name, such as CDD$TOP.ELLINGSWORTH.SQL.PERSONNEL o Relative path name A relative path name consists of the portion of the full path name that follows the current default repository node. For example, assume that you used the SET DICTIONARY command to set the current repository directory to CDD$TOP.ELLINGSWORTH.SQL. Now you can use the relative path name PERSONNEL in place of the full path name CDD$TOP.ELLINGSWORTH.SQL.PERSONNEL. By default, SQL sets the current repository node to the path name defined by the CDD$DEFAULT logical name. See the SET for the description of the SET DICTIONARY statement. See also Using Oracle CDD/Repository on OpenVMS Systems for more detail on repository path names. o Logical name for a full or relative path name Some Oracle Rdb features are not fully supported by all versions of the repository. If you attach by path name and attempt to create, modify, or delete objects not fully supported by the repository, you may receive an error or informational message. See the Oracle Rdb Release Notes for information about compatibility of Oracle Rdb features with the different versions of the repository. 2 Domain_Names A domain is the set of values that a table column can have. A domain definition restricts the set of values that a table column can have by associating a data type with a domain name, and allows optional formatting and collating clauses. The CREATE and ALTER TABLE statements refer to domain names in column definitions. The domain name must be unique among domain names in the schema. You can use a domain when defining columns in multiple tables. Once you have defined a domain, use the CREATE or ALTER TABLE statement to define a column based on the domain definition. You can qualify the domain name with the schema name (when the domain belongs to a multischema database) or with the alias. In general, you should use domains when you create tables. Using domains: o Ensures that similar columns in multiple tables comply to one standard. For example, if you define the columns using the domain ID_DOM, the data type for all these columns is CHAR(5). o Allows you to change the data type for all columns defined using a domain by changing the domain itself. For example, if you want to change the data type for POSTAL_CODE_DOM from CHAR(5) to CHAR(10), you only need to alter the data type for POSTAL_CODE_DOM. You do not have to alter the data type for the column POSTAL_CODE in the tables COLLEGES and EMPLOYEES. You might not want to use domains when you create tables if: o You are creating intermediate result tables. It takes time to plan what the domains are in the database and to define them. Intermediate result tables might not warrant this effort. NOTE In syntax diagrams, the domain-name syntax element refers to either the qualified or unqualified form of the name given to the domain in the CREATE DOMAIN statement. domain-name = -+---------------------------+-> ---> +-+-> -+-> . -+ +-> -------+ 2 Index_Names You name indexes in the CREATE INDEX statement. In CREATE INDEX and other SQL statements, the names you give to indexes can be qualified by authorization identifiers. NOTE In syntax diagrams, the index-name syntax element refers to either the qualified or unqualified form of the name given to the index in the CREATE INDEX statement. index-name = ---+----------------------------+-> ---> +---> -+--> . -+ +---> -------+ 2 Multischema_Databases If you specify the multischema attribute for your database, you can store data definitions in multiple schemas within that database. To specify the multischema attribute, use the MULTISCHEMA IS ON clause in a CREATE DATABASE or ALTER DATABASE statement. If you want SQL behavior compliant with the ANSI/ISO standard, you must specify the multischema attribute. Databases that contain multiple schemas must organize the schemas within one or more catalogs. To refer to data definitions in a multischema database, qualify the names of data definitions with the schema and catalog names and, optionally, qualify with an alias. When you use an alias to qualify the name of a catalog, schema, or object in a multischema database: o Separate subordinate names from the alias and from each other with a period (.) after each name. o Use double quotation marks (") to delimit the leftmost name pair. o Use only uppercase characters in the leftmost name pair. The leftmost name pair in a qualified name for a multischema object is a delimited identifier. In an object name, each qualifying name is considered one level, and names with more than three levels are not allowed. However, a delimited identifier is interpreted as a single level. 2 Nonstored_Names 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 module contains SQL statements that can be called from any host language, including those not supported by the SQL precompiler. The file contains module language elements, including the following user-supplied names: o Module name You supply a module name after the MODULE keyword at the beginning of an SQL module. If you do not supply a module name, SQL names the module SQL_MODULE. Module names must be unique. The following error is returned if a nonstored module is invoked while a stored module with the same name is active: %RDB-E-EXT_ERR, Rdb extension error -RDMS-E-MODEXTS, there is another module named SALARY_ROUTINES in this database o Procedure name Every SQL module contains one or more procedures consisting of a procedure name, one or more actual parameter declarations, and a single executable SQL statement. You must supply a name for each procedure after the PROCEDURE keyword. o Parameter name Actual parameters within a procedure in an SQL module specify a name to be used for the parameter by the SQL statement in the procedure. Some special-purpose procedure parameters are SQL keywords (SQLCODE, SQLCA, SQLDA, SQLSTATE), but you must give names to all other parameters in SQL modules. 2 Parameters_and_Variables Parameters, routine parameters, and SQL variables are often used in value expressions (for information on value expression, see the Value_Expressions HELP topic. A variable is an identifier that represents a value that can change during the execution of a program. You can use SQL variables in multistatement procedures. A routine parameter is a variable associated with a parameter of a routine that is used in a stored routine or an external routine. A stored routine refers to both stored procedures and stored functions defined using the CREATE MODULE statement. An external routine refers to both external procedures and external functions defined using the CREATE PROCEDURE and CREATE FUNCTION statements. When you use SQL variables in multistatement procedures or when you use routine parameters, you do not use indicator variables. See the Multistatement_Variables_and_Stored_Routines HELP topic for more information about SQL variables in multistatement procedures and stored routine parameters. See the External_ Routine_Parameters HELP topic for more information about external routine parameters. A parameter is an identifier declared in a host language program that is associated with an SQL statement. A parameter represents values that can change during the execution of a program. Many SQL data manipulation clauses that do not accept general value expressions require parameters. However, you cannot use parameters in data definition language statements. You can use parameters in the following places: o Interactive SQL In interactive SQL, you use the DECLARE Variable statement to declare the parameter. For more information about declaring parameters and variables, see DECLARE Variable. o SQL module language In programs that call SQL module procedures containing SQL statements, references to host language variables by SQL statements are indirect. The variable declared in the program is specified as a parameter in a host language call statement to a procedure in the SQL module. Parameters in such call statements are called actual parameters. In nonstored procedures, the SQL module procedure contains parameter declarations that correspond to the actual parameters in the calling program. Module parameters in those declarations are called formal parameters. The SQL statement in the module procedure uses the formal parameter name to refer indirectly to the actual parameter named in the host language call to the module procedure. o Precompiled SQL In precompiled programs, SQL statements embedded in the program refer directly to the host language variable using it in the statement. The SQL precompiler supports only a subset of the declaration syntax for host languages. See the Oracle Rdb SQL Reference Manual for more information. You can only use parameter names that conform to the rules of the host language. o Dynamic SQL In dynamic SQL, dynamically executed SQL statements refer to parameters with parameter markers denoted by a question mark (?) in the statement string of PREPARE statements. SQL statements use parameters for the following purposes: o SQL retrieves data from the database and places it in parameters for use by a program. o Parameters contain data generated by a program that SQL uses to update the database. o Data manipulation statements can specify parameters in value expressions. o Special-purpose parameters called indicator parameters indicate whether or not the value stored in a corresponding main parameter is null. (Indicator parameters are not used in stored routines.) o SQL puts information about the success or failure of SQL statements in a parameter called SQLCODE that is either declared explicitly or as part of the SQL Communications Area (SQLCA) or in the SQLSTATE status parameter (ANSI/ISO SQL standard). See SQLCA for more information on SQLCODE and SQLSTATE. o SQL and programs use a collection of parameters called the SQL Descriptor Areas (SQLDA and SQLDA2) to communicate information about dynamic SQL statements. See SQLDA for more information. SQL statements cannot use parameters to refer to columns, tables, or views. For instance, if BADVAR is a host language variable that contains the name of a table in the database, the following statement is invalid: EXEC SQL SELECT FIRST_NAME INTO :GOODVAR FROM :BADVAR END-EXEC When you use the precompiler, module language, or dynamic SQL, display operations should use CAST or EXTRACT with CHAR host variables to convert date-time data from binary format when passing data to and from the database. For example: EXEC SQL SELECT CAST(TBL_INT_H3 AS CHAR(4)) INTO :string_var3 FROM ALL_DATE_TABLE; For more information about the CAST and EXTRACT functions, see the Built_In_Functions HELP topic. 3 Data_and_Indicator_Parameters A data parameter contains the value that an SQL statement stores in, retrieves from, or compares to a column in the database. An indicator parameter specifies whether or not its associated data parameter was assigned a null value. You specify an indicator parameter after the data parameter. As for data parameters, the notation for referring to indicator parameters depends on the environment in which an SQL statement is issued. parameter = --> : -+----------------------------+-> ++---------------+---------+ | +-> INDICATOR --+ | | +--------------------------+ | +-> : -+ o If you set the dialect to SQL99 or another dialect that enforces the use of parameters or if you use a clause, such as PARAMETER COLONS, that enforces the use of parameters, all parameter names must begin with a colon. This rule applies to declarations and references of module language procedure parameters. If you do not use one of these dialects or clauses, no parameter name can begin with a colon. For more information, see SET_DIALECT. 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/ISO standard modules. o In SQL statements to be dynamically executed, you refer to the data parameters and indicator parameters with a single parameter marker (?). SQL gets information about the parameters in EXECUTE or OPEN statements. These statements either provide an explicit list of data parameters and indicator parameters (using the notation for precompiled SQL or SQL modules as appropriate) or refer to the SQLDA that has fields that provide information about data parameters (SQLDATA) and indicator parameters (SQLIND). 3 Host_Structures_and_Indicator_Arrays Host structures are host language parameters that correspond to group constructs or records in the languages that support such constructs. Use a host structure to refer to a list of host language variables with a single name. Once you define a host structure, you can refer to it in an embedded SQL statement or in an SQL module language procedure instead of listing the host language variables that comprise it. Parameters can be qualified by group fields to any depth. The format of a qualified reference to a parameter in a group construct is: qualified-parameter = --> : --+-+------------------------+-+--> parameter-name ----> | +--> group-field-name. --+ | +------------ <--------------+ In addition, you can declare an indicator parameter for a host structure by defining a one-dimensional array of signed longword integers. This array provides indicator parameters for fields in the host structure and is called an indicator array. (Indicator arrays are also called indicator structures or indicator vectors.) Just as you append an indicator parameter to a data parameter, you can append the name of an indicator array to a host structure that represents several data parameters. Indicator arrays are the only way to specify indicator parameters for host structures. You can refer to a host structure anywhere that SQL allows a list of parameters: o VALUES clause of an INSERT statement o Select lists o IN predicates o INTO clause of FETCH or singleton SELECT statements o USING clause of OPEN or EXECUTE statements You cannot use host structures in a stored routine or a multistatement procedure. You can also refer to a single parameter in a host structure. In FORTRAN, C, Pascal, and Ada, you must qualify the parameter name with all preceding group field names. In COBOL and PL/I, you need to qualify the parameter with group field names only if the name is ambiguous without such qualification. 3 Multistatement_Variables_and_Stored_Routines Multistatement procedure variables and stored routine parameters are often used in value expressions A variable is an identifier that represents a value that can change during the execution of a program. You use SQL variables in multistatement procedures. A stored routine parameter is a variable associated with the parameters of a stored routine that you use in a stored procedure or stored function. A stored routine refers to both stored procedures and stored functions defined using the CREATE MODULE statement. variable = --+-> : --------------+-> +-> : -+ Variables in multistatement procedures and stored routine parameters follow the rules, such as case-sensitivity rules, associated with the encompassing module. That means: o In embedded SQL, the variables follow the rules for the host language in which the program is written. o In SQL module language programs, the variables follow the rules for the SQL interface. o In stored routines, the variables follow the rules for the SQL interface. Unlike data parameters, variables and stored routine parameters allow null values. Because of this, you cannot use indicator parameters with variables and stored routine parameters. For more information about stored routine parameters, see CREATE MODULE. 3 External_Routine_Parameters An external routine parameter is a 3GL declaration that corresponds to an actual parameter in the calling program. These declarations are called formal parameters. 3GL or SQL statements in the external routine use the formal parameter name to refer indirectly to the calling programs actual parameters. External routine parameters cannot represent null values. 2 Statement_Names Dynamic SQL lets programs accept or generate SQL statements at run time, in contrast to precompiled statements that must be embedded in the program before it is compiled. Unlike embedded statements, such dynamically executed SQL statements are not part of any source code but are created while the program is running. Dynamic SQL is useful when you cannot predict the type of SQL statement your program needs to process. To handle dynamically executed SQL statements, programs use embedded PREPARE statements to assign a name to the SQL statement created at run time and to prepare it for execution. The EXECUTE, dynamic DECLARE CURSOR, and DESCRIBE statements refer to that assigned name. You cannot qualify prepared statement names. Because they are prepared with embedded PREPARE statements, you can refer to dynamic statement names from programs only, not from interactive SQL. 2 Schema_Names A schema consists of metadata definitions such as tables, views, domains, constraints, collating sequences, indexes, storage maps, triggers, and the privileges for each of these. You name schemas in CREATE SCHEMA or CREATE DATABASE statements. You can also use schema names to qualify the names of other database elements such as tables, views, and columns. NOTE In syntax diagrams, the schema-name syntax element refers to either the qualified or unqualified form of the name given to the schema in the CREATE statement. That is, in syntax diagrams, the schema-name is always defined as: schema-name = --+---------------------------------------------+ +------> --------------+->. ---+ +-> " -> . ->" --+ | | +-----------------------------------------+ | +--------------> ------+-> +-> " -> . ->" ------+ By default, each database that you create has only one schema. CREATE DATABASE tells how to create a multischema database. The alias RDB$DBHANDLE represents the schema when you refer to definitions in a single-schema database or definitions in a multischema database without multischema naming enabled. When you refer to definitions in a multischema database, you must follow multischema naming rules unless you disable multischema naming. In multischema naming: o You must qualify definition names using the name of the schema that contains them. You cannot refer to a table and a view or two objects of the same type (such as two tables) with the same name unless they belong to different schemas. o You may additionally qualify the names of objects in a multischema database with the alias and the catalog name. Whenever you qualify the object name with a catalog name, you must also specify the schema name, unless you want to use the default schema. Remember that the catalog name and alias combination or the schema name and alias combination must be enclosed within double quotation marks. o If you prefer, you can qualify an object name in a multischema database with just an alias, provided you have set the default catalog and schema to the ones that you want to contain the object. Enclose the alias and object name pair within double quotation marks and separate them with a period. If you omit the schema name when referring to objects in a multischema database, SQL uses a schema with the same name as the user identifier of the invoker as the default schema. You can use the SET SCHEMA statement to change the default schema. 2 Storage_Area_Names Storage areas are data and snapshot files that are associated with one or more tables in a multifile database. You name storage areas in CREATE STORAGE AREA clauses within CREATE DATABASE or IMPORT statements. The CREATE STORAGE MAP statements control which parts of which tables get stored in a particular storage area. In syntax diagrams, the syntax element area-name specifies that you supply the name of a storage area at that place in the statement. In CREATE STORAGE AREA clauses and in other SQL statements, the names you give to storage areas in the CREATE statement can be qualified by aliases. You must use ASCII alphanumeric characters for the storage area name. NOTE In syntax diagrams, the area-name syntax element refers to either the qualified or unqualified form of the name given to the storage area in the CREATE STORAGE AREA clause. area-name = -+---------------+-> -----> +---> . + 2 Storage_Map_Names Storage maps control which parts of which tables get stored in a particular storage area in a multifile database. You name storage maps in CREATE STORAGE MAP statements. In syntax diagrams, the syntax element map-name specifies that you supply the name of a storage area at that place in the statement. In CREATE STORAGE MAP and other SQL statements, the names you give to storage maps in the CREATE statement can be qualified by aliases. NOTE In syntax diagrams, the map-name syntax element refers to either the qualified or unqualified form of the name given to the storage map in the CREATE STORAGE MAP statement. map-name = ---+------------------+-> ---> +-> --> . -+ 2 Stored_Names The name that you specify for a data definition when you create it is called the SQL name. Each data definition also has a stored name that it is known by to Oracle Rdb. You can give the same SQL name to two entities of the same type within different schemas of a multischema database. For example, you could create a table called EMPLOYEES in the schema DEPT1 and a second EMPLOYEES table in the schema DEPT2. For the first EMPLOYEES table created, SQL assigns a stored name that is the same as the SQL name. For subsequent EMPLOYEES tables, SQL generates a unique stored name by adding a serial number and truncating the name, if necessary. If you prefer to specify a stored name for a definition in a multischema database instead of relying on SQL to generate one, you can do so using the STORED NAME IS clause for any CREATE statement. You can only specify stored names for definitions in multischema databases. 2 Table_and_View_Names You name tables and views in CREATE TABLE and CREATE VIEW statements. In those and other SQL statements, the names you give to tables and views in CREATE statements can be qualified by aliases and can themselves qualify column names. If your database has the multischema option enabled, you can also qualify table and view names by schema and catalog names, or by the alias. You must use double quotation marks to surround the alias and table name pair and have set your dialect to the ANSI/ISO SQL standard or use the ANSI/ISO SQL standard quoting rules. See the SET_DIALECT and the SET_QUOTING_RULES for more information about dialects and quoting rules. The following are valid names for the EMPLOYEES table in the database with alias CORP, catalog ADMINISTRATION, and schema PERSONNEL: o "CORP.ADMINISTRATION".PERSONNEL.EMPLOYEES o "CORP.EMPLOYEES" NOTE In syntax diagrams, the table-name and view-name syntax elements refer to either the qualified or unqualified form of the names given to the table or view in the CREATE statement. That is, in syntax diagrams, table-name and view- name are always defined as: table-name = -+-------------------------+-> --> +-+-> +> . -+ +-> ------+ view-name = -+-------------------------+-> ---> +-+-> +> . -+ +-> ------+ You must qualify table names and view names with an alias if they are not in the default database. The following example shows the error that SQL generates if you try to use an unqualified table name to refer to a database previously declared with an alias: SQL> ATTACH 'ALIAS PERS PATHNAME personnel'; SQL> SET QUOTING RULES 'SQL92'; SQL> SELECT * FROM EMPLOYEES; %SQL-F-NODEFDB, There is no default database SQL> -- This statement will work: SQL> SELECT * FROM "PERS.EMPLOYEES"; 2 Trigger_Names You name a trigger in the CREATE TRIGGER statement. A trigger name must be unique within a schema of a multischema database or unique within a nonmultischema database. A trigger defines the actions to occur before or after a specified table is updated (by a write operation such as an INSERT, DELETE, or UPDATE statement). A trigger can be thought of as a rule on a single table, which takes effect at a specific time for a particular type of update and causes one or more triggered actions to be performed. With triggers, you can define useful actions such as: o Cascading deletes Deleting a row from one table causes additional rows to be deleted from other tables that are related to the first table by key values. o Cascading updates Updating a row in one table causes additional rows to be updated in other tables that are related to the first table by key values. These updates are usually limited to the key values themselves. o Summation updates Updating a row from one table causes a value in a row of another table to be updated by being increased or decreased. o Hidden deletes Causing rows to be deleted from a table by moving them to a parallel table that is not used by the database. o Audit log Records when and by whom a row is inserted, updated, or deleted.