Connecting Oracle Developer 10g to 11G database takes long time

We migrated to 11G R2 ( for our Oracle Applications R12 few years back, yes few years back (2017) & lived with one of the worst experiences…

Connecting Oracle Developer 10g (Forms/Reports) suite to 11G database.

I have scavenged through community articles for long time before giving up. I hardly came across a single fix for the connection time that used to hang up the Developer suite at times…

Today, I decided to find a solution for the nagging SSH connection issues from Windows 11 to our LINUX application servers and realized that we didn’t update the DNS settings for them once after we decommissioned a domain controller. Once the SSH issues were rectified and addressed, my next attempt was to find a solution for “frmcmp_batch” taking long time to start compiling modules & I landed on the below post.

Credit: Oracle Applications DBA: Form Compilation Against a 11g Database Hangs or Takes a Very Long Time [ID 880660.1] (

As we are already on 11G R2, patching was not required. All I needed was to alter the hidden parameter “_FIX_CONTROL” as mentioned in the article.


(Use scope=spfile to make this change permanent. This will require you to restart the database.)

I opted to go without spfile for testing & as soon as applied, the “frmcmp_batch” started compiling the modules instantly, against the usual delay that ran into many minutes other times.

Out of curiosity, I tried to connect to the database from Developer 10g & the connection was instant! within a fraction of a second.

So DNS being one of the most important elements establishing successful connections, patches and fixes also play crucial role in providing stable connections. Were you stuck with the same issue? give the solution a try and let us know whether it helped you also.

Oracle “directory” object | OS level permissions

We are using Oracle EBS R12 and for a custom module, wanted to log the Oracle seeded API outputs to custom log files for later scrutiny & error corrections.

Following the standard procedure, create the folder like below

create or replace directory OMS_LOGS as '/u01/applmgr/oms_logs'

and started testing the directory using SQL Developer where I am logged in as APPS user

I kept getting the below errors

ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 8

and the detailed errors said this could be due to OS level access permissions. Luckily I landed on a stackoverflow discussion & one of the answers clearly said this error “could” be due to the file/folder permissions for user “oracle”.

As “root” I changed the permissions for the path “/u01/applmgr/oms_logs” like below

chmod g+w /u01/applmgr/oms_logs

and that fixed the invalid file operation errors. Hope this helps someone out there!

RMAN on Windows | Backup & Restore 11g R2 database

11g R2? too late to post anything for 11g? Wait, there are thousands of Businesses still using Oracle database 10g. So let us see how RMAN could be simplified on Windows. I hardly see many articles explaining such for Windows platform in general.

Considering you have 11g database already installed & setup for Archive logging, we’ll start by setting up RMAN backup first, then try a restore routine on a different sever.

Like cron jobs on Linux environment, for Windows we will use Task Scheduler for RMAN backups. Here are couple of batch files we will be using for making RMAN backup on Windows.

crosscheck archivelog all;
crosscheck backup;
DELETE NOPROMPT archivelog all completed before 'sysdate-7';
backup current controlfile format 'D:\RMAN_BACKUP\bkpcontrol_file.ctl_%d_%T'               ;

Save this script as “rman_backup.cmd”. You can save it with any other name ending with .cmd or .bat extension. Make sure you change the name in the next script incase if you choose a name other than “rman_backup.cmd”. Please note, I am taking backup on “D:\RMAN_BACKUP” folder, you should change “D:\RMAN_BACKUP” with the exact location that is used for the backup!

Now, we will create another cmd file that will call the above script.

@echo off
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%a-%%b)
For /f "tokens=1-2 delims=/:" %%a in ('time /t') do (set mytime=%%a%%b)
:: echo %mydate%_%mytime%

set filename="D:\RMAN_BACKUP\logs\%mydate%_%mytime%_rman_backup.log"
:: echo %filename%

rman target / nocatalog cmdfile='D:\scripts\rman_backup.cmd' log='%filename%'

Once again you can save this file with anytime, provided the extension is either .cmd or .bat. I saved this cmd file as “callrman.cmd”

Make sure the path ” D:\RMAN_BACKUP\logs” or equivalent exists prior running the scripts.

Now fire up Windows Task scheduler and create a basic job

That’s all. Everyday, at a said time, RMAN backups will be created and kept in the destination folder. We are using 7 days retention policy with the script file, so on the 8th day obsolete backups will be purged. Depending upon the disk space available, you should adjust the retention policy for your backups. Don’t forget to copy those backup pieces to an external medium to insure maximum availability during a crisis.

That completes the simplest form of Oracle RMAN backups on Windows platform.

RMAN Restore

Currently we are discussing about restoring the RMAN backup to same version of Oracle database. If you want to restore RMAN backup to a higher version of Oracle database, it will be an entirely different exercise.

RMAN backups are the fastest, easiest incase if you are doing it right. No hassles like creating tablespaces, schemas and then waiting hours for the import process to complete! The biggest size of the database I ever dealt with is 550GB and a server with 2×4 core processors, 16GB total memory restores the 140GB backup in less than 1.5 hours. I suggest you to start using RMAN backups the soonest possible regardless whether your database is just few megabytes in size.

Today we will see how to restore RMAN backup from Server A to Server B, ie, to a different physical server with a different database name.

It might sound bit unethical to ask you to create a new database using Oracle database creation wizard for this purpose, however this is what we will do for this exercise. This will save us some time figuring out setting up many other parameters for the new database at later stages.

So let us start.

Start DBCA and create a database with a database name that you prefer for the purpose, for eg: “ORCL”

Once the database created, shut it down & delete all the datafiles from the data store. For example, I had all my data files in the folder C:\oracle\oradata\orcl, where orcl is the database name!

Now copy the RMAN backups from Server A to Server B. I’ve copied only one day backups from Server A to Server B and make a note of the exact location where you copied the backup files. In my case, I copied the files to “C:\RMAN”

From an elevated command prompt start SQLPLUS as sysdba & start the database in non mounted mode.

Now, start another elevated command prompt and connect to RMAN in Auxiliary mode, for duplication of the database.

We are all set to restore the RMAN backup to Server B now. However, there are few things we have to insure to make a successful restoration. For example renaming the datafiles. Based on the new datastore location where you are restoring the database files, each data file should be renamed. Please refer the below sample script for the same.

duplicate database to "ORCL" backup location 'C:\RMAN' nofilenamecheck
) SIZE 1000M ,
) SIZE 1000M ,
) SIZE 1000M ,
) SIZE 1000M ;

