
Migrating Version 6 Applications
This chapter describes how you can use your Oracle tools and applications with Oracle7 releases. Usually, little or no change is required of your applications to achieve the same or enhanced functionality running on a Release 7.x database. The topics included in this chapter are
If you are migrating applications to Trusted Oracle, there are additional features of which you need to be aware. For more information about migrating your applications to Trusted Oracle, see the Trusted Oracle7 Server Administrator's Guide.
Oracle7 Changes and the Programmatic Interfaces
This section contains the following topics:
Retaining General Version 6 Behavior
The behavior of Oracle7 has changed in some minor ways from
Version 6 to accommodate ANSI/ISO standards. Some of these changes can be averted by specifying option and procedure call settings that preserve Version 6 behavior.
The following changes are always in effect and cannot be avoided. Some minor recoding of applications might be required to assure continued correct behavior.
- In SQL statements, the special keyword USER is considered a literal and, if compared to other literals, uses padded comparison semantics.
- All string literals have both the fixed-length and standard-compare attributes, and ANSI/ISO string comparison rules apply.
Retaining Version 6 Precompiler and OCI Behavior
The following changes can be averted by retaining Version 6
behavior using specific option settings to the precompilers or, for
OCI applications, by carefully selecting procedure calls and
associated arguments. Most of these changes were introduced for standards compliance.
Note: If you are migrating a Version 6 application to any of the Version 7 releases and plan to use the CREATE TABLE...AS SELECT command, CHAR datatypes will not be correctly changed to VARCHAR2. To avoid this problem, specify a V6 compatibility mode, as follows:
SQL> SET COMPATIBILITY V6
SQL> CREATE TABLE <tablename> AS SELECT ....
- In Version 6, if a NULL value was fetched into a host variable, and no indicator variable was present, you received the error message "ORA-01405: fetched column value is NULL" only if you set MODE=ANSI. In Oracle7, you always receive this message (unless you set DBMS=V6).
- In Version 6, if a value fetched from the database had to be truncated to fit the host variable, and no indicator variable was present, you did not receive an Oracle error message if you set MODE=ANSI. In Oracle7, you never receive a message (unless you set DBMS=V6).
- PCTINCREASE can no longer be specified in the CREATE ROLLBACK SEGMENT statement.
- Illegal MAXEXTENTS storage parameters are no longer allowed.
In the Release 1.5 Precompilers, specifying DBMS=V6 causes SQL statement behavior to remain the same as when working against Oracle, Version 6, and applications need not be recoded to accommodate the changes listed above. Specifying DBMS=V7 to obtain Oracle7 behavior might require slight recoding. For more information, see the Programmer's Guide to the Oracle Precompilers.
In the Oracle7 OCIs, use of the existing OSQL3 parse call, or the new OPARSE call with the LNGFLG parameter set to Version 6, similarly guarantees Version 6-like program behavior. Substituting OPARSE for OSQL3 and specifying LNGFLG=V7 might require slight recoding to handle the changes listed above. For more information, see the Programmer's Guide to the Oracle Call Interface.
Migrating Your Oracle Programmatic Interface Applications
This section contains the following topics:
The Oracle Programmatic Interface family consists of the Oracle Precompilers, the Oracle Call Interface, and the new SQL*Module. The tool that you select to create an application varies depending upon your needs. This section briefly discusses the changes to these tools resulting from the latest release of the database or the tool itself, and the implications of these changes for existing programs.
New Features of the Oracle Call Interface
Upgrading to any Oracle7 release automatically upgrades your Oracle Call Interface (OCI) libraries.
Attention: Existing applications must be relinked with these new libraries. If desired, you can also recode existing applications or code new applications to take advantage of the following new features.
Deferred Database Calls
A new link option reduces the amount of messaging traffic required between OCI clients and Oracle by transparently bundling BIND and DEFINE variable information on the client. Bundled (or deferred) information is only transmitted when required by DESCRIBE, EXECUTE, and FETCH calls.
New OCI Calls
ODESCR replaces ODSC and OBNDRA supplements OBNDRV/OBNDRN calls. OFLNG allows piecewise access to the
data stored in Oracle7 LONG and LONG RAW database column
types. OPARSE and OEXFET can be used with deferred database linking to realize even greater performance improvements using
network optimization.
Non-blocking Oracle Call Interface
Release 7.2 enhances responsiveness of client/server applications by overcoming the limitations of synchronous OCI calls. Applications are now allowed to continue while an OCI call is being processed. Four new functions support non-blocking OCI
- ONBSET: This function places a database connection in
the non-blocking mode for all subsequent OCI calls on the current connection.
- ONBTST: This function tests to see if a database connection is
in the non-blocking mode.
- ONBCLR: This function places a database connection in the non-blocking mode.
- OLOG: This function establishes a non-blocking database connection between an OCI application and an Oracle database.
For more information on non-blocking OCI, see the Programmer's Guide to the Oracle Call Interface.
SQL*Module
A new, separately licensed product, SQL*Module, provides the best of both the Precompiler and OCI development environments, while delivering 100% compliance with relevant ANSI and ISO standards.
Like the Precompilers, SQL*Module provides an easy capability to execute Static SQL Data Manipulation Language statements. However, like the OCIs, the mechanism by which the host program executes the statements is a simple host language procedure call. Because no embedded SQL code is present in the application source code, you can continue to use your standard programming tools to edit and debug your code.
You might choose to take advantage of this new tool to create any
new applications or even to recode your existing applications. SQL*Module incorporates the MODE and FIPS options of the
Release 1.5 Precompilers.
SQL*Module Default WITH INTERFACE
SQL*Module, Release 1.1 does not require a WITH INTERFACE clause to call Oracle stored procedures.
You might want to use the WITH INTERFACE clause as follows:
- Map PL/SQL types to embedded SQL types that are subsequently mapped to host language types.
- Specify which parameters are associated with NULL INDICATORS.
- Specify how error conditions are to be reported to the client; for example, with SQLCODE, SQLSTATE, or both.
If you use the default WITH INTERFACE clause, a default mapping will be automatically applied.
For more information on SQL*Module WITH INTERFACE, see the SQL*Module User's Guide and Reference.
Migrating Precompiler and OCI Applications
Before you migrate your Oracle database from Version 6 to Oracle7, migrate any OCI and Precompiler applications that you plan to use with your Oracle7 databases. You can then test these applications on a sample Oracle7 database before migrating your production database.
The amount of effort involved in this migration process is dependent upon the degree to which you want to take advantage of the Programmatic Interfaces and Oracle7. In order of increasing difficulty, you can choose to
- maintain your existing performance and functionality
- boost the performance of your applications
- take advantage of the new Oracle7 database functionality
See the "Migrating Your Precompiler Applications" and "Migrating Your OCI Applications" sections which follow in this chapter for
the specific steps required to migrate your Precompiler and
OCI applications.
Migrating Your Precompiler Applications
You must complete a subset of the following steps to use your existing precompiler applications with an Oracle7 database. You may optionally begin at any step; however, once you begin you must complete any steps that follow. For instance, if you begin with Step 3, you must complete Steps 4 and 5. Step 5 is required of all applications.
2. If you want to take advantage of the new features offered by Oracle7, you must recode your applications to reflect the differences between Version 6 and Oracle7 that were outlined previously in this chapter.
3. If you want to take advantage of new features offered by the Release 1.5 Precompiler, you must re-precompile your application using the appropriate option settings as explained below. For a complete explanation of each of the option settings, including those that are now obsolete, see the Programmer's Guide to the Oracle Precompilers.
- Use the following option settings to maintain
existing functionality:
DBMS=V6
other existing options=no changes
- Use the following settings to improve performance and take advantage of the new features available with Oracle7:
DBMS=V7
MODE=Oracle
other existing options=no changes
- To be fully ANSI compliant, use the following settings:
DBMS=V7
MODE=ANSI
FIPS=YES (optionally, to receive FIPS warning messages)
other existing options=no changes
4. Recompile your application with your compiler.
5. (Required) Relink your application with Release 1.5 of the Oracle Runtime Library (SQLLIB), which is included with the precompiler. You must complete at least this step to use your applications
with Oracle7.
Migrating Your OCI Applications
You should complete the following steps to use your existing OCI applications with an Oracle7 database. Steps 1 and 2 are required. Step 3 ensures that constraints present in Version 6 applications will be properly enabled when run on a Version 7 database. Step 4 is optional and allows you to take advantage of the new functionality offered with Oracle7 releases.
- recoded using the "new" OCI equivalents
- relinked with a user-written interface layer to map "old" calls to "new" calls. This may be necessary, for example, where the source code of the original application is no longer available.
2. Relink your applications with Oracle7 of the OCI libraries,
using either
- deferred mode linking, to improve performance of applications
- non-deferred mode, to maintain existing performance levels
The OCI libraries are shipped with all Oracle7 releases.
One implication of deferred linking is that bind and define errors may not be reported to the application immediately after bind and define operations, but may, instead, be reported later at the time of a describe, execute, or fetch call.
3. ENABLE all constraints after a newly migrated Version 6 application is run on a Version 7 database. When a Version 6 OCI application is run on a Version 7 database, the constraints are created, but disabled. For example, if a Version 6 OCI application containing a DDL statement such as
ALTER TABLE gpd ADD (PRIMARY KEY (fd1) CONSTRAINT gp)
is run on a Version 7 database, the constraint, gp, is created, but disabled. If a Version 7 OCI application, containing a similar statement (note the changed syntax) is run, such as
ALTER TABLE gpd ADD (CONSTRAINT gp PRIMARY KEY (fd1))
4. (Optional) Recode your applications to use new calls. The new OBNDRA, ODESCR, OFLNG, ONBSET, ONBTST, ONBCLR, and OLOG calls allow you to take advantage of new features offered by Oracle7, and do not require any further recoding. The new OPARSE and OEXFET calls allow you to improve the performance of your applications. If you use OPARSE with the LNGFLG parameter set to V7, you must also change your code to reflect the differences between Version 6 and Oracle7, outlined previously.
OCI applications written with the non-blocking feature will work in backward mode with Release 7.0 and Release 7.1.
Migrating Your Oracle Forms Applications
This section contains the following topics:
- Taking Advantage of Oracle7 Functionality
General Comments
All Forms releases run with either Oracle, Version 6 or any Oracle7 release. You are not required to regenerate your applications to use them with a Release 7.x database. If you purchased a new Forms release (the newest Forms release is 4.5), install it after the installation of your new 7.x database is completed. Your Forms (Runform) applications run with no modification. You should, however, keep in mind the following issues:
- Forms applications running on an Oracle7 release are functionally identical to those applications running on Version 6. Guidelines for modifying Forms applications to take advantage of Oracle7 functionality are outlined later in this section.
- Forms 4.5 continues to use PL/SQL Version 1, resulting in the following restrictions on your datatypes:
- NUMBER, DATE, BOOLEAN, and VARCHAR2 fields are supported, but VARCHAR2 fields are limited to 2 Kb.
- No other PL/SQL Version 2 datatypes can be used in Forms triggers.
Taking Advantage of Oracle7 Functionality
Forms releases greater than 4.0 have been enhanced to allow support of stored procedures, functions, and packages (local and remote). Because Forms creates the access routine for each stored subprogram that your trigger or Forms PL/SQL code references, it is not possible to write a form containing calls to stored procedures or functions that will generate against both Oracle, Version 6 and an Oracle7 database.
Once a form includes a reference to at least one stored subprogram, it must be generated against an Oracle7 database. Otherwise, compilation errors result when the names of stored subprograms cannot be resolved by the PL/SQL compiler.
You should review the new features described in Appendices A, B, C, and D of this manual to determine if stored procedures or any of the other new Oracle7 features would be beneficial to your Forms applications. A complete description of how these Oracle7 features interact with Forms applications is provided in the Oracle Forms 4.5 Reference Manual, Vol.1 and Vol. 2, the Oracle Forms 4.5 Developer's Guide and Forms 4.5 Advanced Techniques.
Migrating Your CASE*Dictionary Applications
The DDL generator in CASE Dictionary V5.0.22 generates SQL DDL using Oracle, Version 6 syntax, which is slightly different from the syntax used by the Oracle7 Server. To account for this, SQL*Plus and Server Manager provide a setting to allow V6 syntax to be interpreted properly by the Oracle7 Server, including CHAR to VARCHAR2 translation. CASE*Dictionary 5.0 sets the mode automatically when these scripts are run from within the CASE*Dictionary environment. If you wish to execute your CASE-generated DDL scripts outside of CASE*Dictionary, you must first set your SQL*Plus or Server Manager session to V6 compatibility mode by issuing the following command:
SQL> SET COMPATIBILITY V6
To create true Oracle7 CHAR columns (fixed length, blank padded), the DDL scripts must be executed outside of the CASE environment without setting V6 compatibility mode. See Oracle7 Server SQL Reference for more information on Oracle, Version 6 compatibility mode.
Full support for the generation of DDL syntax and objects introduced in the Oracle7 Server will be available in CASE*Dictionary 5.1.
Migrating Your SQL*Report (RPT/RPF) Reports
Support for reports created using SQL*Report is maintained in Oracle7. Your reports should run against either Version 6 or Oracle7 of the database with no modifications. SQL*Report has not been upgraded to take advantage of any new Oracle7 functionality.
Migrating Your Oracle Reports and SQL*ReportWriter Applications
Support for Oracle Reports and SQL*ReportWriter is maintained in Oracle7 releases. Your reports should run against either Version 6 or Oracle7 databases with no modifications. Oracle7 functionality is supported in Oracle Reports, Version 2.
If you have user-owned Oracle Reports, Version 2 tables and wish to take advantage of the row-level locking available in standard Oracle7, you must create system-owned tables. To install the system tables, use the SQL*Plus script SRW_ICEN.SQL if you are using SQL*ReportWriter Version 1.1, or SRW_ICEN2.SQL if you are using Oracle Reports, Version 2. Then load your existing reports into the new tables.
Migrating Your SQL*Plus Scripts
This section contains the following topics:
- Set Compatibility Mode to V6
- Taking Advantage of Oracle7 Functionality
Set Compatibility Mode to V6
After you have migrated your database from Oracle, Version 6 to an Oracle7 release, you can run your SQL*Plus 3.0 scripts against the Oracle7 release and achieve the same output as if you were running against Version 6 by adding the following line as the first line of
your script:
SET COMPATIBILITY V6
Alternatively, you could add this line to your LOGIN.SQL file.
Taking Advantage of Oracle7 Functionality
If you want SQL*Plus Release 3.1, Oracle7, and PL/SQL Version 2 functionality, you must complete these additional steps:
- You must make the following changes to your SQL*Plus 3.0 scripts to make them SQL*Plus 3.1 scripts:
- If your scripts contain the line SET COMPATIBILITY V6, change it to SET COMPATIBILITY V7. Also check your LOGIN.SQL file and make this change as needed.
- If your script relied on the DESCRIBE command's output, be aware that it now displays two new datatypes: ROWLABEL and VARCHAR2.
- To learn about functionality new to SQL*Plus Release 3.1, refer to the SQL*Plus User's Guide and Reference.
- No changes to PL/SQL procedures are required.
SQL*Net
All Oracle7 releases use Version 2 of SQL*Net, which may have an impact on migrated applications. Several points are important.
- SQL*Net, Version 1 has been deprecated.
- Both the client and server must use the same version of SQL*Net.
- The Multi-Threaded Server requires SQL*Net, Version 2 at the server side. Therefore, if you want to connect using the Multi-Threaded Server, you must also use SQL*Net, Version 2 at the client side
Perform the following changes to upgrade from SQL*Net Version 1 to Version 2:
- Install SQL*Net, Version 2.
- Re-create each Version 1 connect string as a Version 2 connect descriptor. SQL*Net Version 2 relies on the new syntax outlined in the SQL*Net V2.0 Administrator's Guide.
- Relink any precompiler programs that you want to use with SQL*Net Version 2.
For complete instructions on upgrading SQL*Net from Version 1 to Version 2, refer to the SQL*Net V2.0 Administrator's Guide and the SQL*Net Version 2 Migration Guide.