Document ID: 103817.404
Author: ASATYAWA
Subject: PORTING PRO-LANGUAGE PROGRAMS FROM V6 TO ORACLE7
Last Revision Date: 16 February 1995
_______________________________________________________________________________
Porting Pro-Language Programs from V6 to Oracle7
The purpose of this article is to apprise the user of some of the changes
that have taken place in the behavior of the Oracle Kernel from the point
of view of language tools like the precompilers.
There are two issues with Oracle7 which can cause programs running
with V6 to crash or give erroneous results when run against Oracle7.
Issue I: Fetching Nulls
-----------------------
In V6 (programs precompiled with versions 1.3/1.4), one could fetch
a null value into a host variable without specifying an indicator variable
if the precompiler option MODE was ORACLE. But with Oracle7 (programs
precompiled with version 1.5 or higher), if there are no indicator variables
then the program errors out with the following message
"ORA 1405 fetched column value is NULL"
This problem can be avoided if, at precompile time, the precompile
option DBMS is set to V6 (the default is NATIVE).
Note: If an application is precompiled amd compiled with version 1.4 of the
precompilers and linked to Oracle7, then the program will have a V6
behavior.
This parameter was introduced, because some Oracle7 SQL behavior was changed
from V6 to ensure full compatibility with SQL89 and SQL2 standards. For example,
the SQL89 standard uses CHAR(n) to mean a fixed-length string column. A summary
of all changes is given later in this bullletin.
There are two classes of changes to the RDBMS from V6 to Oracle7. The first
class of changes are those features that are simply new to the RDBMS. The
second class are changes where RDBMS behaviour has been modified between V6
and Oracle7. When a user migrates from V6 to Oracle7, the user has a
choice between two possibilities. The first is to make a complete transition
and use both the new features of Oracle7 plus to use the new Oracle7 behaviours
where there is a difference between V6 and Oracle7. The second choice is to
use the new Oracle7 features, but to use v6 behaviour for where there is a
change of V6 behaviour in Oracle7.
The precompiler DBMS flag ONLY controls between these two choices. That is
to say that the NEW Oracle7 features ARE available irrespective of the setting
of the precompiler DBMS option. The DBMS option only affects those things that
behave differently between V6 and Oracle7.
So if the user is interested in maintaining the compatibility of his
present code while using the NEW Oracle7 features, then "DBMS=v6" *is* the
appropriate setting. On the other hand, if the user wants to use the
modified behaviours also, then "DBMS=v7" is the proper setting.
The RDBMS features that are toggled by DBMS=V6/V7 are as follows :
Feature V6 V7
----------------------------------------------------------------------
CHAR(n) is SQL89 fixed-length string NO YES
CHAR(n) is VARCHAR2 (v6 ORACLE CHAR) YES NO
v6 constraint name syntax YES NO
v7 constraint name syntax NO YES
constraints are enabled by default NO YES
(note: NOT NULL constraints are always enabled by default)
error if no indicator and fetch null NO YES
error if no indicator and fetched YES NO
value is truncated
describe of fixed-length string returns NO YES
datatype 96
describe of fixed-length string returns YES NO
datatype 1
allow pctincrease with rollback segments YES NO
allow illegal MAXEXTENTS storage parms YES NO
Issue II: Selecting CHAR Columns
--------------------------------
The definition of CHAR cloumn in the database has changed from being
a variable length column to a fixed length column. So, a CHAR column
declared as CHAR(6) in V6 can store KING in it. In Oracle7, KING will
be stored as KINGbb, where bb stands for two blank spaces.
See Bulletin 105584.673 for more information on how this will affect
character comparisons.
Suggested Reading:
------------------
Precompiler Option "MODE" - ANSI/ORACLE - Precompilers Guide, Chapter 11.