cancel
Showing results for 
Search instead for 
Did you mean: 

How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

SOLVED
gmathai
New Contributor

How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

There is a requirement for me to connect to an Oracle thin DB which is identified by 'Service Name' instead of SID.

Our DBA advised that SID is a thing of the past and they’re using Service Name now as best practice and for operational reasons.

But from SOAP UI Pro i dont see an option to enter Service Name in DB configurations, whereas SID is shown instead.

 

Please advice the steps to achieve this.

1 ACCEPTED SOLUTION

Accepted Solutions
rwilson352
Occasional Contributor

Re: How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

Further digging, I found this article: https://community.smartbear.com/t5/SoapUI-Pro/Connection-to-an-Oracle-database-without-CID-but-with/...

 

The solution is to manually edit either the driver or the connection string. See the accepted answer in the linked article. I was able to achieve it.

 

View solution in original post

12 REPLIES 12
Terkon
Occasional Contributor

Re: How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

Hi,

 

I am using the same setup. I simply put the service name into the SID field - workes fine for me.

 

With that I am using java class "ojdbc6.jar" in c:\<yourInstallationFolder>\ReadyAPI-2.7.0\bin\ext

Attached you'll find my settings. The "1" in the service name is for the cluster node I want to connect to. In a cluster environmend that is necessary to give.

 

Hope this helps

Terkon

 

TanyaYatskovska
Community Manager

Re: How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

Thanks for your suggestion, @Terkon!

 

@gmathai, were you able to resolve the issue?

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
gmathai
New Contributor

Re: How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

@TanyaYatskovska / @Terkon 

After placing the ojdbc6.jar in the specified location, i am getting the attached error.New Error.jpg

nmrao
Community Hero

Re: How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

Try checking with sqlplus or other client software to connect to the db and see if that is working.


Regards,
Rao.
gmathai
New Contributor

Re: How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

@nmrao We are able to connect to this DB using Oracle SQL developer. Issue faced from SOAPUI though.

Terkon
Occasional Contributor

Re: How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

Ok, I think that was a step in the right direction. Unfortunately this Error (Ora-12505) can have quite a lot of reasons.

Isn't there a DB admin you can turn to?

 

Check out this:

https://chartio.com/resources/tutorials/how-to-fix-ora-12505-tns-listener-does-not-currently-know-of...

 

Or maybe google some other sites with hints on that problem.

 

What does the TNS look like, which you use for the DB developer? And how the connection string in that tool?

Are you working on a cluster environment or is it a single instance DB?

Maybe you have to adjust your connection string in SOAPui or adjust the TNS so they fit to each other

 

TanyaYatskovska
Community Manager

Re: How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

Thanks for your help, Community!

@gmathai, have you managed to find a solution. Please share it with us - it may help someone else.

---------
Tanya Yatskovskaya
SmartBear Community and Education Manager

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
rwilson352
Occasional Contributor

Re: How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

My intermediate-level knowledge of JDBC leads me to the conclusion that, unless Ready API specifically has a capability to add "Service Name" instead of SID, that this will never be possible. This StackOverflow question/answer demonstrates that the JDBC connection string will need to be of a different structure if one expects to use Service Name in lieu of SID: https://stackoverflow.com/questions/4832056/java-jdbc-how-to-connect-to-oracle-using-service-name-in...

 

Many database clients have capability for Service Name / SID switching. Here are some examples:

 

SQL Developer

sqldeveloper service name.png

 

DBeaver

dbeaver service name.png

 

So I think the Ready API developers need to consider adding the option of Service Name-based JDBC functionality.

 

rwilson352
Occasional Contributor

Re: How to connect to an Oracle Thin DB which is identified by 'Service Name' instead of SID

To be clearer: the change that needs to be made is that ReadyAPI needs to allow connection strings to be in the following format:

 

jdbc:oracle:thin:user/PASS_VALUE@xyz.int.com:1521/DBSCHEMA

currently, it only allows this (as far as I can tell):

jdbc:oracle:thin:user/PASS_VALUE@xyz.int.com:1521:DBSCHEMA

The difference: the Service Name takes a / between it and the port, not a :

 

Another related SO Q&A:

https://stackoverflow.com/questions/18192521/ora-12505-tnslistener-does-not-currently-know-of-sid-gi...

 

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