The following error messages may appear when using SQL Developer to select data from a SQL Server table via a (heterogeneous or DG4ODBC) database link on an Oracle 11g DB:
Bigger type length than Maximum
OALL8 is in an inconsistent state
Solution: switch to the thick driver to work around this.
In SQL Developer: tools -> preferences -> database -> advanced -> use oci/thick driver = checked.
If this does not work or when getting the error messages:
no ocijdbc11 in java.library.path
vendor code 0
then:
- Download Oracle Instant Client 11.1.0.7.0 basic lite (easily found with Google, register with Oracle to download)
Note: version 11.2 does not work with SQL Developer!
Note: take the 64-bits version on a 64-bits OS, like Windows7 x64.
- Extract files to c:\ora\instantclient_x64_11.1.0.7.0_basiclite
- Add to environment variable PATH: c:\ora\instantclient_x64_11.1.0.7.0_basiclite
- Create a batch file called sd.bat in the rootfolder of the SQL Developer installation, with the following body:
set ORACLE_HOME="c:\ora\instantclient_x64_11.1.0.7.0_basiclite"
start sqldeveloper.exe
- Double-click sd.bat to start SQL Developer
- Add a connection with Connection type = basic
To make a connection with Connection type = TNS:
- Create folder c:\ora\instantclient_x64_11.1.0.7.0_basiclite\network\admin
- Put these files in the admin folder:
sqlnet.ora
tnsnames.ora
- In SQL Developer: tools -> preferences -> database -> advanced -> tnsnames folder: c:\ora\instantclient_x64_11.1.0.7.0_basiclite\network\admin
- Add a connection with Connection type = TNS
Note the following:
- The solution above even (or especially) works when another Oracle client is already installed. For example, if the OracleXE DB (which, at the time of writing, is 10g) is installed locally, then the Oracle Client 10 is also installed. But this client might not work when trying to connect to a remote Oracle 11g DB
- There is no need to set the environment variables ORACLE_HOME or TNS_ADMIN variable on the system. It does not matter what their values are
- The order of folders in the variable PATH does not matter
- Most likely Help -> About -> Properties -> oracle.home does not point to the correct Oracle home folder. So not to c:\ora\instantclient_x64_11.1.0.7.0_basiclite. It does not matter what the value is
- Unfortunately, the ORACLE_HOME trick does not work with the PATH variable; it is no use adding that to the batch file
- The error "Bigger type length than Maximum" is related to a bug in the implementation of the thin part of the JDBC driver. Not to heterogeneous services, Oracle DB, SQL Server, SQL Developer or the thick part of the JDBC driver. (Note: the same driver, e.g. ojdbc6.jar, contains the thin and the thick classes)
