9
Upgrading Your Applications

This chapter describes upgrading your current applications to take advantage of new release 8.1 features. You do not need to modify existing Oracle7 and release 8.0 applications that do not use new release 8.1 features. Existing applications should achieve the same, or enhanced, functionality on a release 8.1 database.

The following topics are covered in this chapter:

• Overview of Upgrading Applications to Oracle8i

• Upgrading OCI and Precompiler Applications

• Upgrading SQL*Plus Scripts

• Upgrading Oracle7 Forms or Developer/2000 Applications

• Copying LONGs to LOBs

Overview of Upgrading Applications to Oracle8i

The following version 8 features aid in the process of upgrading applications to release 8.1:

• Both SQL*Net V2 and Net8 work with various Oracle versions and releases. Thus, version 7 and version 8 databases can communicate by using SQL*Net version 2 and Net8.

  Note: SQL*Net version 1, however, used a different network addressing scheme and cannot be used with version 8.

• For application creation and management, the programming interface (such as the Oracle precompilers and Oracle Call Interface) remains unchanged between different versions of Oracle. For example, you can use SQL*Net or Net8, or you can relink applications designed for an earlier release database to run them with a version 8 release, or with an Oracle7 release.

• Backward compatibility generally is supported to accommodate small incompatibilities between different Oracle releases.

• Many new features and enhancements are available automatically after migration. Some of these new features may provide improved performance.

Upgrading OCI and Precompiler Applications

If you are migrating your Oracle database from Oracle7 to Oracle8i, before you migrate the database, upgrade any precompiler and OCI applications that you plan to use with your Oracle8i databases. Then, you can test these applications on a sample Oracle8i database before migrating your production database.

The effort required to upgrade these applications depends on the degree to which you want to take advantage of the programmatic interfaces and Oracle8i. 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 Oracle8i database functionality

The following sections provide more information about upgrading OCI and precompiler applications.

Upgrading OCI Applications

Application developers have the following options if they want to incorporate release 8.1 functionality into existing OCI applications:

• If your application uses Oracle7 OCI calls, completely rewrite the applications to use only new release 8.1 OCI calls.

• If your application uses Oracle7 OCI calls, incorporate release 8.1 OCI calls into the existing application, while still using Oracle7 calls for some operations.

• If your application uses only version 8 calls, incorporate the new release 8.1 OCI calls into existing applications.

  See Also: See Oracle Call Interface Programmer's Guide for more information about writing and upgrading OCI applications.

Using Batch Error Mode for Statement Execution

Starting with release 8.1, OCI applications can use the batch error mode when executing array DMLs using OCIStmtExecute. To do this, both the OCI and server libraries must be release 8.1 or higher.

You can modify existing applications to use batch error mode by setting the mode parameter to OCI_BATCH_ERRORS and adding new code required for this functionality. Then, recompile and relink the application with the release 8.1 client libraries.

Support for Client Notification

Starting with release 8.1, client notification is supported in OCI applications using the publish/subscribe interface. Client notification enables applications to take advantage of Database Event Publication and Advanced Queuing features. To use the client notification feature, client applications must link with release 8.1 or higher client libraries.

Support for the LISTEN Call with the Advanced Queuing Option

Starting with release 8.1, the LISTEN call is supported in OCI applications. The LISTEN call is available with the Advanced Queuing Option and can be used to monitor a set of queues for a message. To use the LISTEN call, client applications must link with release 8.1 or higher client libraries.

Upgrading Precompiler Applications

Complete the following steps to use your existing precompiler applications with a release 8.1 database:

1. If you want to take advantage of the new release 8.1 features in your existing application, complete one of the following actions based on whether the existing application is an Oracle7 application or a version 8 application:
   • If you have a Oracle7 application, the existing Oracle7 application may need to be recoded, or new applications written, to reflect the differences between Oracle7 and Oracle8i.

   • If you have a version 8 application and you want to take advantage of the new release 8.1 features, incorporate code for the new release 8.1 functionality into the existing version 8 application.

   If you decide not to take advantage of release 8.1 features with an existing application, skip this step and move on to Step 2.

2. Relink the application with the release 8.1 runtime library, SQLLIB, which is included with the precompiler. This step is the only required step to use your applications with release 8.1.

   Note: To run an existing Oracle7 pre-compiler application against an Oracle8i database, you do not need to re-precompile, nor recompile, the application.

Simplified Upgrading of Existing Oracle7 Precompiler Applications

Applications written to work with Oracle7 precompilers, such as Pro*C 2.2, have a very smooth upgrade path to Oracle8i precompilers, such as Pro*C 8.1. Oracle7 precompiler clients work with an Oracle8i server, and Oracle8i precompiler clients can work with an Oracle7 server.

Specifically, the following list outlines precompiler/server compatibility. The list uses Pro*C as an example, but the information also applies to Pro*COBOL:

• Applications using Oracle7 Pro*C 2.2 work unchanged against an Oracle8i server (all SQLLIB function calls work against Oracle8i servers).