As I am duplicating the database to existing one, I don’t have to go through the tedious process of changing the dbid or database name etc. Once the restore is done, all I need is to disable the archive logging and go online! We will see those in next few steps.

Please give attention to “db_file_name_convert“, Server A had the datafiles in the path “D:\oracle\oradata\PROD” and the Server B, we will be restoring the datafile to C:\oracle\ordata\orcl folder. So we must mention those changes here in the script. The same way we need to rename the logfiles and locations also. I hope the above script is self explanatory on that regard.

You may not need all those channel allocation for a small database, please adjust the number of cannels allocated as per your requirements. Give a try with less or more number of channels incase if you want to learn what happens :)

Execute the above script from the RMAN prompt.

If there are no errors, based on the size of the database & hardware resources RMAN will return to the prompt once the activities are finished.

Before doing anything else, we need to insure that the Archive logging is disabled for the newly restored database. Rush to the previous SQLPlus session that you started with nomount. This session is already disconnected, hence reconnect and shutdown immediate.

Now start the database in mount state and disable the archive logging.

Shutdown the database and restart normally.

That’s all, your Server B “orcl” has all the data from Server A “PROD” database now. If this is a production recovery, you can change the database name using “NID” utility, that is supported from Oracle database 9i onwards. Hope this helps few Oracle database beginners out there.

As we have restored full database with archive logs, there is no need to restore logs. Cross verify the database details

Cheers guys!

EBS R12 Cloned Instance | opatch

Not being a DBA has it’s own perks ;) Freedom to try to fix everything that do not look “legit” from all angles.

My last attempt was to rebuild “Central Inventory” for a cloned instance of Oracle Applications R12 (EBS 12.0.6) on Linux. This instance was cloned from a certified platform, to a totally unsupported platform. In addition to, the DBAs those migrated from 10g R2 to 11g R2 left much of the post clone activities pending immediately once after the instance came online.

