Not able to connect to Oracle from DataSource with type as JDBC
Getting the following error , when connecting to oracle DB in DataSource Step but its all good if I connect through Toad
Can't get the Connection for specified properties; java.sql.SQLException: Listener refused the connection with the following error: ORA-12505, TNS:listener does not currently know of SID given in connect descriptor
Here are the details :
DataSource :
DataSource : JDBC
Driver : oracle.jdbc.driver.OracleDriver
Connection String : jdbc:oracle:thin:cmpvauto/PASS_VALUE@mydbserver:1521:mydb
TNSNAMES.ORA
Location : c:/app/user/product/11.2.0/client_1/network/admin
Descrption :
MYDB=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=mydbserver)
(PORT=1521)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=mydb)
)
)
Toad Configuration
SOFTWARE\WOW6432NODE\ORACLE (Oracle Root)
ORACLE_HOME_NAME:
ORACLE_HOME:
ORACLE_SID:
NLS_LANG:
SQLPATH:
LOCAL:
Home directory does not exist!
Home is not valid!
SOFTWARE\WOW6432NODE\ORACLE\KEY_ORACLIENT11G_HOME1
ORACLE_HOME_NAME: OraClient11g_home1
ORACLE_HOME: C:\app\User\product\11.2.0\client_1
ORACLE_SID:
NLS_LANG: AMERICAN_AMERICA.WE8MSWIN1252
SQLPATH: C:\app\User\product\11.2.0\client_1\dbs
LOCAL:
C:\app\User\product\11.2.0\client_1\Bin exists.
C:\app\User\product\11.2.0\client_1\Bin is in PATH.
Client DLL: C:\app\User\product\11.2.0\client_1\Bin\oci.dll
Client Version: 11.2.0.1.0
Home is valid.
Ok - this is me trying to help cos of my Oracle experience, NOT SoapUI experience - Its been 4 years since I connected SoapUI to an Oracle database - I haven't got an Oracle DB to connect at moment and I haven't got the space on my harddrive to install the personal edition to check
The database you are trying to connect to doesnt use an SID, it's the Service Name that is specified in your config and the 2 are not equivalent.
SID is a single unique instance name (not the db name) whereas Service name can be an alias to multiple db instances.
you need to play with the JDBC connection string slightly - if I remember correctly the SID was separated by a colon, 'I think' the service name was separated by an oblique (/) - also the earlier part of the connection string might be a little different to the SID connection string - I just can't remember its been so long since I did this.
Easiest way to check? Try using the thin driver's connection string (rather than connecting to the db via the tnsnames.ora file) in Toad (there should be a thin driver connection option - although I can't remember if TOAD automatically picks up the tnsnames.ora file - if its local I think it does, if the file's remote it won't.
Worst case - download DBVisualiser or something like that, that definitely can use a connection string rather than the tnsnames.ora file.
Hope this helps,
Cheers
Richie and Rao , thanks for your follow-up, really appreciate your help.
Rao , I am able to connect thru Toad.
I was able to talk to my DB team and get the SID value for the DB being used and changed the connection string to use the SID value instead of the Service_name and now the connection is established
So we can consider this issue as closed now
Thanks
Pandian
