1 – 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.
2 – 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.
4 – 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.
5 – 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.
6 – char-data-types
Refer to the Oracle Rdb SQL Reference Manual for information
about the character data types that SQL supports.
7 – 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.
8 – 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.
9 – 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
10 – 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.
11 – 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.
12 – 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.
13 – 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.
14 – 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.
15 – 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.
16 – 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.
17 – 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.
18 – 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).
19 – 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.
20 – 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.
21 – 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.
22 – 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.
23 – 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).
24 – 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.
25 – 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.
26 – 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.
27 – 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.
28 – 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.
29 – 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.
30 – 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
31 – 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.
32 – 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.
33 – 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.
34 – 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.
35 – 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.
36 – 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;
37 – 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.
38 – 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.
39 – 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.
40 – 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.
41 – 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.
42 – 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.
43 – 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.