Allows a programmer using Dynamic SQL to alter the way the SQLDA
(and SQLDA2) and Dynamic SQL statements are processed by Oracle
Rdb.
1 – Environment
You can use the SET SQLDA statement:
o In Dynamic SQL as a statement to be dynamically executed
2 – Format
(B)0[m[1;4mSET[m[1m [1;4mSQLDA[m[1m qwq> literal qqqqqqqqqwqqqqq> [m
[1m mq> host-variable qqqj [m
(B)0[m[1msqlda_options = [m
[1m [m
[1m qqwq> sqlda_option qqwqq>[m
[1mmqqqqq[m [1m,[m [1m<qqqqqqqqqj[m
(B)0[m[1msqlda_option = [m
[1m [m
[1m qqwq> [1;4mPADDING[m[1m n [1;4mCHARACTERS[m[1m qqqqqwq>[m
[1mtq> [1;4mNOPADDING[m[1m qqqqqqqqqqqqqqqqu[m
[1mtq>[m [1;4mENABLE[m[1m enable-option qqqqqu[m
[1mtq>[m [1;4mDISABLE[m[1m enable-option qqqqu[m
[1mmq>[m [1mdialect-name qqqqqqqqqqqqqj[m
(B)0[m[1menable-option = [m
[1m [m
[1m qqwq> [1;4mINSERT[m[1m [1;4mRETURNING[m[1m qqqwq>[m
[1mtq> [1;4mNAMED[m[1m [1;4mMARKERS[m[1m qqqqqqu[m
[1mmq>[m [1;4mROWID[m[1m [1;4mTYPE[m[1m qqqqqqqqqj[m
(B)0[m[1mdialect-name = [m
[1m [m
[1m qqwq> [1;4mSQL99[m[1m qqqqqqqqqqqwq>[m
[1mtq> [1;4mSQL92[m[1m qqqqqqqqqqqu[m
[1mtq>[m [1;4mSQL89[m[1m qqqqqqqqqqqu[m
[1mtq>[m [1;4mMIA[m[1m qqqqqqqqqqqqqu[m
[1mtq>[m [1;4mSQLV40[m[1m qqqqqqqqqqu[m
[1mtq>[m [1;4mORACLE[m[1m [1;4mLEVEL1[m[1m qqqu[m
[1mmq> [1;4mORACLE[m[1m [1;4mLEVEL2[m[1m qqqj[m
3 – Arguments
3.1 – Literal|Host-Variable
Parameter passed to the statement. Must be a literal or a host
variable containing one or more SQLDA options (see sqlda_
options syntax diagram for details). If more than one option
is specified, they must be separated by commas.
3.2 – sqlda_options
One or more keyword clauses. If more than one clause is
specified, they must be separated by commas.
3.3 – ENABLE
The ENABLE clause activates one of the following behaviors for
Dynamic SQL.
- INSERT RETURNING - The default behavior of INSERT ...
RETURNING when executed by dynamic SQL is to place parameters
from the RETURNING INTO clause in to the INPUT SQLDA. This
behavior is maintained for backward compatibility. This option
allows the programmer to force different (and corrected)
behavior for the non-compound use of this statement.
NOTE
If the INSERT RETURNING statement is included in a
compound statement then the parameters are handled
correctly.
- NAMED MARKERS - as well as traditional parameters markers
(?). Dynamic SQL will now accept named, host-variable style
parameter markers. See the Usage Notes for further details and
examples.
- ROWID TYPE - returns DBKEY values as a special type (SQLDA_
ROWID, 455) to make processing of the DBKEY values easier.
For instance, in prior releases the SQLDA name field (SQLNAME)
for DBKEY entries in the SQLDA was the only way to distinguish
these values from other CHAR or VARCHAR columns - it would be
either DBKEY or ROWID. If a query renamed the DBKEY column,
then the application had no information in the SQLDA to
indicate that the CHAR or VARCHAR value was binary data. In
all respects, the SQLDA_ROWID type appears as a fixed length
string of octets (possibly containing octets of zero which the
C language would treat as a NULL terminator for a string).
3.4 – DISABLE
The DISABLE clause deactivates one of the specified behaviors for
Dynamic SQL. See ENABLE clause for a list of options.
3.5 – ORACLE_LEVEL1|ORACLE_LEVEL2
Either of these options will set the SQLDA to supply enhanced
semantics. These options are currently reserved for the use
of the OCI Services for Rdb product that is part of Oracle Rdb
SQL/Services component. This setting also implicitly enables
NAMED MARKERS.
3.6 – PADDING n CHARACTERS
This option directs SQL to configure the SQLDA with larger
CHARACTER VARYING strings than would normally be seen. The value
of n is an unsigned numeric literal that specifies the number of
characters that are added to the estimated length. Any CHARACTER
(CHAR) types are converted to CHARACTER VARYING (VARCHAR). This
rule is applied to comparison operators <, <=, >, >=, =, <>, and
string functions (STARTING WITH, CONTAINING).
3.7 – NOPADDING
This option sets the number of padding characters to 0. This also
implies that derived CHARACTER (CHAR) types are not converted to
CHARACTER VARYING (VARCHAR) when PADDING CHARACTERS is used. This
is the default setting.
NOTE
Oracle recommends that applications always check for SQLDA_
CHAR and SQLDA_VARCHAR so that the correctly formatted data
is made available to SQL.
3.8 – SQL99|SQL92|MIA|SQL89|SQLV40
Any of these options will revert to the default semantic for the
SQLDA which includes disabling NAMED MARKERS.
4 – Example
Example 1: Using the NAMED MARKERS feature
This example shows that enabling the NAMED MARKERS feature will
allow SQL to promp tfor one value and the displayed Rdb strategy
shows that only one variable is used.
-> SET SQLDA 'ENABLE NAMED MARKERS';
-> SELECT LAST_NAME FROM EMPLOYEES WHERE FIRST_NAME = :F_NAME AND LAST_NAME <>
:F_NAME;
in: [0] typ=449 len=46
out: [0] typ=453 len=14
[SQLDA - reading 1 fields]
-> Alvin
Tables:
0 = EMPLOYEES
Conjunct: (0.FIRST_NAME = <var0>) AND (0.LAST_NAME <> <var0>)
Get Retrieval sequentially of relation 0:EMPLOYEES
0/FIRST_NAME/Varchar(42/46): Alvin
[SQLDA - displaying 1 fields]
0/LAST_NAME: Toliver
[SQLDA - displaying 1 fields]
0/LAST_NAME: Dement
Example 2: Using the PADDING feature
The following example shows that the derived type for the named
parameter MI is a SQLDA_CHAR (453) of length 1. The input data
('AA') is truncated on assignment and the incorrect results are
returned. By adding a small padding the type is changed to SQLDA_
VARCHAR (449) of length 3 and a correct comparison is performed.
-> ATTACH 'filename sql$database';
-> SET SQLDA 'enable named markers, nopadding';
-> SELECT LAST_NAME FROM EMPLOYEES WHERE MIDDLE_INITIAL = :MI;
in: [0] typ=453 len=1
out: [0] typ=449 len=18
[SQLDA - reading 1 fields]
-> AA
[SQLDA - displaying 1 fields]
0/LAST_NAME: Toliver
[SQLDA - displaying 1 fields]
0/LAST_NAME: Lengyel
[SQLDA - displaying 1 fields]
0/LAST_NAME: Robinson
[SQLDA - displaying 1 fields]
0/LAST_NAME: Ames
-> SET SQLDA 'padding 2 characters';
-> SELECT LAST_NAME FROM EMPLOYEES WHERE MIDDLE_INITIAL = :MI;
in: [0] typ=449 len=7
out: [0] typ=449 len=18
[SQLDA - reading 1 fields]
-> AA
-> EXIT;
Enter statement:
Note that the VARCHAR requires an extra 4 bytes for the length
information in the SQLDA2 used by the Dynamic SQL testing
program.