Oracle Applications Release 10.7 Interoperability Patch for Oracle8i Server Release 8.1.6

March 2000

---

• Server Partitioning

Explains how to run Oracle Applications Release 10.7 with the Oracle8i Server Release 8.1.6.
 

• Upgrading to Oracle8i Server Release 8.1.6

Contains instructions for upgrading an existing Oracle Applications Release 10.7 installation so that it will work with an Oracle8i Server Release 8.1.6 database.
 

• Modifying PL/SQL Packages for Oracle 8i Server Release 8.1.6

Contains instructions for modifying Oracle Applications PL/SQL packages to make them compatible with Oracle8 Server Release 8.0.4 (or higher).
 

Attention: These notes apply for all platforms, but you may need to modify some instructions slightly depending upon your platform. For example, these notes use the UNIX forward slash (/) character as the pathname separator, but you would type the backslash character (\) on Windows NT to separate the elements in a pathname.

Section 1: Server Partitioning

Oracle Applications Release 10.7 supports Oracle8i Server Release 8.1.6 through server partitioning. In this model, the Oracle Applications file system is linked with an Oracle7 Server Release 7.3.4 (or higher) ORACLE_HOME. The Oracle Applications database uses Oracle8i Server Release 8.1.6 located in a separate ORACLE_HOME, which is not necessarily on the same machine or node as Oracle Applications. Objects and data are stored in this database.

SQL*Net Version 2 handles communication between the Oracle Applications file system on the Oracle7 Server and the Oracle8i Server Release 8.1.6 Oracle Applications database.

The following diagram illustrates the layers in a server partitioning model on two separate machines. However, the server partitioning model works just as well if both the Oracle7 Server Release 7.3.4 (or higher) ORACLE_HOME and the Oracle8i Server Release 8.1.6 ORACLE_HOME are on the same machine or node. In this case, however, the two ORACLE_HOME directories must be separate physical directories.
 

SQL*NET V2 Client Oracle Applications Oracle Reports Oracle 7 Server Release 7.3.4   (or higher) ORACLE_HOME

--------->   --------->   SQL*NET V2-->   --Connect->   --------->

Net8 listener Oracle8i Server 8.1.6 ORACLE_HOME Oracle Applications Database

Server partitioning provides all the functional and performance advantages of running Oracle Applications with Oracle8i Server Release 8.1.6 without having to link Oracle Applications executables. Using SQL*Net Version 2 to connect the Oracle7 Server 7.3.4 (or higher) ORACLE_HOME and the Oracle8i Server Release 8.1.6 ORACLE_HOME results in little or no degradation of performance.

If you are currently using Oracle Applications Release 10.7 with an Oracle7 Server Release 7.3.4 (or higher) database on separate nodes or machines, and are, therefore, already using SQL*Net Version 2, then upgrading to an Oracle8i Server Release 8.1.6 database (as described in these notes) should improve performance.
 

Attention: These notes assume that the Oracle8i ORACLE_HOME resides on the same machine or node as the Oracle7 ORACLE_HOME and Oracle Applications. If your Oracle8i ORACLE_HOME resides on a separate machine or node, you will need to log in to that machine before you perform steps that affect your Oracle8i ORACLE_HOME.

Section 2: Upgrading to Oracle8i Server Release 8.1.6

Preparing for your Oracle Applications Database Upgrade

Step 1: Verify your Oracle Applications version.

Step 2: Unload Oracle Applications patches.

Oracle Applications patches 499648, 480129, 487890 and 1054707 are located in the /R1070/IOP816/<your platform> subdirectory on the CD. Patch 499648 includes the adprepdb.sql script, which prepares your existing database for Oracle8i Server Release 8.1.6.

Create an empty patch directory on your server and copy the patches for your platform to this directory from the /R1070/IOP816/<your platform> subdirectory on the CD.  Your patch directory will be called <patchdir> in these notes.

Setting Up the Oracle8i Environment

Step 3: Install Oracle8i Server.

Step 4: Modify init<SID>.ora for Oracle8 Server Release 8.1.6.

    event = "10932 trace name context level 2"

    SEQUENCE_CACHE_ENTRIES

    SEQUENCE_CACHE_HASH_BUCKETS

    USE_ISM

    DISTRIBUTED_LOCK_TIMEOUT

Step 5: Migrate your database to Oracle8i Server Release 8.1.6.

