Specifies the name and the source of the database definitions to
be used for module compilation, and makes the named alias part of
the implicit environment of an application. You can name either
a file or a repository path name to be used for the database
definitions.
1 – Environment
You can use the DECLARE ALIAS statement:
o Embedded in host language programs to be precompiled
o In a context file
o As part of the DECLARE section in an SQL module
The alias that you declare must be different from any other alias
specified in the module.
2 – Format
(B)0[m[1;4mDECLARE[m[1m qwqqqqqqqqqqqqqqqqqqwqk [m
[1m mq> scope-options qj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmqwqqqqqqqqqqqqwq [1;4mALIAS[m[1m FOR COMPILETIME qk [m
[1m mq> <alias> qj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqj [m
[1mmqwq> [1;4mFILENAME[m[1m qq> 'attach-spec ' qwqqqqqqk [m
[1m mq> [1;4mPATHNAME[m[1m qq> <path-name> qqqqj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqj [m
[1mmqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqk [m
[1m mq> lit-or-def-user-authentication qqj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqj [m
[1mmqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk [m
[1m mq> [1;4mRUNTIME[m[1m runtime-options qj x [m
[1mlqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqj [m
[1mmqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq> [m
[1m x tq> database-options qqqqqqqqqqqqqqqqqqqqqqqqu x [m
[1m x tq> attach-options qqqqqqqqqqqqqqqqqqqqqqqqqqu x [m
[1m x tq> [1;4mDEFAULT[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m support-char-set qqu x [m
[1m x tq> [1;4mNATIONAL[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m support-char-set qu x [m
[1mx[m [1mmq>[m [1;4mDISPLAY[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m support-char-set qqj[m [1mx[m
[1m mqqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqj [m
(B)0[m[1mlit-or-def-user-authentication = [m
[1m [m
[1mqq> [1;4mUSER[m[1m qwq> '<username>' qwqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> [m
[1m mq> [1;4mDEFAULT[m[1m qqqqqj mqq> [1;4mUSING[m[1m qwq> '<password>' qqwqj [m
[1m mq> [1;4mDEFAULT[m[1m qqqqqqj [m
[1m [m
(B)0[m[1mscope-options = [m
[1m [m
[1mqwq> [1;4mLOCAL[m[1m qqqqqqqqqqqqqqwqq> [m
[1m tq> [1;4mGLOBAL[m[1m qqqqqqqqqqqqqu [m
[1m mq> [1;4mEXTERNAL[m[1m qqqqqqqqqqqj [m
[1m [m
(B)0[m[1mattach-spec = [m
[1m [m
[1mqqwqqqqqqqqqqqqqqqqwq> <file-spec> qqqqq> [m
[1m mq> <node-spec> qj [m
[1m [m
(B)0[m[1mnode-spec = [m
[1m [m
[1mqwq> <nodename> qwqqqqqqqqqqqqqqqqqqqwqwq>[m
[1m x mq> <access-string> j x [m
[1m mqqqqqqqqqqqqqqqqqq :: <qqqqqqqqqqqqqqj [m
(B)0[m[1maccess-string = [m
[1m [m
[1mqwq> " <user-name> <password> " qqwq> [m
[1m mq> " <VMS-proxy-user-name> " qqqj [m
[1m [m
(B)0[m[1mruntime-options = [m
[1m [m
[1m qwq> [1;4mFILENAME[m[1m qqwq> '<attach-spec>' wqwqqq> [m
[1m x mq> <parameter> qqqj x [m
[1m tq> [1;4mPATHNAME[m[1m qqwq> <path-name> qqwqqu [m
[1m x mq> <parameter> qqj x [m
[1m mq> runtime-string qqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mruntime-string = [m
[1m [m
[1mqw> ' qw> [1;4mFILENAME[m[1m <attach-spec> qwqwqqqqqqqqqqqqqqqqqqqqqw> ' wq> [m
[1m x m> [1;4mPATHNAME[m[1m <pathname> qqqqj m> literal-user-auth qj x [m
[1m m> parameter qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mdatabase-options = [m
[1m [m
[1mqqwqq> [1;4mELN[m[1m qqqqqqqqqqqqqqqqqqqqqwqq> [m
[1m tqq> [1;4mNSDS[m[1m qqqqqqqqqqqqqqqqqqqqu [m
[1m tqq> rdb-options qqqqqqqqqqqqqu [m
[1m tqq> [1;4mVIDA[m[1m qqqqqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mVIDA[m[1m [1;4mV1[m[1m qqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mVIDA[m[1m [1;4mV2[m[1m qqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mVIDA[m[1m [1;4mV2N[m[1m qqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mNOVIDA[m[1m qqqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mDBIV1[m[1m qqqqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mDBIV31[m[1m qqqqqqqqqqqqqqqqqqu [m
[1m mqq> [1;4mDBIV70[m[1m qqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m [1mrdb-options = [m
[1m [m
[1mqwq> [1;4mRDBVMS[m[1m qqwqq>[m
[1m tq> [1;4mRDB030[m[1m qqu [m
[1m tq> [1;4mRDB031[m[1m qqu [m
[1m tq> [1;4mRDB040[m[1m qqu [m
[1m tq> [1;4mRDB041[m[1m qqu [m
[1mtq>[m [1;4mRDB042[m [1mqqu[m
[1mtq>[m [1;4mRDB050[m [1mqqu[m
[1mtq>[m [1;4mRDB051[m [1mqqu[m
[1mtq>[m [1;4mRDB060[m [1mqqu[m
[1mtq>[m [1;4mRDB061[m [1mqqu[m
[1mtq>[m [1;4mRDB070[m [1mqqu[m
[1mmq>[m [1;4mRDB071[m [1mqqj[m
(B)0[m[1mattach-options = [m
[1m [m
[1mqwq> [1;4mDBKEY[m[1m qwq> SCOPE IS qwq> [1;4mATTACH[m[1m qqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqwq>[m
[1m tq> [1;4mROWID[m[1m qj mq> [1;4mTRANSACTION[m[1m qqj x [m
[1m tq> [1;4mMULTISCHEMA[m[1m IS qwq> [1;4mON[m[1m qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x mq> [1;4mOFF[m[1m qj x [m
[1m tq> [1;4mPRESTARTED[m[1m [1;4mTRANSACTIONS[m[1m [1;4mARE[m[1m qwq> [1;4mON[m[1m qqwqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x mq> [1;4mOFF[m[1m qj x [m
[1m mqwqqqqqqqwq> [1;4mRESTRICTED[m[1m [1;4mACCESS[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mq> [1;4mNO[m[1m qj [m
3 – Arguments
3.1 – alias ALIAS
Specifies a name for the attach to the database. Specifying an
alias lets your program refer to more than one database.
You do not have to specify an alias in the DECLARE ALIAS
statement. The default alias in interactive SQL and in
precompiled programs is RDB$DBHANDLE. In the SQL module language,
the default is the alias specified in the module header. Using
the default alias (either by specifying it explicitly in the
DECLARE ALIAS statement or by omitting any alias) makes the
database part of the default environment. Specifying a default
database means that statements that refer to the default database
do not need to use an alias.
If a default alias was already declared and you specify the
default alias in the alias clause (or specify any alias that
was already declared), you receive an error when you precompile
the program or process it with the SQL module processor.
3.2 – database-options
By default, SQL uses only the database options used to compile
a program as valid options for that program. If you want to use
the program with other supported databases, you can override the
default options by specifying database options in the ATTACH or
DECLARE ALIAS statement.
For more information on database options, see the Database_
Options HELP topic.
3.3 – DBKEY_SCOPE
Syntax options:
DBKEY SCOPE IS ATTACH | DBKEY SCOPE IS TRANSACTION
Controls when the database key of a deleted row can be used again
by SQL.
o The default DBKEY SCOPE IS TRANSACTION means that SQL can
reuse the database key of a deleted table row (to refer to a
newly inserted row) as soon as the transaction that deleted
the original row completes with a COMMIT statement. (If the
user who deleted the original row enters a ROLLBACK statement,
then the database key for that row cannot be used again by
SQL.)
During the connection of the user who entered the DECLARE
ALIAS statement, the DBKEY SCOPE IS TRANSACTION clause
specifies that a database key is guaranteed to refer to the
same row only within a particular transaction.
o The DBKEY SCOPE IS ATTACH clause means that SQL cannot use the
database key again (to refer to a newly inserted row) until
all users who have attached with DBKEY SCOPE IS ATTACH have
detached from the database.
It only requires one process to attach with DBKEY SCOPE
IS ATTACH to force all database users to assume this
characteristic.
o Oracle Corporation recommends using DBKEY SCOPE IS TRANSACTION
to prevent excessive consumption of storage area space by
overhead space needed to support DBKEY SCOPE IS ATTACH, and to
prevent performance problems when storing new rows.
During the connection of the user who entered the DECLARE
ALIAS statement, the DBKEY SCOPE IS ATTACH clause specifies
that a database key is guaranteed to refer to the same row
until the user detaches from the database.
See the DBKEY HELP topic for more information.
3.4 – DEFAULT_CHARACTER_SET
Specifies the default character set of the alias at compile
time. For a list of allowable character set names, see Supported
Character Sets.
3.5 – DISPLAY CHARACTER SET support-char-set
Specifies the character set encoding and characteristics expected
of text strings returned from Oracle Rdb.
3.6 – FILENAME
A quoted string containing full or partial information needed to
access a database.
For an Oracle Rdb database, an attach specification contains the
file specification of the .rdb file.
When you use the FILENAME argument, any changes you make to
database definitions are entered only to the database system
file, not to the repository. If you specify FILENAME, your
application attaches to the database with that file name at run
time.
If you specify FILENAME:
- During compilation, your application attaches to the specified
database and reads metadata from the database definitions.
- At run time, your application attaches to the specified
database.
For information regarding node-spec and file-spec, see Oracle Rdb
Attach Specifications.
3.7 – FOR_COMPILETIME
Optional keyword provided for upward compatibility: DECLARE ALIAS
specifies the compile-time environment by default. Specifies that
the alias declared is the source of the database definition for
program compiling and execution.
3.8 – lit-or-def-user-authentication
Specifies the user name and password to enable access to
databases, particularly remote databases.
You can use this clause to explicitly provide user name and
password information in the DECLARE ALIAS statement.
3.9 – literal-user-auth
Specifies the user name and password for the specified database
to be accessed at run time. For more information about when to
use this clause, see the ATTACH statement.
3.10 – MULTISCHEMA_IS
Syntax options:
MULTISCHEMA IS ON | MULTISCHEMA IS OFF
The MULTISCHEMA IS ON clause enables multischema naming for the
duration of the database attach. The MULTISCHEMA IS OFF clause
disables multischema naming for the duration of the database
attach. Multischema naming is disabled by default.
3.11 – NATIONAL_CHARACTER_SET
Specifies the national character set of the alias at compile
time. For a list of allowable character set names, see Supported
Character Sets.
3.12 – PATHNAME
A full or relative repository path name that specifies the
source of the database definitions. When you use the PATHNAME
argument, any changes you make to database definitions are
entered in both the repository and the database system file.
Oracle Rdb recommends using the PATHNAME argument if you have the
repository on your system and you plan to use any data definition
statements.
If you specify PATHNAME:
o During compilation, your application attaches to the
repository database definition and reads metadata from the
dictionary definitions. SQL extracts the file name of the
Oracle Rdb database from the dictionary and saves it for use
at run time.
o At run time, your application attaches to the Oracle
Rdb database file name extracted from the dictionary at
compilation.
3.13 – PRESTARTED_TRANSACTIONS_ARE
Syntax options:
PRESTARTED TRANSACTIONS ARE ON | PRESTARTED TRANSACTIONS ARE OFF
Specifies whether Oracle Rdb enables or disables prestarted
transactions.
Use the PRESTARTED TRANSACTIONS ARE OFF clause only if your
application uses a server process that is attached to the
database for long periods of time and causes the snapshot file
to grow excessively. If you use the PRESTARTED TRANSACTIONS ARE
OFF clause, Oracle Rdb may require additional I/O as each SET
TRANSACTION statement must reserve a transaction sequence number
(TSN).
For most applications, Oracle Rdb recommends that you enable
prestarted transactions. The default is PRESTARTED TRANSACTIONS
ARE ON. If you use the PRESTARTED TRANSACTIONS ARE ON clause or
do not specify the PRESTARTED TRANSACTIONS clause, the COMMIT
or ROLLBACK statement for the previous read/write transaction
automatically reserves the TSN for the next transaction and
reduces I/O.
You can use ALTER DATABASE . . . PRESTARTED TRANSACTIONS clause
to establish a default setting for all applications using the
database. You can also define the RDMS$BIND_PRESTART_TXN logical
name to define the default setting for prestarted transactions
outside of an application. The PRESTARTED TRANSACTION clause
overrides this logical name and database setting. For more
information, see the Oracle Rdb7 Guide to Database Performance
and Tuning.
3.14 – RESTRICTED_ACCESS
Syntax options:
RESTRICTED ACCESS | NO RESTRICTED ACCESS
Restricts access to the database. This allows you to access the
database but locks out all other users until you disconnect from
the database. Setting restricted access to the database requires
DBADM privileges.
The default is NO RESTRICTED ACCESS if not specified.
3.15 – ROWID_SCOPE
Syntax options:
ROWID SCOPE IS ATTACH | ROWID SCOPE IS TRANSACTION
The ROWID keyword is a synonym for the DBKEY keyword. See the
DBKEY_SCOPE argument for more information.
3.16 – RUNTIME
Specifies the source of the database definitions when the program
is run.
3.17 – runtime-string
A quoted string or parameter that specifies the file name or path
name of the database to be accessed at run time, and optionally,
the user name and password of the user accessing the database at
run time.
3.18 – scope-options
LOCAL | GLOBAL | EXTERNAL
Specifies the scope of the alias declaration in precompiled SQL
or SQL module language.
The scope-option declarations are:
o LOCAL declares an alias that is local to procedures in the
module in which it is declared, or local to dynamic statements
prepared in the module in which it is declared.
SQL attaches to a database with LOCAL scope only when you
execute a procedure in the same module without a session. The
alias of a database with LOCAL scope pertains only to that
module.
If the execution of a procedure in another module has attached
to the implicit environment and that procedure subsequently
calls another procedure that references a local database, SQL
attempts to attach to that local database. If no transaction
is active, SQL adds the local database to the implicit
environment for this module. If a transaction is active, SQL
returns an error message.
o GLOBAL declares an alias definition that is global to
procedures in the application. GLOBAL is the default.
o EXTERNAL declares an external reference to a global alias that
is defined in another module.
In single-image applications, the distinction between alias
definitions and alias references is often unimportant. It is
only necessary that each alias have at least one definition.
For this reason, Oracle Rdb has treated all alias references
(declared with the EXTERNAL keyword) the same as alias
definitions (declared with the GLOBAL keyword or the default.)
For compatibility with previous versions, this remains the
default.
However, applications that share aliases between multiple
images require a distinction between alias definitions and
alias references. All definitions of any aliases shared between
multiple OpenVMS images must be defined in one image, generally
the shareable image against which you link the other images.
Oracle Rdb recommends that you distinquish alias definitions
from alias references in any new source code. Use the GLOBAL (or
default) scope keyword for alias definitions and the EXTERNAL
keyword for alias references. If you share aliases between
multiple OpenVMS images, use the NOEXTERNAL_GLOBALS command line
qualifier to override the default and cause SQL to properly treat
alias references as references.
If you use the EXTERNAL_GLOBAL command line qualifier, SQL treats
aliases declared with the EXTERNAL keyword as GLOBAL. That is,
SQL initializes alias references as well as alias definitions.
If you use the NOEXTERNAL_GLOBAL command line qualifier, SQL
treats aliases declared with the EXTERNAL keyword as alias
references and does not initialize them. It initializes all other
aliases.
The EXTERNAL_GLOBAL qualifier is the default.
The [NO]INITIALIZE_HANDLES command line qualifiers also affect
the initialization of aliases, but they are recommended only for
use in versions prior to V7.0.
See the SQL Module Language and SQL Precompiler help topics for
more information about the command line qualifiers.
3.19 – USER
Syntax options:
username | DEFAULT
Specifies the operating system user name that the database system
uses for privilege checking.
You can specify a character string literal for the user name or
you can specify the DEFAULT keyword. The DEFAULT keyword allows
you to avoid placing the user name in a program's source code.
If you specify the DEFAULT keyword, you pass the user name to the
program by using a command line qualifier when you compile an SQL
module or precompiled program. You use the USERNAME qualifier.
3.20 – USING
Syntax options: USING 'password' | DEFAULT
Specifies the user's password for the user name specified in the
USER clause.
You can specify a character string literal for the PASSWORD or
you can specify the DEFAULT keyword. The DEFAULT keyword allows
you to avoid placing the user name in a program's source code.
If you specify the DEFAULT keyword, you pass the password to the
program by using a command line qualifier when you compile an SQL
module or precompiled program. You use the PASSWORD qualifier.
4 – Examples
Example 1: Specifying a database and an alias in embedded SQL
This statement declares the database defined by the file
specification personnel. The precompiler uses this definition
when compiling the program and SQL uses the file personnel when
the program runs. This name may be a logical name or the name
portion of the file personnel.rdb.
EXEC SQL
DECLARE PERS_ALIAS ALIAS FOR FILENAME personnel
END-EXEC
Example 2: Specifying a database with restricted access
This statement is the same as Example 1, but specifies restricted
access to the database.
EXEC SQL
DECLARE PERS_ALIAS ALIAS FOR FILENAME personnel
RESTRICTED ACCESS
END-EXEC
Example 3: Specifying the DECLARE ALIAS statement
This portion of an application program declares the databases
MIA1 and MIA_CHAR_SET. The precompiler uses the MIA1 database
when compiling the program and SQL uses the MIA_CHAR_SET database
when the program runs.
EXEC SQL
DECLARE ALIAS
COMPILETIME FILENAME MIA1
RUNTIME FILENAME MIA_CHAR_SET
DEFAULT CHARACTER SET DEC_KANJI
NATIONAL CHARACTER SET KANJI;
Example 4: Specifying the DEFAULT user authentication
The following example shows how to use the DEFAULT clause for
user name and password in an SQL module:
MODULE TEST_DECLARE
DIALECT SQL99
LANGUAGE C
PARAMETER COLONS
ALIAS RDB$DBHANDLE
-------------------------------------------------------
-----------------------declarations--------------------
DECLARE ALIAS COMPILETIME FILENAME mf_personnel
USER DEFAULT
USING DEFAULT
RUNTIME :run_time_spec
.
.
.
You pass the compile-time user name and password to the program
by using command line qualifiers. For example, to compile the
program use the following command line:
$ SQLMOD TESTDEC /USER=heleng /PASS= helenspasswd
At run time, the host language program can prompt the run-
time user to specify only the file specification or the file
specification and the user name and password at run time. The
host language program can build the run time string.
For example, if the host language program uses only the file
specification, the value of the variable passed to the program
can be the following:
FILENAME "mf_personnel"
If the host language program uses the file specification, user
name and password, the value of the variable passed to the
program can be the following:
FILENAME "mf_personnel 'USER heleng' USING 'mypassword' "
You must enclose the string in quotation marks; whether you
use single (') or double quotation marks (") depends upon the
programming language.
If you use the following DECLARE ALIAS statement, the host
language program can only prompt the run-time user to specify
the file name.
DECLARE ALIAS COMPILETIME FILENAME mf_personnel
USER DEFAULT
USING DEFAULT
RUNTIME FILENAME :foo