Oracle Autonomous Database with Oracle Forms and Reports

Thank you to Suresh Sangaran for providing the information for this blog.

Since the introduction of Oracle’s flagship Autonomous Database, many client applications have been reformed to take advantage of consuming and integrating the most advanced database that Oracle has produced. The legacy Oracle Forms and Reports is not behind to integrate and support with Oracle’s Autonomous Database.

Beginning 12.2.1.4.0, Oracle Forms and Reports is certified to connect with Oracle Autonomous database for both application data and/or the required Fusion Middleware Infrastructure Repository. Click here for more information.

Note: As of July 2023, Only Oracle Forms and Reports 12.2.1.4.0 and 12.2.1.19.0 is certified and supported with Oracle Autonomous Database.

Certification Matrix:

It is important to read the certification maxtrix to understand if your version of Autonmous Database is supported with Oracle Forms and Reports.

How to configure the ADB (Autonomous Database) with Oracle Forms and Reports.

History:
By default Oracle Forms and Reports uses TLS authentication connections, Beginning January 2023 Oracle ADBs (Autonomous Databases) are preconfigured with mTLS – Mutual TLS authentication connections to comply with industry standards as of August 2022. Unfortunately, TLS connections will work only on Oracle Clients using 19.13 or a later version, but Oracle Forms and Reports 12.2.1.4.0 uses Oracle Client 12.1.0.* version.

With the above disadvantage, we shall see how to configure the ADB(Autonomous Database) with Oracle Forms and Reports 12.2.1.4.0.

Important: Oracle Forms/Reports 12.2.1.19.0 already uses Oracle 19c Client so the below steps do not apply for mTLS connection.

Windows:
——–
1. Download the Database connection wallet from the Autonomous database.

2. Copy and unzip the wallet file under Oracle Forms DOMAIN_HOME\config\fmwconfig\

example: Wallet_XXXXXXX

Note: If you unzip the wallet contents under DOMAIN_HOME\config\fmwconfig\ it will overwrite the existing cwallet.sso and tnsnames.ora. So keep the Autonomous database wallet separately.

3. Open Windows Registry and update the new path for TNS_ADMIN for FMW ORACLE_HOME.

Registry –>Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Oracle\KEY_OracleHomeXXXXXX

4. By default sqlnet.ora from Autonomous database wallet will have the WALLET_LOCATION as DIRECTORY=”?/network/admin”. Update the DIRECTORY with the actual path as extracted in step 2. So in this example below is the DIRECTORY path.

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=”<Drive>:DOMAIN_HOME\config\fmwconfig\Wallet_XXXXXXX”)))
SSL_SERVER_DN_MATCH=yes

5. Backup and Edit DOMAIN_HOME\reports\bin\reports.bat and change the lines as below.

@REM set TNS_ADMIN=%DOMAIN_HOME%\config\fmwconfig

set TNS_ADMIN=%DOMAIN_HOME%\config\fmwconfig\Wallet_XXXXXXX

6. Ensure the latest QPR is already applied to avoid any known errors. Refer to Oracle Forms Quarterly Patch Release (QPR) information (Doc ID 2834458.2)

7. Apply the latest CPU/PSU patches for FMW 12.2.1.4.0 and above versions. Refer to Critical Patch Update (CPU) Patch Advisor for Oracle Fusion Middleware – Updated for July 2023 (Doc ID 2806740.2)

8. Restart the full domain and test the Autonomous database connection with the Oracle Forms and Reports Builder and with Applications.

Linux:

1. Download the Database connection wallet from the Autonomous database.

2. Copy and unzip the wallet file under Oracle Forms DOMAIN_HOME/config/fmwconfig/

example: Wallet_XXXXXXX

Note: If you unzip the wallet contents under DOMAIN_HOME/config/fmwconfig/ it will overwrite the existing cwallet.sso and tnsnames.ora. So keep the Autonomous database wallet separately.

3. Edit your default.env or <custom_name>.env file. Update the TNS_ADMIN with wallet_XXXXXXX directory

# TNS Entry to locate the database
#
TNS_ADMIN=DOMAIN_HOME/config/fmwconfig/wallet_XXXXXXX

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY=”DOMAIN_HOME/config/fmwconfig/wallet_XXXXXXX”)))
SSL_SERVER_DN_MATCH=yes

Note: For the above steps 3 and 4 variable DOMAIN_HOME is not predefined, so try to use the full context path.

5. Backup and Edit DOMAIN_HOME/reports/bin/reports.sh and change the lines as below.

## You need to set TNS_ADMIN and TWO_TASK or ORACLE_SID to connect to database
## Some default values have been set below

if [ “$TNS_ADMIN” = “” ]
then
   if [ -f “/etc/tnsnames.ora” ]
   then
        TNS_ADMIN=”/etc”; export TNS_ADMIN
   else
        TNS_ADMIN=${DOMAIN_HOME}/config/fmwconfig/wallet_XXXXXXX; export TNS_ADMIN
   fi
fi

6. Ensure the latest QPR is already applied to avoid any known errors. Refer to Oracle Forms Quarterly Patch Release (QPR) information (Doc ID 2834458.2)

7. Apply the latest CPU/PSU patches for FMW 12.2.1.4.0 and above versions. Refer to Critical Patch Update (CPU) Patch Advisor for Oracle Fusion Middleware – Updated for July 2023 (Doc ID 2806740.2)

8. Restart the full domain and test the Autonomous database connection with the Oracle Forms and Reports Builder and with Applications.

Known Issues and References

How to Connect Oracle Autonomous Database With Oracle Forms Builder 12c (Doc ID 2929302.1)
12.2.1.4.0 Forms Builder with Autonomous Database shows error “ORA-29003: SSL transport detected mismatched server certificate” (Doc ID 2929345.1)
How to Connect Oracle Autonomous Database With Oracle Reports Builder 12c (Doc ID 2929635.1)
Error ORA-12560 or ORA-29003 for 12.2.1.4.0 Reports Builder Connecting with Autonomous Database (Doc ID 2929697.1)

Oracle Forms on MarketPlace
If you would like to use Oracle Forms on MarketPlace with Autonomous Database, please refer to Using Oracle Forms for Oracle Cloud Infrastructure.

Oracle Autonomous Database with Oracle Forms and Reports