So, trying to patch Oracle database 11gR2( brought me to a situation that never existed:

  • “opatch” was “not” recognized as a command!
  • “opatch lsinventory” run always screamed about corrupt/missing central inventory location.

The “opatch” not being recongized as a command was due to $ORACLE_HOME/OPatch path missing, easily fixed by adding the path to the existing $PATH environment variable for the Oracle user.

One of the other issues you might face up with could be the unsupported opatch version. You need to replace the current opatch version with the latest supported. Please refer to patch #6880880 & Install the patch marked in the below image

Please read the “README” to know how to replace the existing version with the latest supported.

Once the opatch stack taken care of, we still need to fix the central inventory, that keeps reported as being missing.

[oratest@bak clone]$ opatch lsinventory
 Oracle Interim Patch Installer version
 Copyright (c) 2020, Oracle Corporation.  All rights reserved.
 Oracle Home       : /u01/oratest/TEST/db/tech_st/11.2.0
 Central Inventory : /u01/oratest/TEST/db/tech_st/11.2.0/inventory
    from           : /u01/oratest/TEST/db/tech_st/11.2.0/oraInst.loc
 OPatch version    :
 OUI version       :
 Log file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2020-11-11_10-35-04AM_1.log
 LsInventorySession failed: OPatch failed to locate Central Inventory.
 Possible causes are:
     The Central Inventory is corrupted
     The oraInst.loc file specified is not valid.
 OPatch failed with error code 73

Let us see how to rebuild the Central Inventory for the EBS R12 cloned instance now.

If your current server (the one that has the cloned instance of the application) never had an Oracle installation executed from the same, will not have /etc/oraInst.loc file, instead the file must be available under $ORACLE_HOME & the file MUST BE pointing towards the source server paths. For example, my cloned instance oraInst.loc contents are like below:

>cat oraInst.loc

where inventory_loc path wrong for the current server.

You may create something equivalent to what already exists with the oraInst.loc file, for example (in my case) a path like /u01/oratest/oraInventory as “Oracle” user.

So, you need to amend the oraInst.loc file that is available with $ORACLE_HOME path like below

>cat oraInst.loc

Now you can proceed building the Central Inventory.

logon as Oracle user


  1. source the environment
  2. switch to $ORACLE_HOME/appsutil/clone
  3. execute (./

Now, We will attach the current ORACLE_HOME with the Central Inventory. Many things could go wrong here, so make sure that you refer the document “How to create, update or rebuild the Central Inventory for Applications R12” in case if you are stuck for reasons those are not mentioned in this post.

As Oracle user

switch to $ORACLE_HOME/oui/bin


./runInstaller -ignoreSysPrereqs -silent -attachHome -invPtrLoc $ORACLE_HOME/oraInst.loc \


./runInstaller -ignoreSysPrereqs -silent -attachHome -invPtrLoc $ORACLE_HOME/oraInst.loc \

If there are no issues, you should get ” ‘AttachHome’ was successful ” confirmation once after the installer finishes the execution.

Test opatch once again

>opatch lsinventory

The above should provide you an output like below:

[oratest@bak bin]$ opatch lsinventory
 Oracle Interim Patch Installer version
 Copyright (c) 2020, Oracle Corporation.  All rights reserved.
 Oracle Home       : /u01/oratest/TEST/db/tech_st/11.2.0
 Central Inventory : /u01/oratest/oraInventory
    from           : /u01/oratest/TEST/db/tech_st/11.2.0/oraInst.loc
 OPatch version    :
 OUI version       :
 Log file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/opatch2020-11-11_10-42-05AM_1.log
 Lsinventory Output file location : /u01/oratest/TEST/db/tech_st/11.2.0/cfgtoollogs/opatch/lsinv/lsinventory2020-11-11_10-42-
 Local Machine Information::
 ARU platform id: 0
 ARU platform description::
 There are no Interim patches installed in this Oracle Home.
 OPatch succeeded.

That’s all folks. You can proceed with your patching for the database now! Hope this helps few certified DBAs out there ;)


Oracle APEX 18.x Installation | Windows

Hello guys

Referenced documents:

Let us see how Oracle APEX (Which will eventually replace Oracle FORMS (regardless whether you like that fact or not) for small to medium applications) could be installed on your Windows 10/Server this time.

We’ll be trying the latest version of ORACLE APEX 18.2 & Windows 10 Pro running the latest version 1809 (as on date, fully patched) and Oracle database 11g R2 ( Oracle depends upon VC++ components loads, so make sure your Windows box is fully patched.

Step #1

Download Oracle APEX installer from APEX repository. Please note, for APEX 18.x, you MUST have 11G R2 or later database. 11g R2 is only available for customers with valid support, hence if you are a student, freelancer, you best bet is 12c database, that is still freely available to download.

Step #2

Unzip the downloaded .zip file to a finely structured directory. For example, you can extract the zip file to D:\APEX, that will create another “apex” folder within “D:\APEX\”. Check the attached image

I usually keep all the Oracle Installers under a single folder, called “D:\Oracle_Installers”. So basically the rest of the instructions, I will be using the same directory structure. Please don’t get confused.

Step #3

Switch to the apex folder from an elevated command prompt, in my case to “D:\Oracle_Installers\apex_18.2\apex”. Source the database environment (eg: SET ORACLE_SID=ORCL) in case if you have multiple Oracle products installed in the same box

Step #4

Start SQLPLUS as sysdba

A default installation of 11g R2 installs APEX, which you should remove at this stage. If you are too eager to know which version is installed, you my run the following query

select version from dba_registry where comp_id='APEX';

If you haven’t removed the APEX installation, it should return you a row with installed version information of APEX

You should remove the existing APEX prior installing the 18.x version(s). To remove the installed version, please execute as following


Wait for the uninstallation to complete, exit SQL and start SQLPLUS once again as sysdba (Mandatory)

Now we can start the installation of APEX 18.x. Prior starting the installation let us create a dedicated tablespace for APEX.


Now execute the SQL for installing APEX 18.x

@apexins APEX APEX TEMP /i/

(Note the forward slash "/")

Wait for the installation to finish. My box has a SSD, hence the installation completes in few minutes time (3-5 minutes I believe). It may take bit more time, depending upon the hardware resources available.

Once the installation completes successfully, we have to configure the APEX. To configure execute “apex_epg_config.sql” that is available in the current path. Please note, here is the catch, as with the installation, Oracle expects forward slashes around the parent folder where you have the image files. So, I have extracted the installation files to “D:\Oracle_Installers\apex_18.2\” & this is the PARENT directory for apex installer. Hence we will call the EPG script as below (correct your path appropriately)

@apex_epg_config.sql D:/Oracle_Installers/apex_18.2/

(Note all back slashes are replaced with forward slashes)

Once the script finally exists after successfully copying the image files to APEX repositories, you need to unlock the following Oracle database schemas


Now, go ahead with the last configuration by executing


This script will ask for ADMIN user details, Select ADMIN as admin user and provide an email address. Please note, you must provide a punctuation symbol for the password and allowed punctuations are as below


That’s all folks. If there are no errors, you are all good to go. You may need to configure your database server for better performances, which we will discuss bit later.

Now try to access the APEX installation. Unless you have changed the ports, you should able to access the portal by visiting http://localhost:8080/apex/apex_admin



Oracle Linked Server | MS SQL

Updated on: 20th November 2020

I see many visits to this thread from Microsoft Technet references & one of the visitors left me the following comment:

For 12c client, I tried applying the steps you described and it did not work.
 I found that there is a bug affecting Oracle Provider for OLE DB – Version and later
 Oracle OLEDB Throws Error “DBCOLUMNFLAGS_ISFIXEDLENGTH” to Handle CHAR(n BYTE) From Linked Server With MS SQL Server (Doc ID 2484393.1)

Thanks Larisa for your kindness to share the information & found that the error is tightly related to Oracle database version 12.1.x.x. Oracle note provides a workaround using OPENQUERY. I don’t think a “workaround” as a permanent solution, Instead, if possible, upgrade your database to 12.2 or higher versions as a possible permanent fix. Oracle claims this bug was addressed by “Windows Database Bundle Patch 26720785 and later versions”. If you don’t have Oracle’s valid support contract, will not able to download the patch & the only possible solution could be upgrading your 12c database while the download repositories still holding the 12c media. So hurry up :)

As Organizations grow, will end up with multiple software solutions taking care of different areas of the business. Based on the availability, there would be situations where business will end up with different database technologies and data exchanges between such will become a mandatory element to avoid duplication and additional efforts.

Today we will discuss about a requirement that involves MS SQL Server and Oracle database. While Microsoft has well established solutions called as Linked Servers, that allows the MS SQL to connect with heterogeneous database technologies, Oracle’s approach is pretty tedious and may require more efforts that one could anticipate. Yes, I am talking about Oracle “Golden Gate”, which allows an Oracle database to access other database technologies through a “gateway”

Instead of discussing the complex “Golden Gate”, we will see how simple (& complex at the same time) it is to setup Linked Servers from Microsoft SQL Server (for Oracle)

Install the client

Depending upon your OS architecture(32Bit/64Bit), you need to install the appropriate Oracle client. I suggest, install 64Bit Oracle Client on 64Bit OS and 32Bit client over 32Bit OS. You save loads of efforts by doing so. I always do a full installation of the Oracle client and later add missing components those are mandatory for “Distributed Transactions”. So what is a distributed transaction?

  • Your MS SQL Database table receives a row (record)
  • Your expectation is to replicate the same row to Oracle table
  • You have an “after insert” trigger defined with the MS SQL Table

A full installation of Oracle client (Administration) doesn’t install the mandatory component to facilitate the above requirement. You must install “Oracle Services for Microsoft Transaction Server” in order to do such a distributed transaction from MS SQL to Oracle database, after invoking the Client Installer. Make sure while the installer kicks in, you select the already existing Oracle home to make sure that the installation will not create another home for the additional components those you will install. Cross check whether the Oracle OLE DB driver is installed & install the Oracle Services for Microsoft Transaction Server component.

Analyze your Oracle provider and make changes to the stack

Make sure your provider, in our case, “OraOLEDB.Oracle” is configured prior you create linked servers.

Enable the following options

  • Allow inprocess
  • Nested Queries
  • Support “Like” operator

and disable

  • Index as access path. Disabling this option is mandatory for “Distributed Transaction”. If your Oracle table has indexes and this option is not disabled, an insert attempt from table trigger will fail with the following error

The OLE DB provider “OraOLEDB.Oracle” for linked server “ERPPROD” returned a “NON-CLUSTERED and NOT INTEGRATED” index “XXFPPUNCHM_N1” with the incorrect bookmark ordinal 0.

Create a linked Server

Right click on the “Linked Server” node and select “New Linked Server…”. I am providing you details of a linked server that is already created at my end. Adjust your linked server details accordingly. Make sure, the Oracle client installation folder is your “PATH” & that the tnsnames.ora file has an entry for the Oracle service (that you will enter in “Provider string” column.

If you have entered the mandatory elements correct, you have successfully created a linked server. If anything goes wrong you will be prompted about it, and address them. Please note, you can always revisit and change the Server options at later stages. However, options under “General” cannot be modified. If requires modifications, you need to drop and recreate the linked server once again.

Test the newly created linked Server

The actual issues starts from now. Please note, my laptop that I use for all developments has multiple versions of Oracle database and clients installed in addition to .Net development tools, Android, PHP etc to name few. If you are planning to implement the linked server solution for a production environment, make sure you have only one Oracle product installed along with MS SQL server. On the other hand you are going to have end number of complexities, few of which are not easily addressed.

For example, my development machine has:

  • Oracle database 11g
  • Oracle Client 12c (12.1)
  • Oracle Database 12c (12.2)

Which is a more than complex situation to address when it is all about MS SQL linked servers. One of the toughest issues to address is the following error

The OLE DB provider “OraOLEDB.Oracle” for linked server “” supplied inconsistent metadata for a column. The column “” (compile-time ordinal 2) of object “””.””” was reported to have a “DBCOLUMNFLAGS_ISFIXEDLENGTH” of 16 at compile time and 0 at run time.

It took me almost 1.5 days to figure out what could be wrong as a simple SQL query like following against the newly created linked server continuously provided me the above error.

Select count(*) from [ERPTEST]..[APPS].[XXFPPUNCHM] 

I’ve come across a post over which said, this could be due multiple Oracle products being installed in the same box & there were few instructions to overcome, which didn’t workout for me. However, I was successful with the production server, in which I only had the Oracle 11g client installed. To insure the real time replication of the data from MS SQL to Oracle database, I had to alter few registry values & restart the server. To my utter surprises, the same scenario I tested over three different boxes & all three experiences were different from each other.

  • The production server where I have SQL Server 2014 standard edition would not post rows to Oracle database, that is a part of “Distributed Transaction” without the registry hacks.
  • My development laptop wouldn’t even fetch rows from Oracle database without tweaking the PATH environment element & registry with proper .dll paths
  • My home PC does everything without having to tweak the path of registry where I have almost the same setup like my development laptop. The ONLY one difference with my home PC is, instead of Oracle client 12c, I have 11g client.

Now we will address each of such situations. Please note, the following exercises require you to make registry changes, so please make sure that you take a full backup of the registry prior attempting any given possible solutions. (If you are having ONLY one Oracle product installed (Database or Client, please move to Step#2 )

Step#1: Register Oracle OLEDB driver (This is to insure that we are using the same stack across the solution). Only one version of OLEDB driver could be activated at a time, regardless how many Oracle products are installed. If you had 12c installed after 11g, you must be having the 12c OLEDB driver activated.

From an elevated command prompt, switch to Oracle Client/Database BIN folder (eg: D:\oracle\product\11.2.0\dbhome_1\BIN)

Issue the following command

regsvr32 OraOLEDB11.dll

This should register the OLE DB driver for you.

Step#2: Check your OS PATH environment element, your client/database bin path must be the first Oracle product entry, eg:

This will insure that tnsnames.ora will be sought in this path, in addition to Oracle dlls. As we are using Oracle database 11g as the first product from the list of other Oracle products installed in the environment element PATH (refer the image above), we will hack the registry with all elements related to the specific product (Again, please make a backup of the registry, minimum the specific key)


Refer the image above and adjust the entries as per your Oracle installation. Once the registry is modified, restart your computer (mandatory)

Once the box restarted, try to insert a row into the Oracle table. Example

insert into [PRODBAK]..[APPS].[XXFPPUNCHM]
 VALUES(GETDATE(),'IT Office','0046','0')

SQL Management studio must stop complaining about “The OLE DB provider “OraOLEDB.Oracle” for linked server “” supplied inconsistent metadata for a column. The column “” (compile-time ordinal 2) of object “””.””” was reported to have a “DBCOLUMNFLAGS_ISFIXEDLENGTH” of 16 at compile time and 0 at run time.” right after setting up the correct .dll files and the relevant paths in the registry.

Now create your table trigger, through which you want to push a row to the Oracle table. A simple after insert trigger could be defined like following:

create trigger addRecordsToERPTable2 on [UNIS].[dbo].[tRajesh]
 after insert
 insert into [PRODBAK]..[APPS].[XXFPPUNCHM]
 FROM inserted

You may come across an error while a distributed transaction is initiated, Management Studio complaining about “Msg 8501, Level 16, State 3, Procedure addRecordsToERPTable2, Line 13
MSDTC on server ” is unavailable.” This is a pretty simple error to address. Open the Windows Services & check whether the service “Distributed Transaction Coordinator” has started. My development machine initially had this service startup mode set as “Manual”, I changed it to “Automatic delayed start” and started the service. Adjust according to your situation.

Basically the above few things should address most of the common issues you would face with Oracle Linked Server from MS SQL.

Finally, Oracle clearly states, there are limitations using their driver for Linked Servers from MS SQL. So, expect for unexpected while using such a setup. For me, it was simple transactions. If you are expecting rapid replications based on complex business requirements, please do test your scenarios as much as possible prior adapting the above hacks.



OLE DB provider “OraOLEDB.Oracle” for linked server “” returned message “New transaction cannot enlist in the specified transaction coordinator.”

Hi guys

A pretty long title? Well, recently I came across a situation where I needed a trigger with MS SQL server table to insert some information into our Oracle database.

The MS SQL Server is hosted in a Windows 64 bit OS, with Oracle 11g 64Bit client installed (For 64Bit OS, you must install Oracle client 64Bit for the Oracle OLEDB provider)

I did some sample inserts using the Management studio and created a trigger like following with one of the sample tables:

create trigger addRecordsToERPTable on [UNIS].[dbo].[tRajesh]
after insert
  insert into [XYZ].[APPS].[XXFPPUNCHM] 
  FROM inserted

So the idea was pretty simple, like an audit, as soon as the SQL table “rRajesh” has a new row inserted, the after insert trigger should sent the same row to underlying table over Oracle. Instead I started getting the following error:

OLE DB provider “OraOLEDB.Oracle” for linked server “XYZ” returned message “New transaction cannot enlist in the specified transaction coordinator. “.
Msg 7391, Level 16, State 2, Procedure addRecordsToERPTable, Line 5
The operation could not be performed because OLE DB provider “OraOLEDB.Oracle” for linked server “XYZ” was unable to begin a distributed transaction.

I’m not very familiar with MS SQL or the complexities related to Linked Server environments. So, started my next series of Google searches. I referred tons of discussions, however was not getting anywhere with the dreaded situation. During the frantic search for a solution, I executed the instructions available over different links.

Even after making changes as mentioned with the above threads, I still kept on receiving the same errors while a row was inserted into my SQL sample table. So I continued searching for a solution and came across a thread

This thread was pointing towards a Microsoft’s post addressing this particular situation.

Although the article addresses pretty Old OS and Oracle environments, the solution is still applicable on later OS and Oracle clients. For example, My MS SQL Server is installed over Windows 2008 R2 and the Oracle client I am using with the server is 11G R2 64Bit.

Let us see quickly what Microsoft provides as a solution.

I checked the registry of my server and found something pretty interesting like below:

Now, Oracle names almost all their major dll files in a particular fashion. Most of the times you will find the dll files having the major version numbers by the end of the filename, for example, if your Oracle database is 8.0, your client dll file will be “Oraclient8.dll” and if you are using Oracle 11g, the filename would be “Oraclient11.dll”

After taking a full backup of the registry, I modified the values with 11g specific & restarted the Server (as per the instructions available for Oracle 8.1 in the Microsoft document.)

Once the server started, I went ahead and tried to insert a new row into my sample table and that was it. No more errors and the row was inserted to both MS SQL table and Oracle table at the same time.

So if you were frantically searching for a solution, this post may help you to resolve it.



Oracle Application R12|Payables|FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403

Hello guys

Recently one of our accountants forwarded me a screenshot, that was showing “FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403” while he was trying to enter invoices against a “NEWLY” created vendor/supplier.


Our Oracle Application R12 (12.0.6) is considered 99.99999% stable, without a single technical or functional issue that really become a show stopper throughout last many years.

Well, this particular issue looked perplexing as it was not dealt by Oracle Application’s error reporting & slowly we started dwelling Oracle support documents those were dealing with the given forms error “FRM-40735 WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-01403”

Most of the documentations where mentioning about IBY duplicate pay party, which was not our case. Hence, I decided to open the associated form APXINWKB.fmb & located the WHEN-VALIDATE-ITEM trigger associated with the column “Purchase Order Number”. I couldn’t find any irregularities between an order that didn’t raise the error and this particular Purchase order did raise the exception, which was unhandled.

After two days of continuous attempts, I remembered that such errors happen in other forms modules when we had missing information for new vendors/suppliers. Must be due to a bug, there were times when site level details were NOT populated to organizations level details for a vendor/customer & I decided to go through all mandatory elements those were expected while creating a new vendor/supplier.

I sat with my colleague and we reached to “Payment Method”, and realized that the default payment method was not set for this particular vendor against the organization where we were getting this unhandled exception.

Once the payment method was set, the invoice was posted for the vendor successfully! So, if you come across these kind of unhandled exceptions across Oracle’s proprietary forms those deal with payments/invoices, prior exhausting yourself with cloning and patching, make sure you have all the mandatory elements for customer/vendors are properly filled in and assigned to all the organizations.

Hope this finding helps few consultants out there!