If your Oracle Applications database is currently using Oracle7 Server Release 7.3, you must migrate the database from Oracle7 to Oracle8i. The migration utility requires enough space in the SYSTEM tablespace to hold both the Oracle8i data dictionary and the Oracle7 data dictionary simultaneously. The Oracle8i data dictionary typically is 50 percent larger than the Oracle7 data dictionary, so you must ensure that there is sufficient space to allow 150 percent growth in the SYSTEM tablespace. If there is insufficient space before the migration starts, the utility will fail. The migration utility also requires extra space in the TEMP tablespace.
 

Additional Information: Oracle8i Server Migration Guide

Step 6: Copy database preparation scripts.

    APPL_TOP/install/ad*.pls .



    <patchdir>/499648/install/adprepdb.sql .



    <patchdir>/499648/install/ad*.pls .

Step 7: Run adprepdb.sql.

This script prompts for the passwords for the Oracle Server SYS and SYSTEM schemas. It displays messages about the tasks it performs and returns to the operating system prompt when it finishes. On most systems, it takes between 30 minutes and one hour to run.

Your Oracle8i environment is now ready for server partitioning.

Completing the Oracle Applications File System Upgrade

Make sure that the TWO_TASK setting points to the connect string of the Oracle8i database and that the SQL*Net connection between the Oracle7 Server and Oracle8i Server releases is set up properly. Also ensure that the ORACLE_SID environment setting is not set.

The Oracle7 Server linked to the Oracle Applications Server should be currently using Server Release 7.3.4 and must have the Oracle Developer 1.6.1 (Reports) and the Forms 2.4 installed in it.
 

Additional Information: Oracle Applications Interoperability Patch for Developer Release 1.6.1 Installation Notes (available at Oracle MetaLink).

Step 8: Log in to the Oracle Applications login account (typically applmgr).

Step 9: Apply Oracle Applications Release 10.7 SmartClient/NCA Server patches.

You must now apply either the Oracle Applications Release 10.7 SmartClient Production 16.1 or the Release 10.7 NCA Applications Database Server Updates. Applying these Updates is mandatory in order for Oracle Applications Release 10.7 to work with Oracle8i Server Release 8.1.6, even if you plan to use Release 10.7 in character mode only. It does not matter which of the Server Updates you apply, but do not apply both these patches as they are identical.

These product updates are available separately from your Oracle Support Representative.
 

Additional Information: The chapter titled "Applying the Updates" or "Applying the Applications Database Server Updates" in either the Release 10 SmartClient Production 16.1 installation manual  or the Release 10.7 NCA installation manual.

The appropriate chapter of one of these installation manuals contains a section called "Step 4: Apply the Release 10.7 driver files." This step instructs you to apply a series of AutoPatch driver files. While applying the db161sc.drv file, you may encounter some invalid PL/SQL packages, which are incompatible with Oracle8i Server Release 8.1.6.

These packages are listed in the Modifying PL/SQL packages for Oracle8i Server Release 8.1.6 section of these release notes. If you encounter the issues discussed in this section, follow the recommended modification and continue with your upgrade.

Step 10: Apply Oracle Applications patch 499648 for Oracle8i.

Log in to the system as applmgr. Make sure that all the required environment settings refer to Oracle7. Then apply patch 499648 to run Oracle Applications against an Oracle8i Server Release 8.1.6 database in the server partitioning model.

You must apply patch 499648 using AutoPatch (adpatch). Apply only the file portion of this patch (patch.drv), which is located in the <patchdir> you created in Step 3. Do not apply any of the database driver (*.drv) files that are available in the <patchdir>.

Step 11: Apply Oracle Applications patch 480129.

Apply patch 480129 (located in the <patchdir>/480129 directory) using adpatch. Do not follow the special instructions, if any, in the readme.txt file in this directory.

Step 12: Apply Oracle Applications patch 1054707.

Step 13: Apply Oracle Applications patch 487890.

Apply patch 487890 (located in the <patchdir>/487890 directory) using adpatch. Do not follow the special instructions, if any, in the readme.txt file in this directory.

This patch creates a new script called ADXGNPNS.sql, which you run in order to pin sequences. ADXGNPNS.sql is similar in function to ADXGNPIN.sql. If you run ADXGNPIN.sql, then you should also run ADXGNPNS.sql. See the comments in the script for more information.
 

Step 14: Modify PL/SQL packages for Oracle8i Server Release 8.1.6.

Instructions for maintaining the compatibility of PL/SQL packages in Oracle8i Server Release 8.1.6 are also listed in the Modifying PL/SQL Packages for Oracle8i Server Release 8.1.6 section of these release notes. If you apply any patch that updates these files, you may need to follow the instructions in that section to restore compatibility. The files are fixed in Oracle Applications Release 10.7 Patch Set 4.

