Migrating from Oracle 8 to Oracle 9i JDBC Connection Problems

The company I'm working for is in the process of migrating to Oracle 9i Version 2. We currently have a Java application that connects to the Oracle 8 database using the JDBC thin driver. This works flawless but with the upgrade I've experience nothing but problems trying to connect out to our test instance. I have downloaded the latest JDBC drivers from Oracle. We are running on Java 1.4 and have tried using both drivers, classes12.zip & ojdbc14.jar. I think the problem might be in my connect string.
Has anyone experienced this and worked through this problem? If so could I see an example.
Thanks,
Robert

Have you tried using the non-thin link - 'oci'?
Regards.
Mickey Joe

I've experience nothing but problems

would that include a stack trace?

I tried using the OCI driver. Here is the code that it says I'm having a problem with:
(DbStatus.java:1075)setConnection(DriverManager.getConnection("jdbc

racle

ci:@shard", "user", "password"));
Here is the command I use to launch our app 'Monitor'.
c:\j2sdk1.4.0_01\bin\java -cp .;classes12.zip Monitor
I've also replaced classes12.zip with ojdbc14.jar but get pretty much the same result.
Here is the output I'm getting at the command prompt.
//-----------------------------------------------------------------------
DbStatus::connectToDb(): Before DriverManager.registerDriver
DriverManager.initialize: jdbc.drivers = null
JDBC DriverManager initialized
registerDriver: driver[className=oracle.jdbc.driver.OracleDriver,oracle.jdbc.dri
ver.OracleDriver@e0ff2f]
registerDriver: driver[className=oracle.jdbc.OracleDriver,oracle.jdbc.OracleDriv
er@9173ef]
DbStatus::connectToDb(): Before DriverManager.getConnection
DriverManager.getConnection("jdbc

racle

ci:@shard")
trying driver[className=oracle.jdbc.driver.OracleDriver,oracle.jdbc.driver.O
racleDriver@e0ff2f]
DriverManager.deregisterDriver: oracle.jdbc.OracleDriver@9173ef
An unexpected exception has been detected in native code outside the VM.
Unexpected Signal : EXCEPTION_ILLEGAL_INSTRUCTION occurred at PC=0xB23662D
Function=szlglb+0x1862D
Library=W:\apps\Ora92\bin\orageneric9.dll
Current Java thread:
at oracle.jdbc.oci8.OCIDBAccess.do_open(Native Method)
at oracle.jdbc.oci8.OCIDBAccess.logon(OCIDBAccess.java:480)
- locked <0312D690> (a oracle.jdbc.oci8.OCIDBAccess)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:360)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.ja
va:521)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:325)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
- locked <06E20048> (a java.lang.Class)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
- locked <06E20048> (a java.lang.Class)
at DbStatus.connectToDb(DbStatus.java:1075)
at Monitor.createDbStatus(Monitor.java:1715)
at Monitor.<init>(Monitor.java:247)
at Monitor.main(Monitor.java:175)
Dynamic libraries:
0x00400000 - 0x00406000 c:\j2sdk1.4.0_01\bin\java.exe
0x77F60000 - 0x77FBF000 C:\WINNT\System32\ntdll.dll
0x77DC0000 - 0x77DFF000 C:\WINNT\system32\ADVAPI32.dll
0x77F00000 - 0x77F5F000 C:\WINNT\system32\KERNEL32.dll
0x77E70000 - 0x77EC2000 C:\WINNT\system32\USER32.dll
0x77ED0000 - 0x77EFC000 C:\WINNT\system32\GDI32.dll
0x77E10000 - 0x77E67000 C:\WINNT\system32\RPCRT4.dll
0x78000000 - 0x78044000 C:\WINNT\system32\MSVCRT.dll
0x10980000 - 0x10986000 C:\WINNT\System32\ROUSRNT.DLL
0x6D330000 - 0x6D445000 c:\j2sdk1.4.0_01\jre\bin\client\jvm.dll
0x77FD0000 - 0x77FFA000 C:\WINNT\System32\WINMM.dll
0x6BC00000 - 0x6BC0B000 C:\WINNT\System32\alcxnt.dll
0x6D1D0000 - 0x6D1D7000 c:\j2sdk1.4.0_01\jre\bin\hpi.dll
0x6D300000 - 0x6D30D000 c:\j2sdk1.4.0_01\jre\bin\verify.dll
0x6D210000 - 0x6D228000 c:\j2sdk1.4.0_01\jre\bin\java.dll
0x6D320000 - 0x6D32D000 c:\j2sdk1.4.0_01\jre\bin\zip.dll
0x6D000000 - 0x6D0F6000 C:\j2sdk1.4.0_01\jre\bin\awt.dll
0x77C00000 - 0x77C18000 C:\WINNT\System32\WINSPOOL.DRV
0x76AB0000 - 0x76AB5000 C:\WINNT\System32\IMM32.dll
0x77B20000 - 0x77BD1000 C:\WINNT\system32\ole32.dll
0x6D180000 - 0x6D1D0000 C:\j2sdk1.4.0_01\jre\bin\fontmanager.dll
0x6D2D0000 - 0x6D2DD000 C:\j2sdk1.4.0_01\jre\bin\net.dll
0x776D0000 - 0x776D8000 C:\WINNT\system32\WSOCK32.dll
0x776B0000 - 0x776C4000 C:\WINNT\system32\WS2_32.dll
0x776A0000 - 0x776A7000 C:\WINNT\system32\WS2HELP.dll
0x74FF0000 - 0x74FFE000 C:\WINNT\System32\rnr20.dll
0x66210000 - 0x66219000 C:\WINNT\system32\netware\NWWS2NDS.DLL
0x50D50000 - 0x50D95000 C:\WINNT\System32\NETWIN32.DLL
0x50D00000 - 0x50D15000 C:\WINNT\System32\CLNWIN32.DLL
0x50DF0000 - 0x50E10000 C:\WINNT\System32\LOCWIN32.DLL
0x50DB0000 - 0x50DD9000 C:\WINNT\System32\NCPWIN32.dll
0x66220000 - 0x6622C000 C:\WINNT\system32\netware\NWWS2SLP.DLL
0x1C000000 - 0x1C007000 C:\WINNT\System32\NWSRVLOC.dll
0x77660000 - 0x7766F000 C:\WINNT\system32\msafd.dll
0x77690000 - 0x77699000 C:\WINNT\System32\wshtcpip.dll
0x77BF0000 - 0x77BF7000 C:\WINNT\System32\rpcltc1.dll
0x62F00000 - 0x62F0F000 W:\Apps\Ora92\bin\ocijdbc9.dll
0x610A0000 - 0x6113F000 W:\apps\Ora92\bin\oracore9.dll
0x612A0000 - 0x61319000 W:\apps\Ora92\bin\oranls9.dll
0x613A0000 - 0x613B1000 W:\apps\Ora92\bin\oraunls9.dll
0x60600000 - 0x60784000 W:\apps\Ora92\bin\oraclient9.dll
0x60800000 - 0x60806000 W:\apps\Ora92\bin\oravsn9.dll
0x60500000 - 0x60590000 W:\apps\Ora92\bin\oracommon9.dll
0x0B070000 - 0x0B2C7000 W:\apps\Ora92\bin\orageneric9.dll
0x62FC0000 - 0x6303D000 W:\apps\Ora92\bin\oraxml9.dll
0x780A0000 - 0x780B2000 C:\WINNT\System32\MSVCIRT.dll
0x630F0000 - 0x63110000 W:\apps\Ora92\bin\oraxsd9.dll
0x615A0000 - 0x61629000 W:\apps\Ora92\bin\orannzsbb9.dll
0x61480000 - 0x61534000 W:\apps\Ora92\bin\oran9.dll
0x61400000 - 0x6142C000 W:\apps\Ora92\bin\oranl9.dll
0x61960000 - 0x61971000 W:\apps\Ora92\bin\oranldap9.dll
0x62000000 - 0x62024000 W:\apps\Ora92\bin\oraldapclnt9.dll
0x616B0000 - 0x616C8000 W:\apps\Ora92\bin\orancrypt9.dll
0x65340000 - 0x653DB000 C:\WINNT\system32\OLEAUT32.dll
0x62300000 - 0x6233E000 W:\apps\Ora92\bin\ORATRACE9.dll
0x61730000 - 0x61766000 W:\apps\Ora92\bin\oranro9.dll
0x617C0000 - 0x617C6000 W:\apps\Ora92\bin\oranhost9.dll
0x617D0000 - 0x617D6000 W:\apps\Ora92\bin\oranoname9.dll
0x616A0000 - 0x616A6000 W:\apps\Ora92\bin\orancds9.dll
0x61820000 - 0x61827000 W:\apps\Ora92\bin\orantns9.dll
0x64000000 - 0x64007000 W:\apps\Ora92\bin\oranms.dll
0x64020000 - 0x64030000 W:\apps\Ora92\bin\oranmsp.dll
0x60A00000 - 0x60D13000 W:\apps\Ora92\bin\orapls9.dll
0x62500000 - 0x62507000 W:\apps\Ora92\bin\oraslax9.dll
0x61350000 - 0x61360000 W:\apps\Ora92\bin\orasnls9.dll
0x60810000 - 0x60816000 W:\apps\Ora92\bin\orawtc9.dll
0x62600000 - 0x62675000 W:\apps\Ora92\bin\orasql9.dll
0x76AC0000 - 0x76ADD000 C:\WINNT\System32\imagehlp.dll
0x71DC0000 - 0x71DCA000 C:\WINNT\System32\PSAPI.DLL
Local Time = Thu Sep 11 10:36:30 2003
Elapsed Time = 17
#
# The exception above was detected in native code outside the VM
#
# Java VM: Java HotSpot(TM) Client VM (1.4.0_01-b03 mixed mode)
#
# An error report file has been saved as hs_err_pid216.log.
# Please refer to the file for further information.
#
//-----------------------------------------------------------------------
I'll post the results of the THIN client here in another reply.

