The SQL module language and SQL module processor allow procedures
that contain SQL statements to be called from any host language,
including those not supported by the SQL precompiler.
The SQL module language provides a calling mechanism for host
language programs to execute SQL statements contained in a
separate file called an SQL module file. The file contains module
language elements that specify a single SQL module. The module
includes one or more procedures. A procedure can contain a:
o Simple statement, which consists of a single SQL statement and
optional parameter declarations
o Compound statement, which can include local variable
declarations, multiple SQL statements, flow control
statements, and transaction management statements
A procedure that contains a single SQL statement is called a
simple-statement procedure. A procedure that contains a compound
statement, which can contain multiple SQL statements, is called a
multistatement procedure.
The host language program uses call statements to specify a
particular SQL module procedure and supplies a sequence of actual
parameters that corresponds in number and in data type to the
parameter declarations in the procedure. A call to a procedure
in an SQL module causes the simple or compound statement in the
procedure to be executed.
Oracle Rdb recommends using SQL module language, rather than
precompiled SQL, because module language offers the following
advantages:
o Module language allows procedures that contain SQL statements
to be called from any host language. In contrast, the SQL
precompiler only supports a subset of host languages: Ada, C,
COBOL, FORTRAN, Pascal, and PL/I.
o Programs that use the SQL module language can isolate all
SQL statements in SQL modules to improve modularity and avoid
using two languages in the same source file.
o Programs can work around restrictions of the SQL precompiler
by calling SQL modules:
- Programs that support pointer variables can take full
advantage of dynamic SQL and use the SQLDA and SQLDA2 with
the SQL module language.
- SQL module language does not restrict use of host language
features not supported by the precompiler (such as pointer
variables in C, block structure, macros, user-defined
types, and references to array elements).
o Programs written in languages for which there is an ANSI
standard can avoid embedding code that does not conform to
the standard by isolating noncompliant SQL statements in SQL
modules.
For a detailed discussion of programming considerations for the
SQL module language, see the Oracle Rdb Guide to SQL Programming.
1 – Environment
SQL module language elements must be part of an SQL module file.
2 – Format
(B)0[m[1;4mMODULE[m[1m qqqqwqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqwqk [m
[1m m> <module-name> qj m> [1;4mDIALECT[m[1m environment qqqj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqqwqqqqqqqqqqqqqqqqqqqqqqqwqqqq>qqqqqqqqqqqqqqqqqqqqqqqqqqk [m
[1m mq> char-set-options qqqj x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqq> [1;4mLANGUAGE[m[1m language-name qqwqqqqqqqqqqqqqqqqqqqqqqqqqwqk [m
[1m m> [1;4mCATALOG[m[1m <catalog-name> j x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqwqqqqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqqk [m
[1m m> [1;4mSCHEMA[m[1m <schema-name> qj m> [1;4mAUTHORIZATION[m[1m <auth-id> j x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m mqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk [m
[1m mqq> [1;4mPRAGMA[m[1m (module-pragma-list) qj[m [1mmqq> module-language-options qqj[m [1mx[m
[1m lqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj[m
[1mmqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqwq>[m [1mprocedure-clause qqwqqqqqq>[m
[1m [m [1mmwq>[m [1mdeclare-statement qqwqj[m [1mmqqqqqqqqqqq<qqqqqqqqqqj [m
[1mmqqqqqqqqqqq<qqqqqqqqqqqj[m
(B)0[m[1menvironment = [m
[1mqqwqq> [1;4mSQL99[m[1m qqqqqwq> [m
[1mtqq> [1;4mSQL92[m[1m qqqqqu [m
[1m tqq> [1;4mSQL89[m[1m qqqqqu [m
[1m tqq> [1;4mSQLV40[m[1m qqqqu [m
[1m mqq> [1;4mMIA[m[1m qqqqqqqj [m
[1m [m
(B)0[m[1mchar-set-options = [m
[1m [m
[1mqqwqqqqqqqqqqqqqqqq> qqqqqqqqqqqqqwqqqqqk [m
[1m mqq> [1;4mNAMES[m[1m [1;4mARE[m[1m names-char-set qqj x [m
[1mlqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqj [m
[1mmwqwqqqqqqqqqqqqqqq> qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq> [m
[1m x tqq> [1;4mLITERAL[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m support-char-set qqqqu x [m
[1m x tqq> [1;4mNATIONAL[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m support-char-set qqqu x [m
[1m x tqq> [1;4mDEFAULT[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m support-char-set qqqqu x [m
[1m x tqq> [1;4mIDENTIFIER[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m names-char-set qqqu x [m
[1mx[m [1mmqq> [1;4mDISPLAY[m[1m [1;4mCHARACTER[m[1m [1;4mSET[m[1m names-char-set qqqqqqj[m [1mx [m
[1mmqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mdeclare-statement = [m
[1m [m
[1mqwq> declare-alias-statement qqqqqqqwqqqq> [m
[1m tq> declare-cursor-statement qqqqqqu [m
[1m tq> declare-statement qqqqqqqqqqqqqu [m
[1m tq> declare-table-statement qqqqqqqu [m
[1m mq> declare-transaction-statement qj [m
[1m [m
(B)0[m[1mmodule-pragma-list = [m
[1m [m
[1m qqqqqqqqqqqqwqqwq> [1;4mDEC_ADA[m[1m qqqqqqqqqqqqqqqwqqwqqqq>[m
[1mx[m [1m tq> [1;4mGNAT_ADA[m[1m qqqqqqqqqqqqqqu[m [1mx[m
[1mx[m [1mmq>[m [1;4mIDENT[m[1m string-literal qqqj[m [1mx[m
[1mmqqqqqqqqqqqqqqqq[m [1m,[m [1m<qqqqqqqqqqqqqqj[m
(B)0[m[1mmodule-language-options = [m
[1m [m
[1mqwqwqqq> [1;4mALIAS[m[1m <alias-name> qqqqqqqqqqqqqqqqqqqqqqqqwqwqq>[m
[1m x tqqq> [1;4mCHARACTER[m[1m [1;4mLENGTH[m[1m qwq> [1;4mCHARACTERS[m[1m qwqqqqqqqqu x [m
[1m x x mq> [1;4mOCTETS[m[1m qqqqqj x x [m
[1m x tqqq> [1;4mDEFAULT[m[1m [1;4mDATE[m[1m [1;4mFORMAT[m[1m qqwqq> [1;4mSQL99[m[1m qwqqqqqqqqu x [m
[1m x x tqq> [1;4mSQL92[m[1m qu x x [m
[1mx[m [1mx[m [1mmqq> [1;4mVMS[m[1m qqqj[m [1mx[m [1mx[m
[1m x tqqq> [1;4mKEYWORD[m[1m [1;4mRULES[m[1m environment qqqqqqqqqqqqqqqqqu x [m
[1m x tqqq> [1;4mPARAMETER[m[1m [1;4mCOLONS[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqu x [m
[1m x tqqq> [1;4mQUOTING[m[1m [1;4mRULES[m[1m environment qqqqqqqqqqqqqqqqqu x [m
[1m x tqqq> [1;4mRIGHTS[m[1m qqwqq> [1;4mINVOKER[m[1m qqqwqqqqqqqqqqqqqqqqqu x [m
[1m x x mqq> [1;4mRESTRICT[m[1m qqj x x [m
[1m x tqqq> [1;4mVIEW[m[1m [1;4mUPDATE[m[1m [1;4mRULES[m[1m environment qqqqqqqqqqqqqu x [m
[1m x tqqq> [1;4mQUIET[m[1m [1;4mCOMMIT[m[1m qwq> [1;4mON[m[1m qqqwqqqqqqqqqqqqqqqqqqu x [m
[1mx[m [1mx[m [1mmq> [1;4mOFF[m [1mqqj[m [1mx[m [1mx[m
[1mx[m [1mmqqq> [1;4mCOMPOUND[m[1m [1;4mTRANSACTIONS[m[1m [m [1mqwq> [1;4mINTERNAL[m [1mqwqqqqj[m [1mx[m
[1mx [m [1mmq> [1;4mEXTERNAL[m [1mqj[m [1mx[m
[1mmqqqqqqqqqqqqqqqqqqqqqqqqqqq <qqqqqqqqqqqqqqqqqqqqqqqj[m
(B)0[m[1mprocedure-clause = [m
[1m [m
[1mqqwq> [1;4mPROCEDURE[m[1m <procedure-name> qqqqqqk[m
[1m x lqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqj[m
[1m x mqwq> param-decl-list qqqqqwq> ; qqk[m
[1m x mq> ( param-decl-list ) qj x[m
[1m x lqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqj[m
[1m x mqwq> simple-statement qqqwq> ; qwq>[m
[1m x mq> compound-statement qj x [m
[1m mqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqj [m
(B)0[m[1mparam-decl-list = [m
[1m [m
[1mqwqq> param-decl qqqwq>[m
[1m mqqwqqqqq <qqqqqwqqj [m
[1m mqqqq , <qqqqj [m
(B)0[m[1mparam-decl = [m
[1m [m
[1mqwq> <parameter-name> qqk [m
[1m x lqqqqqqqqqqqqqqqqqqqj [m
[1m x mwq> data-type qqqqqwwqqqqqqqqqqqqqqq>qqqqqqqqqqqqqwqqwq> [m
[1m x tq> <domain-name> qumq> [1;4mBY[m[1m [1;4mDESCRIPTOR[m[1m wqqqqq>qqqqwj x [m
[1m x mq> record-type qqqj mq> [1;4mCHECK[m[1m qj x [m
[1m tq> [1;4mSQLCA[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mSQLCODE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mSQLSTATE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m mqwqqqqqqqqqqqqqqqqqqqqqwqqwq> [1;4mSQLDA[m[1m qqqqqqqqqqqqqqqqqqqqu [m
[1m mq> <parameter-name> qj mq> [1;4mSQLDA2[m[1m qqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mrecord-type = [m
[1m [m
[1mq> [1;4mRECORD[m[1m qqqk [m
[1mlqqqqqqqqqqqqj [m
[1mmww> <item-name> w> data-type qqwwqqw> [1;4mEND[m[1m [1;4mRECORD[m[1m q> [m
[1m xx m> record-type jx x [m
[1m xmqqqqqqqqqwqqqqqqqwqqq<qqqqqqqqj x [m
[1m x mqq , <qj x [m
[1m tq> [1;4mFROM[m[1m <path-name> qqqk x [m
[1m xlqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m xmwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqu [m
[1m x tq> [1;4mFIXED[m[1m qqqqqqqqqqqqqqqqqqqqqu x [m
[1m x tq> [1;4mNULL[m[1m [1;4mTERMINATED[m[1m [1;4mBYTES[m[1m qqqqqu x [m
[1m x mq> [1;4mNULL[m[1m [1;4mTERMINATED[m[1m [1;4mCHARACTERS[m[1m j x [m
[1m mq> [1;4mINDICATOR[m[1m [1;4mARRAY[m[1m OF qqqqqk x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mqq> <array-length> qqqqqqk x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mqq> exact-numeric-type qqqqqqqqqj [m
[1m [m
(B)0[m[1mexact-numeric-type = [m
[1m [m
[1mqqqqqqwqq> [1;4mSMALLINT[m[1m qqqwqqqqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqwq>[m
[1m tqq> [1;4mBIGINT[m[1m qqqqqu mq> [m( [1m<n>[m ) [1mqj [m [1m x [m
[1m tqq> [1;4mTINYINT[m[1m qqqqj [m [1m x [m
[1m tqq> [1;4mINTEGER[m[1m qqwqqqqqqqq[m [1mqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqu[m
[1m x mq> ([m [1m<n>[m [1m) qj mq> [1;4mIS[m[1m qqwq> 4 qwq> [1;4mBYTES[m[1m qj x[m
[1m x [m [1m mq> 8 qj x[m
[1m tqq> [1;4mDECIMAL[m[1m qqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqj[m
[1m mqq> [1;4mNUMERIC[m[1m qqj mq> ( qq> <n> qwqqqqqqqqqqwq> ) qj [m
[1m mq> , <n> qj [m
(B)0[m[1mlanguage-name = [m
[1m [m
[1m qwq> [1;4mADA[m[1m qqqqqwqq>[m
[1m tq> [1;4mBASIC[m[1m qqqu [m
[1m tq> [1;4mC[m[1m qqqqqqqu [m
[1m tq> [1;4mCOBOL[m[1m qqqu [m
[1m tq> [1;4mFORTRAN[m[1m qu [m
[1m tq> [1;4mPASCAL[m[1m qqu [m
[1m tq> [1;4mPLI[m[1m qqqqqu [m
[1m mq> [1;4mGENERAL[m[1m qj [m
(B)0[m[1mdata-type = [m
[1m [m
[1mqqwq> char-data-types qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> [m
[1m tq> [1;4mTINYINT[m[1m qqqqqqqqqqqqqqwqqqqqwqqqqqqqqqqwqqqqqqqqqqqu [m
[1m tq> [1;4mSMALLINT[m[1m qqqqqqqqqqqqqu mq> (<n>) qj x [m
[1m tq> [1;4mBIGINT[m[1m qqqqqqqqqqqqqqqu x [m
[1m tq> [1;4mLIST[m[1m [1;4mOF[m[1m [1;4mBYTE[m[1m [1;4mVARYING[m[1m qj x [m
[1m tq> [1;4mINTEGER[m[1m qwqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqu [m
[1m x mq> (<n>) qj mq> [1;4mIS[m[1m qqwq> 4 qwq> [1;4mBYTES[m[1m qj x [m
[1m x mq> 8 qj x [m
[1m tq> [1;4mDECIMAL[m[1m qwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqu [m
[1m tq> [1;4mNUMERIC[m[1m qjmq> ( qq> <n> wqqqqqqqqqqwq> ) j x [m
[1m x mq> , <n> qj x [m
[1m tq> [1;4mFLOAT[m[1m qwqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x mq> (<n>) qj x [m
[1mtq>[m [1;4mNUMBER[m[1m qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqu[m
[1mx[m [1mmq> ([m [1mqwqq <p> qwqwqqqqqqqqqwq> )[m [1mqj[m [1mx[m
[1mx[m [1mmq>[m [1m*[m [1mqqqj[m [1mmq> <d> qqj[m [1mx[m
[1m tq> [1;4mREAL[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mDOUBLE[m[1m [1;4mPRECISION[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m mq> date-time-data-types qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
(B)0[m[1mchar-data-types = [m
[1m [m
[1mqwq> [1;4mCHAR[m[1m qqqqqqqqqqqqqwwqqqqqqqqqqqqwwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqwq>[m
[1m tq> [1;4mCHARACTER[m[1m qqqqqqqqumq> ( <n> ) qjmq> [1;4mCHARACTER[m[1m [1;4mSET[m[1m char[m-[1mset-name qj x [m
[1mtq> [1;4mCHAR[m[1m [1;4mVARYING[m[1m qqqqqu[m [1m [m [1mx [m
[1mtq> [1;4mCHARACTER[m[1m [1;4mVARYING[m[1m j[m [1mx [m
[1mtq> [1;4mVARCHAR[m[1m qqw>[m [1m( <n> ) qqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqqqqqqu [m
[1mtq> [1;4mVARCHAR2[m[1m qj[m [1m mq> [1;4mCHARACTER[m[1m [1;4mSET[m[1m char-set-name qj [m [1mx[m
[1m tq> [1;4mLONG[m[1m [1;4mVARCHAR[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mNCHAR[m[1m qqqqqqqqqqqqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mNATIONAL[m[1m [1;4mCHAR[m[1m qqqqqqu mq> ( <n> ) qj [m [1m [m [1mx [m
[1m tq> [1;4mNATIONAL[m[1m [1;4mCHARACTER[m[1m qj [m [1m [m [1mx [m
[1m tq> [1;4mNCHAR[m[1m [1;4mVARYING[m[1m qqqqqqqqqqqqqqwqwqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqu[m
[1m tq> [1;4mNATIONAL[m[1m [1;4mCHAR[m[1m [1;4mVARYING[m[1m qqqqqqu mq> ( <n> ) qj [m [1m [m [1mx [m
[1m tq> [1;4mNATIONAL[m[1m [1;4mCHARACTER[m[1m [1;4mVARYING[m[1m qj [m [1m [m [1mx [m
[1mtq> [1;4mRAW[m[1m q> ( <n> ) q[mqqq[1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m
[1mmq> [1;4mLONG[m[1m qwqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1mmq> [1;4mRAW[m[1m qj[m
(B)0[m[1mdate-time-data-types = [m
[1m [m
[1mqqwq> [1;4mDATE[m[1m qwqqqqqqqqqqwqqqqqqqqqqqqqqqqqwqq> [m
[1m x tq> [1;4mANSI[m[1m qu x [m
[1m x mq> [1;4mVMS[m[1m qqqj x [m
[1m tq> [1;4mTIME[m[1m qqq> frac qqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mTIMESTAMP[m[1m qq> frac qqqqqqqqqqqqqqqqu [m
[1m mq> [1;4mINTERVAL[m[1m qqq> interval-qualifier qqj [m
[1m [m
(B)0[m[1mfrac = [m
[1m [m
[1mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1m mqq> ( <numeric-literal> ) qj [m
[1m [m
(B)0[m[1minterval-qualifier = [m
[1m [m
[1mqqwq> [1;4mYEAR[m[1m qqq> prec qqwqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqwq> [m
[1m x mq> [1;4mTO[m[1m [1;4mMONTH[m[1m qj x [m
[1m tq> [1;4mMONTH[m[1m qq> prec qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mDAY[m[1m qqqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x mq> [1;4mTO[m[1m qwq> [1;4mHOUR[m[1m qqqqqqqqqqqqqqqu [m
[1m x tq> [1;4mMINUTE[m[1m qqqqqqqqqqqqqu [m
[1m x mq> [1;4mSECOND[m[1m q> frac qqqqqu [m
[1m tq> [1;4mHOUR[m[1m qqq> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x mq> [1;4mTO[m[1m qwq> [1;4mMINUTE[m[1m qqqqqqqqqqqqqu [m
[1m x mq> [1;4mSECOND[m[1m q> frac qqqqqu [m
[1m tq> [1;4mMINUTE[m[1m q> prec qqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x mq> [1;4mTO[m[1m [1;4mSECOND[m[1m qqqqqq> frac qqqqqu [m
[1m mq> [1;4mSECOND[m[1m q> seconds-prec qqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mprec = [m
[1m [m
[1mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1m mqq> ( <numeric-literal> ) qj [m
[1m [m
(B)0[m[1mseconds-prec = [m
[1m [m
[1mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqq> [m
[1m mq> ( <numeric-literal-1> qqqk x [m
[1m lqqqqqqqqqqqqqqqqqqqqqqqqqj x [m
[1m mwqqqqqqqqqqqqqqqqqqqqqqqqqqwq> ) qqj [m
[1m m> , <numeric-literal-2> qqj [m
[1m [m
3 – Arguments
3.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.
3.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.3 – BY_DESCRIPTOR
Specifies that the formal parameter will be passed to the calling
program module by descriptor. The BY DESCRIPTOR clause is useful
when:
o You specify the GENERAL keyword in the LANGUAGE clause of
an SQL module, but the default for the language is to pass
parameters by descriptor. The default for GENERAL is to pass
parameters by reference, but you can override that default
passing mechanism by specifying BY DESCRIPTOR.
o You want to take advantage of the CHECK option for parameter
declarations. That option is available only for parameters
declared with the BY DESCRIPTOR clause.
o You need to override the default parameter passing mechanism
for languages that pass parameters by reference.
The BY DESCRIPTOR clause supports only OpenVMS static
descriptors, which are fixed-length fields.
For any language, the passing mechanism for SQL module formal
parameters must be the same as the actual parameters in the host
language module.
Ada, BASIC, C, FORTRAN, Pascal, and PL/I do not support passing
records by descriptor. You may construct a descriptor from
elements in all these languages and pass the constructed
descriptor to the SQL module language by reference.
o When you construct a descriptor for a host language record
when the module language is Ada, BASIC, C, FORTRAN, Pascal,
PL/I, or GENERAL, use a fixed-length descriptor (CLASS_S)
with a character string data type, and pass the length of the
entire record.
o If the language is Ada, BASIC, FORTRAN, or Pascal, pass
indicator arrays using an array descriptor (CLASS_A) and the
data type of all of the array elements.
o If the language is COBOL, pass arrays using fixed-length
(CLASS_S) descriptors and character string data types,
regardless of the data types of the array elements.
o If the language is C, the SQL module processor interprets
CHAR fields one way when the data type is defined in the
module, and another way when the definition is read from
the dictionary. When the data type is defined in the module,
the SQL module processor interprets character strings within
records as null-terminated strings. In other words, if you
declare a field specified as CHAR(9), the C module language
interprets this as a field that is actually 10 characters
long, with the tenth character being the null terminator.
However, if you include a record in a C module from the data
dictionary, you can specify any of three options for CHAR
field interpretation.
3.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.
3.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.
3.6 – char-data-types
Refer to the Oracle Rdb SQL Reference Manual for information
about the character data types that SQL supports.
3.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.
3.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.
3.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
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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).
3.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.
3.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.
3.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.
3.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.
3.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).
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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
3.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.
3.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.
3.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.
3.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.
3.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.
3.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;
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
3.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.
4 – Examples
Example 1: Calling an SQL module procedure from a Pascal program
The following example is a Pascal program that calls a procedure
in an SQL module file:
PROGRAM list_employees(OUTPUT);
{
Program to list employees' names whose last name matches a LIKE
predicate.
Note the following:
1) The input parameter (like_string) to the SELECT expression
in the DECLARE CURSOR is supplied on the OPEN_CURSOR call.
2) The output parameters are returned on each FETCH_INTO call.
3) The cursor is closed after the desired rows are processed,
so that it will be positioned properly in subsequent
operations.
}
TYPE
LAST_NAME = PACKED ARRAY[1..14] OF CHAR;
FIRST_NAME = PACKED ARRAY[1..10] OF CHAR;
VAR
{ Variable data }
sqlcode : INTEGER := 0;
emp_last : LAST_NAME;
emp_first: FIRST_NAME;
like_string : LAST_NAME := 'T_ _ _ _ _ _ _ _ _ _ _ _ _';
{ Declarations of entry points in the SQL module }
PROCEDURE SET_TRANS (VAR sqlcode : INTEGER); EXTERNAL;
PROCEDURE OPEN_CURSOR (VAR sqlcode: INTEGER;
name : LAST_NAME); EXTERNAL;
PROCEDURE FETCH_INTO (VAR sqlcode : INTEGER;
VAR last : LAST_NAME;
VAR first : FIRST_NAME); EXTERNAL;
PROCEDURE CLOSE_CURSOR (VAR sqlcode : INTEGER); EXTERNAL;
PROCEDURE ROLLBACK_TRANS (VAR sqlcode : INTEGER); EXTERNAL;
BEGIN
SET_TRANS (sqlcode); { Start a read-only transaction.}
OPEN_CURSOR (sqlcode, like_string);{ Open the cursor, supplying }
{ the string to match against. }
WRITELN('Matching Employees:'); { Print header. }
REPEAT { Iterate matching names. }
BEGIN
FETCH_INTO (sqlcode, emp_last, emp_first);{ Fetch the next name. }
IF sqlcode = 0
THEN
WRITELN(emp_first, emp_last); { Print employee information. }
END
UNTIL sqlcode <> 0;
IF sqlcode <> 100 { Print any error information. }
THEN
WRITELN ('SQL error code = ', sqlcode);
CLOSE_CURSOR (sqlcode); { Finish the cursor operation. }
ROLLBACK_TRANS (sqlcode); { Finish the transaction. }
END.
Here is the SQL module file that this program calls:
MODULE employees
LANGUAGE PASCAL
AUTHORIZATION SQL_USER
ALIAS RDB$DBHANDLE
DECLARE ALIAS FOR FILENAME PERSONNEL
DECLARE names CURSOR FOR
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE match_string
PROCEDURE SET_TRANS
SQLCODE;
SET TRANSACTION READ ONLY;
PROCEDURE OPEN_CURSOR
SQLCODE
match_string CHAR(14);
OPEN names;
PROCEDURE FETCH_INTO
SQLCODE
l_name CHAR(14)
f_name CHAR(10);
FETCH names INTO l_name, f_name;
PROCEDURE CLOSE_CURSOR
SQLCODE;
CLOSE names;
PROCEDURE ROLLBACK_TRANS
SQLCODE;
ROLLBACK;
Example 2: Calling an SQL module procedure from a C program
The following example is a C program that calls a procedure that
is in an SQL module file:
/*
C program to list employees' names where the last name matches a LIKE
predicate.
Note the following:
1) The input parameter (like_string) to the SELECT expression
in the DECLARE CURSOR is supplied on the OPEN_CURSOR call.
2) The output parameters are returned on each FETCH_INTO call.
3) The cursor is closed after the desired rows are processed,
so that it will be positioned properly in subsequent operations.
*/
#include <stdio.h>
#pragma dictionary "name"
typedef struct name NAME_TYPE;
extern void FETCH_INTO (int *sqlcode, NAME_TYPE *name_record);
typedef char LAST_NAME[15];
typedef int *SQLCODE;
/* Declarations of entry points in the SQL module */
extern void SET_TRANS (int *sqlcode);
extern void OPEN_CURSOR (int *sqlcode,
LAST_NAME name);
extern void CLOSE_CURSOR (int *sqlcode);
extern void ROLLBACK_TRANS (int *sqlcode);
void main ()
{
int sqlcode = 0;
NAME_TYPE name_record;
LAST_NAME like_string = "T%";
SET_TRANS (&sqlcode); /* Start a read-only transaction. */
if (sqlcode != 0) /* Print any error information. */
printf ("SQL error code = %d\n", sqlcode);
OPEN_CURSOR (&sqlcode, like_string); /* Open the cursor, supplying */
/* the string to match against. */
if (sqlcode != 0) /* Print any error information. */
printf ("SQL error code = %d\n", sqlcode);
printf ("Matching Employees:\n"); /* Print header. */
do /* Iterate matching names. */
{
FETCH_INTO (&sqlcode, &name_record);/* Fetch the next name. */
if (sqlcode == 0)
printf ("%s%s\n", name_record.f_name, name_record.l_name);
} /* Print employee information. */
while (sqlcode == 0);
if (sqlcode != 100) /* Print any error information. */
printf ("SQL error code = %d\n", sqlcode);
CLOSE_CURSOR (&sqlcode); /* Complete the cursor operation. */
if (sqlcode != 0) /* Print any error information. */
printf ("SQL error code = %d\n", sqlcode);
ROLLBACK_TRANS (&sqlcode); /* Finish the transaction. */
if (sqlcode != 0) /* Print any error information. */
printf ("SQL error code = %d\n", sqlcode);
}
Here is the SQL module file that this program calls:
MODULE employees
LANGUAGE C
AUTHORIZATION SQL_USER
ALIAS RDB$DBHANDLE
DECLARE ALIAS FOR PATHNAME 'MF_PERSONNEL'
DECLARE names CURSOR FOR
SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE match_string
PROCEDURE SET_TRANS
SQLCODE;
SET TRANSACTION READ ONLY;
PROCEDURE OPEN_CURSOR
SQLCODE
match_string CHAR(14);
OPEN names;
PROCEDURE FETCH_INTO
SQLCODE,
name_record RECORD FROM 'name' END RECORD;
FETCH names INTO name_record;
PROCEDURE CLOSE_CURSOR
SQLCODE;
CLOSE names;
PROCEDURE ROLLBACK_TRANS
SQLCODE;
ROLLBACK;
5 – Character Parameters
To ensure that you specify the length of character string
parameters correctly, use the following guidelines:
o For C host language programs that call SQL modules declared
with LANGUAGE C, any character parameters that correspond
to character data type columns must be defined as the length
of the longest valid column value in octets, plus 1 octet to
allow for the null terminator.
o For other host language programs (or C host language programs
that call SQL modules declared with LANGUAGE GENERAL), any
character parameters that correspond to character data type
columns must be defined as the length of the longest valid
column value in octets.
o When calculating the length of the longest valid column value,
you must take into consideration the number of octets for
each character in the character set of the column and whether
the SQL module language interprets the length of columns in
characters or octets. A program can control how the SQL module
language interprets the length of columns in the following
ways:
- The CHARACTER LENGTH clause of the module header or DECLARE
MODULE statement
- The DIALECT clause of the module header or DECLARE MODULE
statement
- For dynamic SQL, the SET CHARACTER LENGTH statement
6 – Host Language Data Types
The SQL data type specified for the formal parameter in a module
must be equivalent to the data type of the host language variable
declaration for the actual parameter. If the formal parameter and
actual parameter are not declared with equivalent data types, SQL
can give unpredictable results.
However, host languages typically do not support the same set of
data types that SQL supports. To work with a column in a database
defined with a data type not supported in a host language, the
module must declare formal parameters of a data type that the
host language supports. SQL automatically converts between the
data type of the database column and the formal parameter when it
processes the SQL statement in a procedure.
The following table shows the OpenVMS data types that SQL
requires for actual parameters when you declare formal parameters
for each SQL data type.
Table 4 SQL and Corresponding OpenVMS Data Types for Module
Language
Formal
Parameter Data
Type Requires Actual Parameter of OpenVMS Data Type
CHAR (n) Character string (DSC$K_DTYPE_T)
CHAR (n), Character string (DSC$K_DTYPE_T)
qualified by
character set
NCHAR (n) Character string (DSC$K_DTYPE_T)
VARCHAR (n) Varying character string (DSC$K_DTYPE_VT)
VARCHAR (n), Varying character string (DSC$K_DTYPE_VT)
qualified by
character set
NCHAR VARYING Varying character string (DSC$K_DTYPE_VT)
(n)
LONG VARCHAR Varying character string (DSC$K_DTYPE_VT)
TINYINT [(n)] Signed byte integer (DSC$K_DTYPE_B)
SMALLINT [(n)] Signed word integer (DSC$K_DTYPE_W)
INTEGER [(n)] Signed longword integer (DSC$K_DTYPE_L)
BIGINT [(n)] Signed quadword integer (DSC$K_DTYPE_Q)
QUADWORD [(n)] Signed quadword integer (DSC$K_DTYPE_Q)
DECIMAL Packed decimal string (DSC$K_DTYPE_P)
[(n)[,(n)]]
NUMERIC Numeric string, left separate sign (DSC$K_DTYPE_
[(n)[,(n)]] NL)
FLOAT [(n)] Single- or double-precision, floating-point
number, depending on n. For single-precision:
DSC$K_DTYPE_F or DSC$K_DTYPE_FS and for double-
precision: DSC$K_DTYPE_G, DSC$K_DTYPE_D, or
DSC$K_DTYPE_FT.
REAL Single-precision, floating-point number (DSC$K_
DTYPE_F or DSC$K_DTYPE_FS).
DOUBLE Double-precision, floating-point number (DSC$K_
PRECISION DTYPE_G, DSC$K_DTYPE_D, or DSC$K_DTYPE_FT).
(DATE) No equivalent OpenVMS data type; two-longword
array
DATE ANSI No equivalent OpenVMS data type; two-longword
array
DATE VMS Absolute date and time (DSC$K_DTYPE_ADT)
TIME No equivalent OpenVMS data type; two-longword
array
TIMESTAMP No equivalent OpenVMS data type; two-longword
array
INTERVAL (Year- No equivalent OpenVMS data type; two-longword
month) array
INTERVAL (Day- No equivalent OpenVMS data type; two-longword
time) array
LIST OF BYTE Not supported
VARYING
7 – SQLMOD Command Line
You can define a symbol to make invoking the SQL module processor
easier. For example:
$ SQLMOD == "$SQL$MOD"
You then can invoke the SQL module processor with or without a
module file specification:
o If you invoke the SQL module processor without a module file
specification, the module processor prompts you for it. For
example:
$ SQLMOD
INPUT FILE> module-file-specification
o If you invoke the SQL module processor with a module file
specification as part of the DCL command line, SQL starts
processing your module file immediately after you press the
Return key. For example:
$ SQLMOD module-file-specification
Either way, there are several qualifiers you can specify with
the file specification that control how SQL processes the module
file. The syntax diagram shows the format for those qualifiers.
7.1 – Format
(B)0[m[1mmodule-file-spec-qual = [m
[1m [m
[1;4mSQLMOD[m[1m qqqq> module-file-spec qwqqqqqqqqqqqqqqqqqqqqqqqqqwqq> [m
[1m tq> <context-file-name> qqu [m
[1m tq> module-qualifiers-1 qqu [m
[1m mq> module-qualifiers-2 qqj [m
[1m [m
(B)0[m[1mmodule-qualifiers-1 = [m
[1m [m
[1mqqwwqwqq> no-qualifiers-1 qqwqqqqqqqqqqqqqqqqqwwq> [m
[1m xx mqq> no-qualifiers-2 qqj xx [m
[1mxtq>[m [1m/[1;4mARCHITECTURE[m[1m = architecture_options[m [1mqqux[m
[1m xtq> [1;4m/C_STRING[m[1m = c-string-options qqqqqqqqqqux [m
[1m xtq> [1;4m/CONSTRAINT_MODE[m[1m = qwq> [1;4mIMMEDIATE[m[1m qqqqqux [m
[1m xx tq> [1;4mDEFERRED[m[1m qqqqqqux [m
[1m xx tq> [1;4mOFF[m[1m qqqqqqqqqqqux [m
[1m xx mq> [1;4mON[m[1m qqqqqqqqqqqqux [m
[1m xtq> [1;4m/CONTEXT[m[1m = qwq> [1;4mNONE[m[1m qqqqqqqqqqqqqqqqqqux [m
[1m xx tq> [1;4mALL[m[1m qqqqqqqqqqqqqqqqqqqux [m
[1m xx [m [1m mq> procedure-list qqqqqqqqux [m
[1mxtq> [1;4m/FLOAT[m [1m= qwq> D_FLOAT[m [1mqqqqqqqqqqqqqqqqqux[m
[1mxx[m [1m [m [1mtq>[m [1mG_FLOAT[m [1mqqqqqqqqqqqqqqqqqux[m
[1mxx[m [1mmq>[m [1mIEEE_FLOAT[m [1mqqqqqqqqqqqqqux[m
[1m xtq> [1;4m/USER_DEFAULT[m[1m = <username> qqqqqqqqqqqqux [m
[1m xmq> [1;4m/PASSWORD_DEFAULT[m[1m = <password> qqqqqqqqjx [m
[1m mqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mno-qualifiers-1 = [m
[1m [m
[1mq> / wqqqqwqwq> [1;4mALIGN_RECORDS[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1mm [1;4mNO[m[1m j[m [1mtq>[m [1;4mC_PROTOTYPES[m[1m qqwqqqqqqqqqqqqqqqqqqqqqwqqqqu[m
[1mx[m [1mmq> = <file-name> qqj[m [1mx[m
[1m tq> [1;4mCONNECT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mEXTERNAL_GLOBALS[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mFLAG_NONSTANDARD[m[1m qqwqqqqqqqqqqqqqqqqqqqwu [m
[1m x tq> = [1;4mMIA[m[1m qqqqqqqqqux [m
[1m x tq> = [1;4mSQL89[m[1m qqqqqqqux [m
[1m x mq> = [1;4mSQL92_ENTRY[m [1mqjx [m
[1m tq> [1;4mG_FLOAT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mINITIALIZE_HANDLES[m[1m qqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mLIST[m[1m qqqqwqqqqqqq>qqqqqqqqqwqqqqqqqqqqqqqqu [m
[1m x mq> = <file-spec> j x [m
[1mtq>[m [1;4mLOWERCASE_PROCEDURE_NAMES[m[1m qqqqqqqqqqqqqqqqu[m
[1m mq> [1;4mMACHINE_CODE[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqj [m
[1m [m
(B)0[m[1mno-qualifiers-2 = [m
[1m [m
[1mq> / wq>qqwqwq> [1;4mOBJECT[m[1m qqwqqqqqqq>qqqqqqqqqwqqqqqqqqqqqqqqqqwqqq>[m
[1m m [1;4mNO[m[1m j x mq> = <file-spec> j [m [1m x [m
[1m tq> [1;4mPACKAGE_COMPILATION[m[1m qqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m tq> [1;4mPARAMETER_CHECK[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1mtq>[m [1;4mPRAGMA[m[1m = ( q> [1;4mIDENT[m[1m = string-literal q> ) qqu[m
[1mtq> [1;4mPROTOTYPES[m[1m qqwqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqu[m
[1mx[m [1mmq> = <prototypesfile> qqj[m [1mx[m
[1m tq> [1;4mQUERY_ESTIMATES[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1mtq>[m [1;4mQUIET_COMMIT[m [1mqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu[m
[1m tq> [1;4mTRANSACTION_DEFAULT[m[1m qwqqqqqqqqqqqqqqqqqqwqqqu [m
[1m x tq> = [1;4mIMPLICIT[m[1m qqqqu [m [1m x [m
[1m x mq> = [1;4mDISTRIBUTED[m[1m qj [m [1m x [m
[1m mq> [1;4mWARN[m[1m qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqj [m
[1m [m [1m m> = ( w> warning-option w> ) qqj [m [1m [m
[1mmqqqqqq , <qqqqqqqj[m
(B)0[m[1mwarning-option = [m
[1m [m
[1mqqqwqqqwq> [1;4mWARNING[m[1m qqqqqqwqqwq> [m
[1m x tq> [1;4mNOWARNING[m[1m qqqqu x [m
[1m x tq> [1;4mDEPRECATE[m[1m qqqu x [m
[1m x mq> [1;4mNODEPRECATE[m[1m qj x [m
[1m mqqqqqqqqq , <qqqqqqqqqqqj [m
(B)0[m [1marchitecture_options =[m
[1m [m
[1mqwq> [1;4mGENERIC[m [1mqwqq>[m
[1m tq> [1;4mHOST[m[1m qqu [m
[1m tq> [1;4mEV4[m[1m qqu [m
[1m tq> [1;4mEV5[m[1m qqu [m
[1m tq> [1;4mEV56[m[1m qqu [m
[1mtq>[m [1;4mPCA56[m[1m qqu[m
[1mtq>[m [1;4mEV6[m[1m [m [1mqqu[m
[1mtq>[m [1;4mEV67[m[1m qqu[m
[1mtq>[m [1;4mEV68[m[1m qqu[m
[1mmq>[m [1;4mEV7[m[1m qqj[m
[1m [m [1m [m
[1m [m
(B)0[m[1mc-string-options = [m
[1m [m
[1mqwwqqwqqqqqqw> [1;4mBLANK_FILL[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqw>[m
[1m xx m> [1;4mNO[m[1m qj x [m
[1m xmqqwqqqqqqw> [1;4mFIXED_CDD_STRINGS[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu [m
[1m x m> [1;4mNO[m[1m qj x [m
[1m m> ( wqqqqqw> [1;4mBLANK_FILL[m[1m q> , wqqqqqqw> [1;4mFIXED_CDD_STRINGS[m[1m q> ) j [m
[1m m> [1;4mNO[m[1m j m> [1;4mNO[m[1m qj [m
[1m [m
(B)0[m[1mmodule-qualifiers-2 = [m
[1m [m
[1mqqqqwwq> /database-options qqqqqqqqqqqqqqqqqqqqqqqwwq> [m
[1m xtq> /optimization_options qqqqqqqqqqqqqqqqqqqux [m
[1m xtq> [1;4m/QUERY_TIME_LIMIT[m[1m = <total-seconds> qqqqqux [m
[1m xtq> [1;4m/QUERY_MAX_ROWS[m[1m = <total-rows> qqqqqqqqqqux [m
[1m xtq> [1;4m/QUERY_CPU_TIME_LIMIT[m[1m = <total-seconds> qux [m
[1m xmq> [1;4m/ROLLBACK_ON_EXIT[m[1m qqqqqqqqqqqqqqqqqqqqqqqjx [m
[1m mqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj [m
(B)0[m [1m [m [1m [m [1m [m
[1moptimization-options= [m
[1mqqqq> [1;4mOPTIMIZATION_LEVEL[m[1m= qwqqqq> [1;4mDEFAULT[m[1m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwq> [m
[1mmqq> ([m [1mqwwq> [1;4mAGGRESSIVE[m[4m_[1;4mSELECTIVITY[m[1m qqwqqwq[m [1m)[m [1mqj[m
[1m [m [1mxtq> [1;4mFAST[m[4m_[1;4mFIRST[m[1m [m [1mqqu[m [1mx[m [1m [m
[1m [m [1mxtq> [1;4mSAMPLED[m[4m_[1;4mSELECTIVITY[m [1mqqqqu[m [1mx[m [1m [m
[1m [m [1mxmq> [1;4mTOTAL[m[4m_[1;4mTIME[m[1m [m [1mqqqqqqqqqqqqj[m [1mx[m [1m [m
[1mmqqqqqq[m [1m, <qqqqqqqqqqqqqqqqqqqqqqj[m
[1m [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=V1[m[1m qqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mVIDA=V2[m[1m qqqqqqqqqqqqqqqqqu [m
[1m tqq> [1;4mVIDA=V2N[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[1mprocedure-list = [m
[1m [m
[1mqq> ( qwq> <procedure> wqqqqqqqqqqqqqqqqqqqwwq> ) q> [m
[1m x mqq> : <entry-name> jx [m
[1m mqqqqqqqqqqqqqqq , <qqqqqqqqqqqqqqqqqj [m
[1m [m
7.2 – Arguments
7.2.1 – ALIGN_RECORDS
Syntax options:
ALIGN_RECORDS
NOALIGN_RECORDS
Aligns the fields in an SQL module procedure record parameter.
If your host language is C, the default is ALIGN_RECORDS;
otherwise, the default is NOALIGN_RECORDS.
7.2.2 – ARCHITECTURE
Syntax options:
ARCHITECTURE=GENERIC
ARCHITECTURE=HOST
ARCHITECTURE=EV4
ARCHITECTURE=EV5
ARCHITECTURE=EV56
ARCHITECTURE=PCA56
ARCHITECTURE=EV6
ARCHITECTURE=EV67
ARCHITECTURE=EV68
ARCHITECTURE=EV7
For improved performance of generated code, the ARCHITECTURE
command line qualifier can be used on OpenVMS Alpha systems. The
ARCHITECTURE qualifier is ignored on Itanium systems.
The ARCHITECTURE qualifier specifies the lowest version of the
Alpha architecture where this code will run. This allows the
compiler to generate more efficient code, with the tradeoff that
code may not run on older systems.
All Alpha processors implement a core set of instructions and, in
some cases, the following extensions:
o Byte/word extension (BWX) - The instructions that comprise the
BWX extension are LDBU, LDWU, SEXTB, SEXTW, STB, and STW.
o Square-root and floating-point convert extension (FIX) - The
instructions that comprise the FIX extension are FTOIS, FTOIT,
ITOFF, ITOFS, ITOFT, SQRTF, SQRTG, SQRTS, and SQRTT.
o Count extension (CIX) - The instructions that comprise the CIX
extension are CTLZ, CTPOP, and CTTZ.
o Multimedia extension (MVI) - The instructions that comprise
the MVI extension are MAXSB8, MAXSW4, MAXUB8, MAXUW4, MINSB8,
MINSW4, MINUB8, MINUW4, PERR, PKLB, PKWB, UNPKBL, and UNPKBW.
The Alpha Architecture Reference Manual describes the extensions
in detail.
The keyword specified with the ARCHITECTURE qualifier determines
which instructions the compiler can generate and which coding
rules it must follow.
o GENERIC - Generate instructions that are appropriate for all
Alpha processors. This option is the default and is equivalent
to /ARCH=EV4.
o HOST - Generate instructions for the processor that the
compiler is running on (for example, EV56 instructions on
an EV56 processor, EV7 instructions on an EV7 processor, and
so on).
o EV4 - Generate instructions for the EV4 processor (21064,
20164A, 21066, and 21068 chips). Applications compiled with
this option will not incur any emulation overhead on any Alpha
processor.
o EV5 - Generate instructions for the EV5 processor (some 21164
chips). (Note that the EV5 and EV56 processors both have the
same chip number - 21164.) Applications compiled with this
option will not incur any emulation overhead on any Alpha
processor.
o EV56 - Generate instructions for EV56 processors (some 21164
chips). This option permits the compiler to generate any
EV4 instruction plus any instructions contained in the BWX
extension. Applications compiled with this option may incur
emulation overhead on EV4 and EV5 processors.
o PCA56 - Generate instructions for PCA56 processors (21164PC
chips). This option permits the compiler to generate any EV4
instruction plus any instructions contained in the BWX and MVI
extensions. Applications compiled with this option may incur
emulation overhead on EV4 and EV5 processors.
o EV6 - Generate instructions for EV6 processors (21264
chips). This option permits the compiler to generate any
EV4 instruction, any instruction contained in the BWX and
MVI extensions, plus any instructions added for the EV6
chip. These new instructions include a floating-point square
root instruction (SQRT), integer/floating-point register
transfer instructions, and additional instructions to identify
extensions and processor groups. Applications compiled with
this option may incur emulation overhead on EV4, EV5, EV56,
and PCA56 processors.
o EV67 or EV68 - Generate instructions for EV67 and EV68
processors (21264A chips). This option permits the
compiler to generate any EV6 instruction plus the new bit
count instructions (CTLZ, CTPOP, and CTTZ). However, the
precompilers do not currently generate any of the new bit
count instructions and the EV67 and EV68 have identical
instruction scheduling models so the EV67 and EV68 are
essentially identical to the EV6. Applications compiled with
this option may incur emulation overhead on EV4, EV5, EV56,
and PCA56 processors.
o EV7 - Generate instructions for the EV7 processor (21364
chip). This option permits the compiler to generate any EV67
instruction. There are no additional instructions available
on the EV7 processor but the compiler does have different
instruction scheduling and prefetch rules for tuning code
for the EV7. Applications compiled with this option may incur
emulation overhead on EV4, EV5, EV56, and PCA56 processors.
The OpenVMS Alpha operating system includes an instruction
emulator. This capability allows any Alpha chip to execute and
produce correct results from Alpha instructions even if some of
the instructions are not implemented on the chip. Applications
using emulated instructions will run correctly but may incur
significant emulation overhead at run time.
Of the available extension types, the Byte/word extension (BWX)
will often be beneficial for increased performance of Rdb-based
applications. In addition, for those Alpha implementations
that support quad-issue of instructions (the EV6 and later
processors), the compiler does have different instruction
scheduling and prefetch rules for tuning code.
For highest levels of performance of generated code, Oracle
recommends that the ARCHITECTURE qualifier be specified with
the keyword that most closely matches the lowest processor type
of the machine where the program will execute.
LANGUAGE COMPILER SUPPORT FOR ARCHITECTURE
If specified, the ARCHITECTURE qualifier is passed on the
command line to the specified language compiler by the SQL
Precompiler. The language compiler being used must support
the ARCHITECTURE qualifier and the architecture keyword
value when the ARCHITECTURE qualifier is specified.
7.2.3 – C_PROTOTYPES
Syntax options:
C_PROTOTYPES=file-name
NOC_PROTOTYPES
This qualifier is deprecated and will not be supported in a
future release. It has been replaced by the PROTOTYPES qualifier.
7.2.4 – C_STRING
Syntax options:
C_STRING=[NO]BLANK_FILL
C_STRING=[NO]FIXED_CDD_STRINGS
C_STRING=([NO]BLANK_FILL, [NO]FIXED_CDD_STRINGS)
Specifies how to handle C host language character strings:
o [NO]BLANK_FILL (default: BLANK_FILL)
Controls whether or not C character strings are filled with
blanks as required by the SQL89 and ANSI/ISO SQL standards or
if the null terminator is placed after the last data byte of
the source string.
o [NO]FIXED_CDD_STRINGS (default: NOFIXED_CDD_STRINGS)
Controls whether or not SQL treats C character strings from
Oracle CDD/Repository record definitions as fixed-length
character strings or C null-terminated strings.
7.2.5 – c-string-options
Controls how SQL handles C host language character strings.
Use either or both of the [NO]BLANK_FILL and [NO]FIXED_CDD_
STRINGS keywords with the C_STRING qualifier to control C string
characteristics.
7.2.6 – CONNECT
Syntax options:
CONNECT
NOCONNECT
Specifies whether or not SQL allows multiple user sessions and
access to global databases across modules. All SQL modules in
an application must be compiled with connections enabled or
disabled.
The default setting is NOCONNECT.
7.2.7 – CONSTRAINT_MODE
Syntax options:
CONSTRAINT_MODE=IMMEDIATE
CONSTRAINT_MODE=DEFERRED
CONSTRAINT_MODE=ON
CONSTRAINT_MODE=OFF
You can optionally specify either the CONSTRAINT_MODE=IMMEDIATE
or CONSTRAINT_MODE=DEFERRED qualifier on the SQL module language
command line to set the default constraint evaluation mode for
commit-time constraints. (This qualifier does not affect the
evaluation of verb-time constraints.) The default is DEFERRED;
that is, commit-time constraints are evaluated at commit time.
Setting constraints ON causes each of the affected constraints
to be evaluated immediately, as well as at the end of each
statement, until the SET ALL CONSTRAINTS OFF statement is issued
or until the transaction completes with a commit or rollback
operation.
The SET ALL CONSTRAINTS statement overrides the CONSTRAINT_
MODE=IMMEDIATE or the CONSTRAINT_MODE=DEFERRED qualifier.
SQL users who require ANSI-standard SQL compatibility should set
constraints IMMEDIATE. The default (CONSTRAINT_MODE=DEFERRED) is
acceptable for most other users.
You can use the ON keyword instead of IMMEDIATE and the OFF
keyword instead of DEFERRED.
7.2.8 – CONTEXT=
Instructs the SQL module processor to execute module language
procedures in the context of a particular distributed
transaction. When you use this qualifier, SQL generates an
additional parameter for the procedures and places the parameter
as the last parameter declared in the procedure.
Following are the options you can specify with the CONTEXT=
qualifier:
o NONE
Specifies that the SQL module processor does not add a context
parameter to any procedure in the module.
o ALL
Specifies that the SQL module processor adds a context
parameter to every procedure in the module.
o procedure-list
Specifies that the SQL module processor adds a context
parameter to each procedure listed. If you specify an entry
name for a procedure in the list, the SQL module processor
changes the name of that procedure to the name specified.
For example, you can specify the following qualifier on the
command line:
/CONTEXT=(OPEN_PROC :OPEN_PROC_DIST, FETCH_PROC :FETCH_PROC_DIST,-
CLOS_PROC :CLOS_PROC_DIST)
SQL passes the context parameter to the OPEN_PROC, FETCH_
PROC, and CLOS_PROC procedures and gives them the new names
specified. For more information, see the Oracle Rdb7 Guide to
Distributed Transactions.
Your application must use the context structure to pass the
address of the distributed TID from the host language program
to procedures in the module that are involved in the distributed
transaction. You pass the context structure to procedures that
contain executable SQL statements, except statements that
you cannot execute when a transaction is already started or
statements that you cannot use when you explicitly call the
DECdtm system services. the Oracle Rdb SQL Reference Manual
lists the nonexecutable statements that do not take a context
structure.
You can also use the CONTEXT qualifier to specify a new name for
a procedure.
Qualifiers used with the CONTEXT qualifier specify which
procedures receive context parameters, and whether or not the
names of the procedures are changed.
Because you cannot use batch-update transactions with distributed
transactions, you should define the SQL$DISABLE_CONTEXT logical
name as True before you start a batch-update transaction.
(Distributed transactions require that you are able to roll back
transactions. Because batch-update transactions do not write to
recovery-unit journal (.ruj) files, batch-update transactions
cannot be rolled back.)
If you attempt to start a distributed transaction using a batch-
update transaction, what happens depends upon whether you call
the DECdtm system services implicitly or explicitly and which SQL
statement you use to start the transaction:
o If you start a batch-update transaction and explicitly call
the DECdtm system services, SQL returns an error at compile
time.
o If you start a batch-update transaction and implicitly call
the DECdtm system services, SQL takes the following actions:
- If you use a SET TRANSACTION statement with the BATCH
UPDATE clause, SQL starts a nondistributed transaction.
- If you use a DECLARE TRANSACTION statement with the BATCH
UPDATE clause, SQL returns an error at compile time.
The two-phase commit protocol applies only to distributed
transactions. For more information about distributed
transactions, see the Oracle Rdb7 Guide to Distributed
Transactions.
7.2.9 – context-file-name
A SQL command procedure containing DECLARE statements that you
want to apply when your program compiles and executes. See the
Oracle Rdb SQL Reference Manual for more information.
7.2.10 – database-options
Specifies that the SQL module processor will process a program
for access to the specified database type.
For more information regarding database options, see the Oracle
Rdb SQL Reference Manual
7.2.11 – DEPRECATE
Syntax options:
DEPRECATE
NODEPRECATE
The DEPRECATE and NODEPRECATE qualifiers specify whether or
not the SQL module processor writes diagnostic messages about
deprecated features.
Deprecated features are currently allowed features that will
not be allowed in future versions of SQL; that is, they will
be obsolete. For example, one deprecated feature is the use
of obsolete keywords such as VERB_TIME instead of VERB TIME.
A complete list of deprecated features appears on line in the
interactive SQL Help utility.
You can specify the WARN=WARNING qualifier if you prefer to have
all warning messages except those about deprecated features.
You can specify the WARN=(NOWARNING, DEPRECATE) qualifier if you
prefer only the deprecated feature messages. The WARN qualifier
alone is equivalent to the WARN=(WARNING, DEPRECATE) qualifier,
which means that SQL writes informational and warning messages,
plus messages about deprecated features. The NOWARN qualifier
alone is equivalent to the WARN=(NOWARNING, NODEPRECATE)
qualifier, which means that SQL does not write any messages.
7.2.12 – EXTERNAL_GLOBALS
Syntax options:
EXTERNAL_GLOBALS
NOEXTERNAL_GLOBALS
Specifies whether or not alias references are coerced into alias
definitions. An alias definition is an alias declared with the
GLOBAL keyword (the default) in the DECLARE ALIAS statement. An
alias reference is an alias declared with the EXTERNAL keyword in
the DECLARE ALIAS statement.
The EXTERNAL_GLOBALS qualifier treats alias references as alias
definitions. This qualifier provides compatibility with versions
prior to V7.0.
The NOEXTERNAL_GLOBALS qualifier treats alias references as alias
references. The NOEXTERNAL_GLOBALS qualifier may be useful on
OpenVMS if your application shares an alias between multiple
shareable images.
The default setting is EXTERNAL_GLOBALS.
For information on using aliases and shareable images, see the
Oracle Rdb Guide to SQL Programming.
7.2.13 – FLAG_NONSTANDARD
Syntax options:
FLAG_NONSTANDARD
FLAG_NONSTANDARD =SQL92_ENTRY
FLAG_NONSTANDARD =SQL89
FLAG_NONSTANDARD =MIA
NOFLAG_NONSTANDARD
Specifies whether or not SQL identifies nonstandard syntax.
Nonstandard syntax, called an extension, refers to syntax that
is not part of the ANSI/ISO SQL standard or the Multivendor
Integration Architecture (MIA) standard. You can specify the
following options:
o FLAG_NONSTANDARD
Notifies you of syntax that is an extension to the ANSI/ISO
SQL standard.
o FLAG_NONSTANDARD=SQL92_ENTRY
Notifies you of syntax that is an extension to the ANSI/ISO
SQL standard. This qualifier has the same effect on flagging
as does the FLAG_NONSTANDARD qualifier.
o FLAG_NONSTANDARD=SQL89
Notifies you of syntax that is an extension to the ANSI/ISO
1989 standard.
o FLAG_NONSTANDARD=MIA
Notifies you of syntax that is an extension to the MIA
standard.
o NOFLAG_NONSTANDARD
Prevents notification of extensions.
Preventing notification of extensions (NOFLAG_NONSTANDARD) is the
default.
7.2.14 – FLOAT
Syntax options:
FLOAT=D_FLOAT
FLOAT=G_FLOAT
FOAT=IEEE_FLOAT
The /FLOAT qualifier determines the conversion that SQL Module
language performs on SQL Module Language procedure parameters
declared as single or double precision floating point SQL
datatypes. SQL floating point datatypes are FLOAT(n), REAL, and
DOUBLE PRECISION. See the Oracle Rdb SQL Reference Manual for
details. Internally to Oracle Rdb, single precision floating
point types are represented as F-Floating while double precision
floating point types are represented and G-Floating. See SQL and
Corresponding OpenVMS Data Types for Module Language in Section
3.4 of the Oracle Rdb SQL Reference Manual for more details.
By default, parameters declared as single or double precision
floating point type are expected to be passed by the calling
host language program in F-Floating and G-Floating format,
respectively. This is equivalent to using a qualifier of
/FLOAT=G_FLOAT with the SQL$MOD command.
If the command line for SQL$MOD has /FLOAT=D_FLOAT, then the
single and double precision floating point parameters are
expected to be in F-Floating and D-Floating format respectively.
SQL Module Language will convert the double precision parameters
between D-Floating and G-Floating formats for both input and
output.
If the command line for SQL$MOD has /FLOAT=IEEE_FLOAT, the single
and double precision floating point parameters are expected to
be in IEEE S-Floating and IEEE T-Floating format, respectively.
SQL Module Language will convert between these formats and the
internal F-Floating and G-Floating formats for both input and
output.
If a parameter of an SQL Module Language procedure is of a record
type, any fields of the record which are of floating point types
follow the same rules as described above.
The floating point formats of the host language program actual
parameters must agree with the format expected by the SQL Module
Language actual parameter. See the Oracle Rdb SQL Reference
Manual for information concerning actual and formal parameter
agreement.)
NOTES
Oracle Rdb always stores floating point numbers internally
using the VAX 32-bit and 64-bit types called F-Floating (F_
FLOAT) and G-Floating (G_FLOAT), respectively. This means
that when IEEE formats are used in a host language program,
Oracle Rdb converts back and forth between the VAX and IEEE
formats. There are differences in the number of available
bits in the fraction and exponent between these formats.
Additionally, the IEEE formats have certain exponent values
reserved for infinity values. These differences can cause
floating point overflow or underflow as well as rounding
errors during the conversion process. See Appendix A of the
Portable Mathematics Library in the OpenVMS Operating System
documentation for data on the maximum and minimum values for
VAX versus IEEE floating point formats.
When /FLOAT=IEEE_FLOAT is used, floating point data types
may not be imported from the Common Data Dictionary.
7.2.15 – G_FLOAT
Syntax options:
G_FLOAT
NOG_FLOAT
The /G_FLOAT and /NOG_FLOAT qualifiers are for backwards
compatibility. They are equivalent to /FLOAT=G_FLOAT and
/FLOAT=D_FLOAT, respectively. You should not specify both /FLOAT
and /[NO]G_FLOAT qualifiers.
7.2.16 – INITIALIZE_HANDLES
Syntax options:
INITIALIZE_HANDLES
NOINITIALIZE_HANDLES
Specifies whether or not alias definitions are coerced into alias
references. The NOINITIALIZE_HANDLES qualifier causes all alias
declarations to be treated as alias references.
An alias definition is an alias declared with the GLOBAL keyword
(the default) in the DECLARE ALIAS statement. An alias reference
is an alias declared with the EXTERNAL keyword in the DECLARE
ALIAS statement.
The NOINITIALIZE_HANDLES qualifier may be useful for existing
source code on OpenVMS in coercing alias definitions into alias
references. Because there is usually no distinction between a
definition and a reference on OpenVMS, your application might
declare an alias definition where an alias reference is needed.
If you reorganize your application into multiple images that
share aliases, you must distinguish the alias definition from
the alias reference. In this case, use the NOINITIALIZE_HANDLES
qualifier to coerce a definition into a reference without
changing your source code.
If your application correctly declares alias references with the
EXTERNAL keyword, use the NOEXTERNAL_GLOBALS qualifier, instead
of the [NO]INITIALIZE_HANDLES to override the default on OpenVMS
and cause SQL to treat alias references properly as references.
The default setting is INITIALIZE_HANDLES. This qualifier
overrides the [NO]EXTERNAL_GLOBALS qualifier.
This qualifier is maintained for compatibility with previous
versions of Oracle Rdb. Use the [NO]EXTERNAL_GLOBALS qualifier,
which provides more precise control over alias definition. For
information on using aliases and shareable images, see the Oracle
Rdb Guide to SQL Programming.
7.2.17 – LIST
Syntax options:
LIST
NOLIST
Determines whether or not the SQL module processor creates a list
file containing the original module list along with any error
messages from the processing, and, if it does, what it is named.
The NOLIST qualifier is the default. If you specify the LIST
qualifier and do not include a file specification, the SQL module
processor creates a list file with the same file name as your
module source file with the file extension .lis.
7.2.18 – LOWERCASE_PROCEDURE_NAMES
Syntax options:
LOWERCASE_PROCEDURE_NAMES
NOLOWERCASE_PROCEDURE_NAMES
Forces the names of the module language procedures to be in
lowercase. This qualifier not only assumes that the SQL module
procedure names are in lowercase, it overrides the case in any
quoted SQL module procedure.
The default setting is NOLOWERCASE_PROCEDURE_NAMES.
7.2.19 – MACHINE_CODE
Syntax options:
MACHINE_CODE
NOMACHINE_CODE
Oracle Rdb determines whether or not the SQL module processor
includes machine code in the list (.lis) file; however, to
generate the list file with the machine code in it, you must
also specify the LIST qualifier.
The NOMACHINE_CODE qualifier is the default.
7.2.20 – module-file-spec
The file specification for an SQL module source file. The default
file extension for the source file is .sqlmod.
7.2.21 – module-qualifiers
A set of qualifiers that you can optionally apply to the SQL
module processor command line.
7.2.22 – no-qualifiers
You can add the NO prefix to negate any qualifier in this group.
7.2.23 – OBJECT
Syntax options:
OBJECT
NOOBJECT
Specifies whether or not the SQL module processor creates an
object file when compiling the source file if the compilation
does not generate fatal errors; and, if an object file is
produced, what the file is named. The OBJECT qualifier is the
default. If you specify the OBJECT qualifier and do not include
a file specification, the SQL module processor creates an object
file with the same file name as the source file and with the file
extension .obj.
7.2.24 – OPTIMIZATION_LEVEL
Specifies the optimizer strategy to be used to process all
queries within your SQL module language program. Select the:
o AGGRESSIVE_SELECTIVITY option if you expect a small number of
rows to be selected.
o DEFAULT option to accept the Oracle Rdb defaults: FAST_FIRST
and DEFAULT SELECTIVITY. strategy.
o FAST_FIRST option if you want your program to return data to
the user as quickly as possible, even at the expense of total
throughput.
o SAMPLED_SELECTIVITY option to use literals in the query to
perform preliminary estimation on indices.
o TOTAL_TIME option if you want your program to run at the
fastest possible rate, returning all the data as quickly as
possible. If your application runs in batch, accesses all the
records in a query, and performs updates or writes reports,
you should specify TOTAL_TIME.
You can select either the TOTAL_TIME or the FAST_FIRST option in
conjunction with either the AGGRESSIVE_SELECTIVITY or SAMPLED_
SELECTIVITY option. Use a comma to separate the keywords and
enclosed the list in parentheses.
The following example shows how to use the OPTIMIZATION_LEVEL
qualifier:
$ SQL$MOD/OPTIMIZATION_LEVEL=(TOTAL_TIME,SAMPLED_SELECTIVITY) APPCODE.SQLMOD
Any query that explicitly includes an OPTIMIZE WITH, or OPTIMIZE_
FOR clause is not affected by the settings established using the
OPTIMIZATION_LEVEL qualifier.
You affect the optimizer strategy of static SQL queries with the
optimization level qualifier; however, the default optimizer
strategy set by the OPTIMIZATION_LEVEL qualifier can be
overridden by the default optimizer strategy set in a top-level
SELECT statement.
In contrast, the SET OPTIMIZATION LEVEL statement specifies the
query optimization level for dynamic SQL query compilation only;
the statement does not affect the SQL compile-time environment
nor does it affect the run-time environment of static queries.
7.2.25 – PACKAGE_COMPILATION
Syntax options:
PACKAGE_COMPILATION
NOPACKAGE_COMPILATION
Determines if a package specification is produced and loaded into
the ACS library.
Oracle Rdb produces a package specification when you process
a module with the LANGUAGE ADA clause specified in the module
header unless you specify the NOPACKAGE_COMPILATION qualifier.
The NOPACKAGE_COMPILATION qualifier prevents the package
specification from being loaded in the ACS library, but still
creates and compiles the .ada file.
The PACKAGE_COMPILATION qualifier is the default.
7.2.26 – PARAMETER_CHECK
Syntax options:
PARAMETER_CHECK
NOPARAMETER_CHECK
Specifies whether or not the SQL module processor compares
the number of formal parameters declared for a procedure with
the number of parameters specified in the SQL statement of the
procedure:
o PARAMETER_CHECK (default)
Checks that parameter counts match and generates an error at
run time (not compile time) when they do not.
o NOPARAMETER_CHECK
Suspends checking parameters to improve module compilation
time. Consider using the NOPARAMETER_CHECK qualifier after you
have debugged your SQL module.
SQL checks parameter counts by default. To improve module
compilation time, you must explicitly use the NOPARAMETER_CHECK
qualifier.
7.2.27 – PASSWORD_DEFAULT
Specifies the user's password at compile time.
If you use the USING DEFAULT clause of the DECLARE ALIAS
statement, you use this qualifier to pass the compile-time user's
password to the program.
7.2.28 – PRAGMA
Syntax options:
PRAGMA = IDENT = string-literal
NOPRAGMA
Using the IDENT keyword with the PRAGMA qualifier allows the user
to pass a text string to the SQL Module Language compiler to be
written to the Object Module Header. This is a way to note the
generation of the compiler module.
If the PRAGMA (IDENT ...) clause is used as part of the DECLARE
MODULE statement, then that value will override any value used on
the command line.
The ANALYZE/OBJECT and LIBRARY command can be used to display
this ident string, and the value will be displayed in LINKER map
files.
OpenVMS limits the IDENT string to a 15 octet string. If the
string is longer than this (even with trailing spaces) then an
error will be reported by the SQL precompiler.
The following example demonstrates the use of the qualifier to
establish the generation of the compiler module.
$ SQL$MOD TEST/PRAGMA=IDENT="v1.2-32"
7.2.29 – PROTOTYPES
Syntax options:
PROTOTYPES[=prototypesfile]
NOPROTOTYPES
The PROTOTYPES qualifier uses the LANGUAGE clause from the module
to generate routine declarations for the following languages: C
(C++), Pascal, and BLISS. The qualifier is ignored for all other
language values.
The prototypes file specification defaults to the same device,
directory, and file name as the module language source. The file
types default to .h for C, .PAS for Pascal, and .REQ for BLISS.
For the BLISS language, the PROTOTYPES qualifier generates
EXTERNAL ROUTINE declarations for each SQL module language
procedure.
For the Pascal language, the generated external procedure
declarations are suitable for inclusion in either a Pascal
program or module. Structured types (RECORD ... END RECORD),
SQLDA, and SQLCA used by the SQL module language procedures are
declared as UNSAFE arrays of bytes to simplify passing structures
via these external definitions. However, care must be taken as
this form of declaration disables the strong typing checks in
Pascal.
The output for the C language includes pre-processor directives
to conditionally include C++ "extern C" syntax and also allow
multiple #include references.
The default setting is NOPROTOTYPES.
7.2.30 – QUERY_CPU_TIME_LIMIT
Limits the amount of CPU time used to optimize a query for
execution. If the query is not optimized and prepared for
execution before the CPU time limit is reached, an error message
is returned.
The default is unlimited time for the query to compile. Dynamic
SQL options are inherited from the compilation qualifier.
7.2.31 – QUERY_ESTIMATES
Syntax options:
QUERY_ESTIMATES
NOQUERY_ESTIMATES
Specifies whether or not SQL returns the estimated number of
rows and estimated number of disk I/O operations in the SQLCA
structure. If you specify the default, which is the QUERY_
ESTIMATES qualifier, SQL returns the estimated number of rows
in the field SQLCA.SQLERRD[2] and the estimated number of disk
I/O operations in the field SQLCA.SQLERRD[3]. The value of
SQLCA.SQLERRD[2] and SQLCA.SQLERRD[3] is normally 0 after you
execute an OPEN statement for a table.
The following example shows interactive SQL output from a
statement that accesses the INTRO_PERSONNEL database. The
database was loaded using the sample program SQL$INTRO_LOAD_
EMPL_C.SQLMOD with the QUERY_ESTIMATES qualifier on the module
language command line. The SQLCA.SQLERRD[2] field shows that SQL
estimates 100 rows. The SQLCA.SQLERRD[3] field shows that SQL
estimates 16 disk I/O operations.
$ SQL$
SQL> ATTACH 'FILENAME intro_personnel';
SQL> DECLARE MY_CURSOR
cont> TABLE CURSOR FOR
cont> SELECT * FROM EMPLOYEES;
SQL> OPEN MY_CURSOR;
SQL> SHOW SQLCA;
SQLCA:
SQLCAID: SQLCA SQLCABC: 128
SQLCODE: 0
SQLERRD: [0]: 0
[1]: 0
[2]: 100
[3]: 16
[4]: 0
[5]: 0
SQLWARN0: SQLWARN1: SQLWARN2:
SQLWARN3: SQLWARN4: SQLWARN5:
SQLWARN6: SQLWARN7:
7.2.32 – QUERY_MAX_ROWS
Limits the number of records returned during query processing by
counting the number of rows returned by the query and returning
an error message if the query exceeds the total number of rows
specified.
The default is an unlimited number of record fetches. Dynamic SQL
options are inherited from the compilation qualifier.
7.2.33 – QUERY_TIME_LIMIT
Limits the number of records returned during query processing
by counting the number of seconds used to process the query and
returning an error message if the query exceeds the total number
of seconds specified.
The default is unlimited time for the query to compile. Dynamic
SQL options are inherited from the compilation qualifier.
7.2.34 – ROLLBACK_ON_EXIT
Rolls back outstanding transactions when a program exits from
SQL.
On OpenVMS outstanding transactions are committed when a program
exits from SQL by default. Therefore, if you want to roll back
changes, specify this qualifier on the command line.
7.2.35 – TRANSACTION_DEFAULT
Syntax options:
TRANSACTION_DEFAULT=IMPLICIT
TRANSACTION_DEFAULT=DISTRIBUTED
NOTRANSACTION_DEFAULT
Specifies when SQL starts a transaction and how SQL handles
default distributed transactions. You can specify the following
options:
o TRANSACTION_DEFAULT=IMPLICIT
Causes SQL to start a transaction when you issue either a SET
TRANSACTION statement or the first executable SQL statement in
a session.
o TRANSACTION_DEFAULT=DISTRIBUTED
Causes SQL to use the distributed transaction identifier (TID)
for the default distributed transaction established by the
DECdtm system service SYS$START_TRANS. Using this option
eliminates the need to declare context structures in host
language programs and to pass context structures to SQL module
procedures. Because it closes all cursors, it also eliminates
the need to call the SQL_CLOSE_CURSORS routine.
You must explicitly call the DECdtm system services when you
use this option.
This option provides support for the Structured Transaction
Definition Language (STDL) of the Multivendor Integration
Architecture (MIA) standard.
If you specify the TRANSACTION_DEFAULT=DISTRIBUTED option with
the CONTEXT qualifier, you must declare a context structure
and pass the context structure to the statements named in
the CONTEXT qualifier or, if you specify CONTEXT=ALL, to most
executable statements involved in the distributed transaction.
See the Oracle Rdb SQL Reference Manual for information
about which executable statements do not require a context
structure.
o NOTRANSACTION_DEFAULT
Prevents SQL from starting a transaction unless you execute
a SET TRANSACTION statement. If you use this qualifier and
issue an executable statement without first issuing a SET
TRANSACTION statement, SQL returns an error.
The default is TRANSACTION_DEFAULT=IMPLICIT.
7.2.36 – USER_DEFAULT
Specifies the user name at compile time.
If you use the USER DEFAULT clause of the DECLARE ALIAS
statement, you use this qualifier to pass the compile-time user
name to the program.
7.2.37 – WARNING
Syntax options:
WARNING
NOWARNING
You can use combinations of the warning options to specify which
warning messages the SQL module processor writes. If you specify
only a single warning option, you do not need the parentheses.
The WARNING and NOWARNING qualifiers specify whether or not the
SQL module processor writes informational and warning messages.
7.2.38 – warning-option
Specifies whether the SQL module processor writes informational
and warning messages to your terminal, a list file, or both.
The WARN qualifier is the default. You can specify two warning
options with the WARN qualifier to customize message output.
You cannot specify warning options if you specify the NOWARN
qualifier.
7.3 – Example
Example 1: Compiling and linking a program with an SQL module
$ SQLMOD :== $SQL$MOD
$ SQLMOD LIST_EMP_PASMOD.SQLMOD
$ PASCAL LIST_EMP.PAS
$ ! This LINK command requires that the logical name
$ ! LNK$LIBRARY is defined as SYS$LIBRARY:SQL$USER.OLB
$ LINK LIST_EMP.OBJ, LIST_EMP_PASMOD.OBJ
$ RUN LIST_EMP.EXE
Matching Employees:
Alvin Toliver
Louis Tarbassian