Home » Other » Client Tools » Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID (Oracle 11.2.0.1 Database)
Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID [message #577916] Thu, 21 February 2013 14:46 Go to next message
wmcrews
Messages: 6
Registered: February 2013
Junior Member
I wonder if anyone can help out. The problem is that this connection errors when I try to connect using my SQuirreL client. My developers want to connecting using the SID or the SERVICE_NAME and by using the "New Method" syntax, not the "Old Method" syntax. According to the documentation the "New Method" syntax works with the SERVICE_NAME or the SID. The "Old Method" only works with the SID.

# Host name is vmwwin7634.na.SAS.com
# SID is sting2
# SERVICE_NAME is sting2.na.sas.com

PROBLEM using SID errors ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2
THIS WORKS using SERVICE_NAME ==> jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com

I am using SQuirreL Client version 3.4.0

# Here is the tnsnames.ora entry on the server.
STING2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vmwwin7634.na.SAS.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = sting2.na.sas.com)
)
)

# In this documentation it says, "On new syntax SERVICE may be a oracle service name or a SID."
You can find this on the orafax wiki under JDBC#Thin_driver.

# I was able to connect with the "old method" using the SID only, SERVICE_NAME errors
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2.na.sas.com
(SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")

jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2 (this works)

# "New Method"
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com (this works)

jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2 (this errors)
(SQuirreL client error, "Unexpected Error occurred attempting to open an SQL connection.")
Re: Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID [message #577918 is a reply to message #577916] Thu, 21 February 2013 15:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The problem is that "Unexpected Error occurred attempting to open an SQL connection." is not an Oracle error and without this one we can't help you.
Maybe you will get a faster answer posting in a Squirrel forum.

Regards
Michel
Re: Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID [message #577919 is a reply to message #577916] Thu, 21 February 2013 15:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what is OS name & version for the DB Server system?
Re: Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID [message #577920 is a reply to message #577916] Thu, 21 February 2013 15:25 Go to previous messageGo to next message
wmcrews
Messages: 6
Registered: February 2013
Junior Member
After reading the documentation more closely, I believe the "old method" syntax works with the SID and the "new method" syntax works with the SERVICE_NAME.
The documentation is a bit confusing saying the "new method" works with both SERVICE and SID.

These connections both work:
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521:sting2
jdbc:oracle:thin:@vmwwin7634.na.SAS.com:1521/sting2.na.sas.com
Re: Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID [message #577921 is a reply to message #577920] Thu, 21 February 2013 15:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post results from follow OS commands run on the DB Server system

lsnrctl status
lsnrctl service

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID [message #577923 is a reply to message #577921] Thu, 21 February 2013 16:07 Go to previous messageGo to next message
wmcrews
Messages: 6
Registered: February 2013
Junior Member
C:\oracle\product\11.2.0\dbhome_1\BIN>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 21-FEB-2013 17:05
:31

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date 08-FEB-2013 21:55:23
Uptime 12 days 19 hr. 10 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\oracle\product\11.2.0\dbhome_1\network\admin\listen
er.ora
Listener Log File c:\oracle\diag\tnslsnr\vmwwin7634\listener\alert\log.x
ml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vmwwin7634.na.SAS.com)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "sting2.na.sas.com" has 1 instance(s).
Instance "sting2", status READY, has 1 handler(s) for this service...
Service "sting2XDB.na.sas.com" has 1 instance(s).
Instance "sting2", status READY, has 1 handler(s) for this service...
Service "stingray.na.SAS.com" has 1 instance(s).
Instance "stingray", status READY, has 1 handler(s) for this service...
Service "stingrayXDB.na.SAS.com" has 1 instance(s).
Instance "stingray", status READY, has 1 handler(s) for this service...
The command completed successfully

C:\oracle\product\11.2.0\dbhome_1\BIN>lsnrctl service

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 21-FEB-2013 17:05
:48

Copyright (c) 1991, 2010, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "sting2.na.sas.com" has 1 instance(s).
Instance "sting2", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:13309 refused:0 state:ready
LOCAL SERVER
Service "sting2XDB.na.sas.com" has 1 instance(s).
Instance "sting2", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: VMWWIN7634, pid: 1332>
(ADDRESS=(PROTOCOL=tcp)(HOST=vmwwin7634.na.SAS.com)(PORT=64530))
Service "stingray.na.SAS.com" has 1 instance(s).
Instance "stingray", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:16052 refused:0 state:ready
LOCAL SERVER
Service "stingrayXDB.na.SAS.com" has 1 instance(s).
Instance "stingray", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: VMWWIN7634, pid: 2988>
(ADDRESS=(PROTOCOL=tcp)(HOST=vmwwin7634.na.SAS.com)(PORT=63922))
The command completed successfully
Re: Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID [message #577924 is a reply to message #577919] Thu, 21 February 2013 16:09 Go to previous messageGo to next message
wmcrews
Messages: 6
Registered: February 2013
Junior Member
Windows Server 2008 R2 Enterprise SP1
Re: Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID [message #577925 is a reply to message #577919] Thu, 21 February 2013 16:10 Go to previous messageGo to next message
wmcrews
Messages: 6
Registered: February 2013
Junior Member
Sorry, not Windows 2008, Windows 7 Enterprise SP1
Re: Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID [message #577926 is a reply to message #577925] Thu, 21 February 2013 16:11 Go to previous messageGo to next message
wmcrews
Messages: 6
Registered: February 2013
Junior Member
Here is the link to the documentation: http://www.orafaq.com/wiki/JDBC#Thin_driver
Re: Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID [message #577927 is a reply to message #577926] Thu, 21 February 2013 17:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
When reality & documentation conflict, consider that the documentation is not correct.
I am tempted to change the WIKI entry to at warn using the "new" format might not succeed specifying the SID
Re: Cannot Connect using JDBC Oracle Thin Client with "New Method" Using SID [message #577957 is a reply to message #577927] Fri, 22 February 2013 01:34 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The page is correct but just a bit misleading in its example with ORCL, as in the default Oracle database the instance name and the service name are equal (the same thing is also true with XE database).
I modified the page to make the example clearer.

Regards
Michel
Previous Topic: Out of memory using DDL export in Toad
Next Topic: Where can i download SQL*Plus?
Goto Forum:
  


Current Time: Fri Mar 29 03:11:54 CDT 2024