The Oracle DBA told me to use that connect string. In our previous version we were using the host name, port address, and instance name. This time around he told me just to use the instance name.
setConnection(DriverManager.getConnection("jdbc

racle:thin:@shard", "user", "password"));
Here are the results I'm getting from using the THIN client.
//------------------------------------------------------------------
DbStatus::connectToDb(): Before DriverManager.registerDriver
DriverManager.initialize: jdbc.drivers = null
JDBC DriverManager initialized
registerDriver: driver[className=oracle.jdbc.driver.OracleDriver,oracle.jdbc.dri
ver.OracleDriver@e0ff2f]
registerDriver: driver[className=oracle.jdbc.OracleDriver,oracle.jdbc.OracleDriv
er@9173ef]
DbStatus::connectToDb(): Before DriverManager.getConnection
DriverManager.getConnection("jdbc

racle:thin:@shard")
trying driver[className=oracle.jdbc.driver.OracleDriver,oracle.jdbc.driver.O
racleDriver@e0ff2f]
DriverManager.deregisterDriver: oracle.jdbc.OracleDriver@9173ef
SQLException: SQLState(null) vendor code(17002)
java.sql.SQLException: Io exception: SO Exception was generated
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:418)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.ja
va:521)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:325)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
at DbStatus.connectToDb(DbStatus.java:1075)
at Monitor.createDbStatus(Monitor.java:1715)
at Monitor.<init>(Monitor.java:247)
at Monitor.main(Monitor.java:175)
getConnection failed: java.sql.SQLException: Io exception: SO Exception was gene
rated
DbStatus::connectToDb(): Oracle Connection SQLException: java.sql.SQLException:
Io exception: SO Exception was generated
DbStatus::connectToDb(): Oracle Connection SQLException: java.sql.SQLException:
Io exception: SO Exception was generated
//------------------------------------------------------------------

