E Migrating to Oracle Database Version 7

Fetching Truncated Values

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.

Handling Character Strings

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.

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.