• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Migrating from Oracle 8 to Oracle 9i JDBC Connection Problems

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 411
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you tried using the non-thin link - 'oci'?
Regards.
Mickey Joe
 
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I've experience nothing but problems


would that include a stack trace?
 
Robert Baggett
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Robert Baggett
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
//------------------------------------------------------------------
 
Robert Baggett
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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)))
 
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Robert Baggett
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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)
 
Wayne L Johnson
Ranch Hand
Posts: 399
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 55
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Robert Baggett
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for all the responses. I'll try the ping solution when I get back into work first thing in the morning.
Thanks,
Robert
 
Robert Baggett
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Robert Baggett
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Robert Baggett
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Forget this weirdo. You guys wanna see something really neat? I just have to take off my shoe .... (hint: it's a tiny ad)
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic