cancel
Showing results for 
Search instead for 
Did you mean: 

Not able to connect to Oracle from DataSource with type as JDBC

SOLVED
Highlighted
Occasional Contributor

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.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Hero

Re: Not able to connect to Oracle from DataSource with type as JDBC

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

if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

Highlighted
Occasional Contributor

Re: Not able to connect to Oracle from DataSource with type as JDBC

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

View solution in original post

3 REPLIES 3
Highlighted
Community Hero

Re: Not able to connect to Oracle from DataSource with type as JDBC

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

if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

Highlighted
Community Hero

Re: Not able to connect to Oracle from DataSource with type as JDBC

Are you able to connect it thru sqlplus commandline? It appears that listener is not registered.


Regards,
Rao.
Highlighted
Occasional Contributor

Re: Not able to connect to Oracle from DataSource with type as JDBC

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

View solution in original post

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors