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

We migrated to 11G R2 (11.2.0.4) 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] (appsjagan.blogspot.com)

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

SQL> ALTER SYSTEM SET "_FIX_CONTROL"='8560951:ON';

(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 SQL Developer 19.x.x & ORA-20001: Oracle error -6502: ORA-06502: PL/SQL: numeric or value error: character string buffer too small has been detected in fnd_global.set_nls

Hi guys

Okay, so you switched to Oracle SQL Developer for the main reason that the suite is from Oracle & it is absolutely free and putting loads of efforts to get accustomed with certain “JAVA” platform limitations (as per developers).

Everything is fine until you start getting “ORA-20001: Oracle error -6502: ORA-06502: PL/SQL: numeric or value error: character string buffer too small has been detected in fnd_global.set_nls” or errors those WERE never happening while you were trying to execute years old PL/SQL blocks which were always completing successfully using Quest TOAD or at SQL prompt itself.

Before scavenging through your archives to find the Toad Installer for a re-installation, give the following exercise a try.

Applicable to Windows ONLY!

Go to user specific “AppData\Roaming” folder, eg: C:\Users\rajesh\AppData\Roaming

and delete both the folders, “SQL Developer” & “sqldeveloper”

Usually whenever you download and start the latest version of SQL Developer, the new version checks for the older versions under Roaming profile & if found, prompts the user asking whether the existing preferences should be copied (that includes already saved connections and other setting you may have made), which may cause errors like the ONE I have had once after migrating 19.2.x to 19.4.x

Regardless whether you were using previous versions or NOT, deleting all folders for “SQL Developer” under the roaming profile will force the latest version of Oracle SQL Developer to start afresh & most probably will take care of unreliable error messages (Confirmed by running the PL/SQL blocks without outputting the same errors using Quest TOAD)

Thank me later ;)

rajesh