• Applications using Pro*C 8.1 can work against an Oracle7 server if they do not use any new version 8 features, including the object capabilities, of the precompiler or server.

• Oracle7 and Oracle8i SQLLIB function calls can be mixed in the same application and the same transaction.

The following three alternative upgrade paths are available for an existing Oracle7 application, without requiring recompilation or re-precompilation. Again, the list uses Pro*C as an example, but the information also applies to Pro*COBOL:

• Retain the Oracle7 client application without any modification. It will continue to work against a new Oracle8i server.

• Upgrade to Oracle8i client but do not modify the application. To upgrade an Oracle7 client to an Oracle8i client, you only need to relink the new version of SQLLIB and do not need to recompile the application. Relinked Oracle7 applications work unchanged against an Oracle8i server.

• Upgrade to Oracle8i and modify the application to leverage new Oracle8i precompiler capabilities for performance and scalability benefits, or to use any object capabilities of Oracle8i. Existing Oracle7 applications must be recoded using the new Oracle8i SQLLIB calls, and must be relinked with the new SQLLIB, to run against an Oracle8i server. This upgrade path is required for any application to use any object capability of the Oracle8i server. Further, if application developers need to use any of the object capabilities of the Oracle8i server, they need to upgrade their client to use Pro*C 8.0 or higher.

  See Also: Pro*C/C++ Precompiler Programmer's Guide and Pro*COBOL Precompiler Programmer's Guide for more information about precompiler applications.

Upgrading SQL*Plus Scripts

To use SQL*Plus release 8.0 or higher, a release 8.0 or higher database, and PL/SQL release 8.0 and higher functionality, complete the following steps:

• Make the following changes to SQL*Plus 3.x scripts to convert them into SQL*Plus 8.0 or higher scripts:
  • If a script contains the line SET COMPATIBILITY VERSION 7, change it to SET COMPATIBILITY VERSION 8, or remove the line so that the default setting is used (VERSION 8).

  • Check any login.sql file(s) and change any SET COMPATIBILITY VERSION 7 line found to SET COMPATIBILITY VERSION 8.

  • Refer to the SQL*Plus User's Guide and Reference to learn about new functionality in SQL*Plus.

• To use new Oracle8i functionality, change existing SQL scripts to conform to Oracle8i syntax. Existing SQL scripts run unchanged on Oracle8i, and require no modification, if they do not use new Oracle8i functionality

  See Also: Oracle8i SQL Reference for more information about upgrading SQL scripts.

  Note: No changes to PL/SQL procedures are required.

  .

Upgrading Oracle7 Forms or Developer/2000 Applications

Forms applications run the same on Oracle7 and Oracle8i. However, review the new features described in Getting to Know Oracle8i to determine whether any of the new Oracle8i features would be beneficial to your applications or might otherwise affect them. Information about the ways in which the Oracle8i 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.

Copying LONGs to LOBs

LOB datatypes (BFILE, BLOB, CLOB, and NCLOB) can provide many advantages over LONG datatypes. See Oracle8i Concepts for information about the differences between LOB and LONG datatypes.

In release 8.1, a new SQL operator, TO_LOB, copies data from a LONG column in a table to a LOB column. The datatype of the LONG and LOB must be the same for a successful copy. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data.

In the examples in the following procedure, the LONG column named LONG_COL in table LONG_TAB is copied to a LOB column named LOB_COL in table LOB_TAB. These tables include an ID column that contains identification numbers for each row in the table.

Complete the following steps to copy data from a LONG column to a LOB column:

1. Create a new table with the same definition as the table that contains the LONG column, but use a LOB datatype in place of the LONG datatype.

   For example, suppose you have a table with the following definition:

   CREATE TABLE long_tab (
       id NUMBER,
       long_col LONG);
   
   

   Create a new table using the following SQL statement:

   CREATE TABLE lob_tab (
       id NUMBER,
       clob_col CLOB);
   
   

   Note: When you create the new table, make sure you preserve the table's schema, including integrity constraints, triggers, grants, and indexes. The TO_LOB operator only copies data; it does not preserve the table's schema.

2. Issue an INSERT command using the TO_LOB operator to insert the data from the table with the LONG datatype into the table with the LOB datatype.

   For example, issue the following SQL statement:

   INSERT INTO lob_tab
       SELECT id,
       TO_LOB(long_col)
       FROM long_tab;
   
   

3. When you are certain that the copy was successful, drop the table with the LONG column.

   For example, issue the following SQL command to drop the LONG_TAB table:

   DROP TABLE long_tab;
   
   

4. Create a synonym for the new table using the name of the table with LONG data. The synonym ensures that your database and applications continue to function properly.

   For example, issue the following SQL statement:

   CREATE SYNONYM long_tab FOR lob_tab;
   
   

Once the copy is complete, any applications that use the table must be modified to use the LOB data.

See Also: Oracle8i Application Developer's Guide - Large Objects (LOBs) for information about modifying applications to use LOB data.

---