Migrating to Oracle7

Oracle7 conforms fully to the new ANSI/ISO SQL standard. As a result, Oracle7 and Oracle Version 6 behave differently in a few areas. By pointing out those areas, this appendix helps you migrate your application programs to Oracle7.

Fetching Nulls

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, Oracle7 generates the following error:

ORA-01405: fetched column value is NULL

Therefore, to migrate the program from Oracle Version 6 to Oracle7, 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, Oracle7 generates no error (instead of generating error ORA-01405).
If you fetch a truncated value into a host variable that lacks an indicator variable, Oracle7 generates error ORA-01406 (instead of generating no error).
Oracle7 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, Oracle7 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" .)

Fetching Truncated Values

When MODE=ORACLE, if your program fetches a truncated value into a host variable that lacks an indicator variable, Oracle7 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 Oracle7, 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, Oracle7 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.

Handling Character Strings

Some application programs expect variable-length character strings (of type VARCHAR2, for example). However, by default, Oracle7 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.

Using VARCHAR2 versus CHAR

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.

On Input

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.

On Output

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.