Step 15: Compile invalid objects.

If you encounter invalid objects in this step, see the Modifying PL/SQL Packages for Oracle8i Server Release 8.1.6 section of these release notes.

Section 3: Modifying PL/SQL Packages for Oracle8i Server Release 8.1.6

In Oracle8i Server Release 8.1.6, certain PL/SQL packages require modification for compatibility. The packages and package bodies with incompatible constructs fall into three categories.

Category 1

For these package bodies, you may get the following error messages during package compilation:

    PLS-00103: Encountered the symbol "VARCHAR2" when expecting one of the

    following: ( The symbol "(" was substituted for "VARCHAR2" to continue.

    PLS-00103: Encountered the symbol "DATE" when expecting one of the 

    following: ( The symbol "(" was substituted for "DATE" to continue.

    PLS-00103: Encountered the symbol "NUMBER" when expecting

    one of the following: ( The symbol "(" was substituted for "NUMBER" to continue.

    hr_exu_bus.Check_Unique (p_rec.calendar_id  in number,

    p_rec.calendar_usage_id in number,

    p_rec.exception_id in number);

    hr_exu_bus.Check_Unique (p_rec.calendar_id,

    p_rec.calendar_usage_id,

    p_rec.exception_id);

Category 2

For this package, you may get the following error messages during compilation:

    PLS-00566: type name "TAX_AMT_REC_TYPE" cannot be constrained.

    --- For each tax code, or rate, a total tax amount is accumulated.

    ---

    --- This is required for summary by tax rate in the sales order

    --- acknowledgment report.

    ---

    TYPE tax_amt_rec_type IS RECORD

    (

    tax_code          AR_VAT_TAX.TAX_CODE%TYPE

    tax_rate          AR_VAT_TAX.TAX_RATE%TYPE

    amount            NUMBER

    );

    ---

    tax_code_amt   tax_amt_rec_type(100), /* Store tax amount per tax code */

    tax_rate_amt   tax_amt_rec_type(100), /* Store tax amount per tax rate */

Category 3

If you have the same or higher version of any of the packages listed above, then these packages will be valid in your database. Otherwise, please follow the instructions listed below.

AP_INV_SELECTION_CRITERIA_PKG

This package will be invalid due to the following statement:

    SELECT sum(decode(c.status_lookup_code, 'NEGOTIABLE',

                      c.amount, 0)) batch_total,

           sum(decode(c.status_lookup_code, 'VOIDED', 1,

                        'STOP INITIATED', 1,

                        'RECONCILED', 1, 0))

    INTO   batch_total, num_non_negotiable

    FROM   ap_checks c

    WHERE  c.checkrun_id = X_CHECKRUN_ID

    FOR UPDATE NOWAIT;

    SELECT sum(decode(c.status_lookup_code, 'NEGOTIABLE',

                      c.amount, 0)) batch_total,

           sum(decode(c.status_lookup_code, 'VOIDED', 1,

                        'STOP INITIATED', 1,

                        'RECONCILED', 1, 0))

    INTO   batch_total, num_non_negotiable

    FROM   ap_checks c

    WHERE  c.checkrun_id = X_CHECKRUN_ID ;

ARP_AA_HISTORY_PKG

ARP_CR_ICR_LINES_PKG

ARP_MISC_CASH_DIST_PKG

These package is invalid due to a statement like:

    SELECT  ...

    INTO    ...

    FROM    ...

    WHERE   ...

    FOR UPDATE OF STATUS NOWAIT;

    SELECT ...

    INTO   ...

    FROM   ...

    WHERE  ...

    FOR UPDATE OF variable_name NOWAIT;

Maintaining Compatibility

Modify the lines causing the errors by removing the type definitions in the calling functions in the PL/SQL source file and updating the database objects. To identify the incompatible sections, you can try compiling the package in your database without modifications first. Connect to a SQL session as the apps user and use the following SQL command:

     SQL> alter package <package name> compile body;

    Warning: Package Body altered with compilation errors.

    SELECT line, text from user_errors

    WHERE  name = upper ('<package name>')

           AND type = upper ('<package body>');

    SELECT line, text from user_errors

    WHERE  name = upper ('PO_Lines_SV')

           AND type = upper ('package body');

   SELECT text from user_source

   WHERE  name = upper ('<package name>')

          AND type = upper ('<package body>');

   SELECT text from user_source

   WHERE  name = upper ('PO_Lines_SV')

          AND type = upper ('package body');