Here is the data thats in the tnsnames.ora file.
SHARD.yourcompany.com =
(DESCRIPTION =
(failover = on)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 999.99.999.001)(PORT = 1524))
(ADDRESS = (PROTOCOL = TCP)(HOST = 999.99.999.002)(PORT = 1524))
(load_balance = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = SHARD.yourcompany.com)
(failover_mode =
(type = select)
(method = basic)
(retries = 20)
(delay = 15)
)
)
)
SHARD1 = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=xsdrac01)(PORT=1524)))(CONNECT_DATA=(SID=SHARD1)))
SHARD2 = (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=xsdrac02)(PORT=1524)))(CONNECT_DATA=(SID=SHARD2)))

I've never used the OCI driver, but since the "thin" driver is a type-4 it doesn't use any of the Oracle client files. That means you have to use the IP (or host name), port name and SID with the "thin" driver. It should work with the "ojdbc14.jar" file.
Try the "thin" driver, making sure that "ojdbc14.jar" is in your class path and that "classes12.zip" is NOT in your path. If you have both of them in the path, it might be resolving the driver in "classes12.zip" first, which wouldn't work.

I went ahead and modified the connect string as so:
setConnection(DriverManager.getConnection("jdbc

racle:thin:@XSDRAC01:1524:SHARD1", "cap_user", "cap_user99"));
I compiled with this:
c:\j2sdk1.4.0_01\bin\javac -classpath .;ojdbc14.jar *.java
I ran with this:
c:\j2sdk1.4.0_01\bin\java -cp .;ojdbc14.zip Monitor
It says I'm erroring on this line:
(DbStatus.java:1068)DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
I got the following error message:
//-----------------------------------------------------------
DbStatus::connectToDb(): Before DriverManager.registerDriver
Exception in thread "main" java.lang.NoClassDefFoundError: oracle/jdbc/OracleDri
ver
at DbStatus.connectToDb(DbStatus.java:1068)
at Monitor.createDbStatus(Monitor.java:1715)
at Monitor.<init>(Monitor.java:247)
at Monitor.main(Monitor.java:175)

You might want to check the new JAR file, but for all previous versions of the "thin" client, the driver is "oracle.jdbc.driver.OracleDriver". So you can register is using:
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
or
Class.forName("oracle.jdbc.driver.OracleDriver");
The connect string may need to be massaged. You have ...@XSDRAC01:1524:SHARD1. Remember that the thin driver won't use the tnsnames.ora file, so this will work only if it can resolve "XSDRAC01" to a real IP address. It's probably best if you use the IP address, or the fully qualified name (if you do a "ping -a 999.99.99.99" it should show you the fully qualified name).
Once you make those changes you should be good to go.

Hi,
OCI is type 2 Partial java driver. U need to install Oracle Client on your PC to make JDBC connection. Also, your $ORACLE_HOME/bin directory must in the system. It needs ocijdbc8.dll to work.
I would rather advise you to spend your time on thin driver as it requires minimal efforts. It is a type 4 java driver and doesn't need any client side installation. Yeak but you must have classes12.zip (get the right one) in your class path.
Good Luck..
Upen

Thanks for all the responses. I'll try the ping solution when I get back into work first thing in the morning.
Thanks,
Robert

ok, I tried all the suggestions was finally able to connect to the database but I'm running into another problem. So to minimize the problem I went ahead and created a stand-alone java app to query the database. I got the same results.

//-------------------------------------------------------------------------
Here is the error message:
Exception in thread "main" java.sql.SQLException: ORA-00942: table or view does not exist
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1674)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:187
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:538)
at JdbcCheckup.main(JdbcCheckup.java:26)
//-------------------------------------------------------------------------
//Here is the stand-alone app to connect in to Oracle are return a query.
import java.sql.*;
public class JdbcCheckup
{
public static void main(String args[]) throws SQLException
{

String url = "jdbc

racle:thin:@999.99.999.001:1524:SHARD1";
// String url = "jdbc

racle:thin:@999.99.999.002:1524:SHARD2";
String login = "cap_user";
String password = "cap_user99";

try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e)
{
System.out.println("Couldn't load up driver!");
}
Connection theConnection = DriverManager.getConnection(url, login, password);

Statement theStatement = theConnection.createStatement();
ResultSet theResultSet = theStatement.executeQuery ("SELECT * FROM AlertNames");
while (theResultSet.next())
System.out.println(theResultSet.getString(1));
System.out.println("TESTING IS COMPLETE");
}
}
//-------------------------------------------------------------------------
Looks like the Table doesn't exist from the message but I could've sworn that he copied the one instance to the other without a problem. I'll contact him to see what's up.

I finally got it to work. I was trouble shooting with the DBA and found out that he assigned the tables to another userid. So as soon as I changed it, it worked great.
But now he wants me to use the OCI driver. I have the ocijdbc9.dll in my $ORACLE_HOME\bin\ directory. Is there any modification I'd have to make to the connect string since he has 2 boxes associated with the instance alias, SHARD?
Thanks

Ok. I got everything resolved. The Oracle DBA didn't originally setup the tables to be accessible by the user he set me up for.