Oracle® Database Programmer's Guide to the Oracle Precompilers 10g Release 2 (10.2) Part Number B14354-01 |
|
|
View PDF |
Oracle database version 7 conforms fully to the new ANSI/ISO SQL standard. As a result, Oracle database version 7 and Oracle Version 6 behave differently in a few areas. By pointing out those areas, this appendix helps you migrate your application programs to Oracle database version 7.
When MODE=ORACLE
(the default), if your program fetches a null into a host variable that lacks an indicator variable, Oracle Version 6 generates no error; however, Oracle database version 7 generates the following error:
ORA-01405: fetched column value is NULL
Therefore, to migrate the program from Oracle Version 6 to Oracle database version 7, you may:
Re-precompile the program, specifying DBMS=V6 on the command line, which preserves the Oracle Version 6 behavior,
Revise the program by associating an indicator variable with every host variable into which nulls might be fetched, or
Specify UNSAFE_NULL=YES
with MODE=ORACLE
and DBMS=V7
to simply disable the ORA-01405
message.
When DBMS=V6
, regardless of the MODE
setting, the following changes go into effect:
If you fetch a null into a host variable that lacks an indicator variable, Oracle database version 7 generates no error (instead of generating error ORA-01405
).
If you fetch a truncated value into a host variable that lacks an indicator variable, Oracle database version 7 generates error ORA-01406
(instead of generating no error).
Oracle database version 7 treats the following items like variable-length character values (instead of fixed-length character values):
String literals
Local CHAR
variables in a PL/SQL block
The return value of the function USER
If you process a multirow query that calls a SQL group function such as AVG
or COUNT
, the function is called at OPEN
time (instead of at FETCH
time).
When DBMS=V6
, if you fetch nulls into a host array that lacks an indicator array, Oracle database version 7 continues processing. So, always use an indicator array; otherwise, you cannot find the nulls. (To Learn how to find nulls and truncated values, see "Using Indicator Variables".)
When MODE=ORACLE
, if your program fetches a truncated value into a host variable that lacks an indicator variable, Oracle database version 7 generates no error; however, Oracle Version 6 generates the following error:
ORA-01406: fetched column value was truncated
Therefore, to migrate the program from Oracle Version 6 to Oracle database version 7, you must either
re-precompile the program, specifying DBMS=V6
on the command line, which preserves the Oracle Version 6 behavior, or
revise the program by associating an indicator variable with every host variable into which truncated values might be fetched
When DBMS=V6
, if you fetch a truncated value into a host array that lacks an indicator array, Oracle database version 7 stops processing and sets SQLERRD(3)
in the SQLCA to the number of rows processed. No other information is available. So, always use an indicator array; otherwise, you cannot find the truncated values and their original lengths.
Some application programs expect variable-length character strings (of type VARCHAR2
, for example). However, by default, Oracle database version 7 uses fixed-length character strings of type CHAR
. This affects string-comparison semantics. For details, see the next section.
If your program expects variable-length character strings, specify DBMS=V6
on the precompiler command line. Among other things, this preserves Oracle Version 6 string-comparison semantics.
The VARCHAR2
and CHAR
datatypes differ in subtle but significant ways. CHAR
semantics have changed slightly to comply with the current ANSI/ISO SQL standard. The changes come into play when you compare, insert, update, select, or fetch character values.
When MODE=ANSI
, if both values being compared in a SQL statement belong to type CHAR
, blank-padding semantics are used. That is, before comparing character values of unequal length, Oracle blank-pads the shorter value to the length of the longer value. For example, if ENAME
is a CHAR
database column and emp_name is a CHAR
host variable (by default or datatype equivalencing), the following search condition is TRUE
when the column value "BELL" and the host value "BELL####" (with four trailing spaces) are compared:
... WHERE ENAME = :emp_name;
When MODE={ANSI14|ANSI13|ORACLE}
, if either or both values in a comparison belong to type VARCHAR2
, non-blank-padding semantics are used. That is, when comparing character values of unequal length, ORACLE makes no adjustments and uses the exact lengths. For example, if JOB
is a CHAR
column and job_title is a VARCHAR2
host variable, the following search condition is FALSE
when the column value "CLERK" and the host value "CLERK###" (with three trailing spaces) are compared:
... WHERE JOB = :job_title;
When you insert a character value into a CHAR
database column, if the value is shorter than the defined width of the column, Oracle blank-pads the value to the defined width. As a result, information about trailing blanks is lost. If the character value is longer than the defined width of the CHAR
column, Oracle generates an error. Oracle neither truncates the value nor tries to trim trailing blanks.
When you insert a character value into a VARCHAR2
database column, if the value is shorter than the defined width of the column, Oracle does not blank-pad the value. Nor does Oracle strip trailing blanks. Character values are stored intact, so no information is lost. If the character value is longer than the defined width of the VARCHAR2
column, Oracle generates an error. Oracle neither truncates the value nor tries to trim trailing blanks.
The same rules apply when updating.
When a column value is selected into a CHAR
host variable, if the value is shorter than the declared length of the variable, Oracle blank-pads the value to the declared length. For example, if emp_name is a CHAR(15)
host variable (by default or variable equivalencing), and 10-byte column value is selected into it, Oracle appends 5 blank characters to it. If the column value is longer than the declared length of the CHAR
host variable, Oracle truncates the value, stores it, and generates a warning.
When a column value is selected into a VARCHAR2
host variable, if the value is shorter than the declared length of the variable, Oracle does not blank-pad the value; nor does Oracle strip trailing blanks. If the column value is longer than the declared length of the VARCHAR2
host variable, Oracle truncates the value, stores it, and generates a warning.
The same rules apply when fetching.