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
1 – 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.
2.1 – 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.
2.2 – 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.
3 – 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:
(B)0[m[1mcatalog-name = [m
[1m [m
[1mqwqqqqqq> <name-of-catalog> qqqqqqqqqqqqqqwq>[m
[1m x x [m
[1m mq> " q> <alias>.<name-of-catalog> q>" qj [m
[1m [m
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.
4 – 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:
(B)0[m[1mcolumn-name = [m
[1m [m
[1mqqqwqqqqqqqqqqq>qqqqqqqqqqqqqqqqqwq> <name-of-column> qq> [m
[1m tq> <table-name> qqqqqqqw> . qj [m
[1m tq> <view-name> qqqqqqqqu [m
[1m mq> <correlation-name> qj [m
[1m [m
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.
4.1 – 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
4.2 – 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.
5 – 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.
6 – 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.
7 – 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.
8 – 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)
8.1 – 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.
8.2 – 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.
9 – 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.
(B)0[m[1mdomain-name = [m
[1m [m
[1mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> <name-of-domain> qqq> [m
[1m mqwq> <schema-name> qwq> . qj [m [1m [m
[1m mq> <alias> qqqqqqqj [m [1m [m
[1m [m [1m [m [1m [m
10 – 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.
(B)0[m[1mindex-name = [m [1m [m [1m [m
[1m [m [1m [m [1m [m
[1mqqqwqqqqqqqqqqqqqq[mqqqqqqqqqqqqqqw[1mq> <name-of-index> [mqq[1mq> [m
[1m tqqq> <schema-name> [mqwqq>[1m [m. qj [1m [m [1m [m
[1m mqqq> <alias> [mqqqqqqqj[1m [m [1m [m [1m [m
[1m [m [1m [m [1m [m
11 – 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.
12 – 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.
13 – 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.
13.1 – 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.
(B)0[m[1mparameter = [m
[1m [m
[1mqq> : <data-parameter> qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1m mwqqqqqqqqqqqqqqqwqqqqqqqqqk x [m
[1m mq> INDICATOR qqj x x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mq> : <indicator-parameter> qj [m
[1m [m
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).
13.2 – 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:
(B)0[m[1mqualified-parameter = [m
[1m [m
[1mqq> : qqwqwqqqqqqqqqqqqqqqqqqqqqqqqwqwqq> parameter-name qqqq> [m
[1m x mqq> group-field-name. qqj x [m
[1m mqqqqqqqqqqqq <qqqqqqqqqqqqqqj [m
[1m [m
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.
13.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.
(B)0[m[1mvariable = [m
[1m [m
[1mqqwq> : <variable-name> qqqqqqqqqqqqqqwq> [m
[1m mq> : <stored-procedure-parameter> qj [m
[1m [m
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.
13.4 – 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.
14 – 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.
15 – 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:
(B)0[m[1mschema-name = [m
[1m [m
[1mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq[mqqqqqqqq[1mqk [m
[1m tqqqqqq> <catalog-name> qqqqqqqqqqq[mqq[1mqwq>. qqqu [m
[1m tq> " q> <alias>.<catalog-name> q>"[m [1mqqj [m [1mx [m
[1m x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m x mqqqqqqqqqqqqqq> <name-of-schema> qqqqqqwq> [m
[1m mq> " q> <alias>.<name-of-schema> q>" qqqqqqj [m
[1m [m
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.
16 – 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.
(B)0[m[1marea-name = [m
[1m [m
[1mqwqqqqqqqqqqqqqqqwq> <name-of-area> qqqqq> [m
[1m mq[mq[1mq> <alias> . j [m
[1m [m
17 – 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.
(B)0[m[1mmap-name = [m
[1m [m
qq[1mqwqqqqqqqqqqqqqqqqqqwq> <name-of-map> qqq> [m
[1m mq> <alias> qq> . qj [m
18 – 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.
19 – 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:
(B)0[m[1mtable-name = [m
[1m [m
[1mqwqqqqqqqqqqqqqqqqqqqqqqqqqwq> <name-of-table> qq>[m
[1m mqwq> <schema-name> w> . qj [m [1m [m
[1m [m [1m mq> <alias> qqqqqqj [m [1m [m
[1m [m [1m [m
(B)0[m[1mview-name = [m
[1m [m
[1mqwqqqqqqqqqqqqqqqqqqqqqqqqqwq> <name-of-view> qqq> [m
[1m mqwq> <schema-name> w> . qj [m [1m [m
[1m mq> <alias> qqqqqqj [m [1m [m
[1m [m
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";
20 – 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.