• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Oracle Connection error. ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (6)

Resolved Oracle Connection error. ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

  1. Issue
0
Votes
Undo
  1. Babu Ambalavanan
    Babu Ambalavanan
  2. PowerBuilder
  3. Tuesday, 8 October 2024 19:03 PM UTC

I am trying to migrate a PowerBuilder application built many years ago to the new 2022 R3 version. I created a connection profile for connecting the application to the Oracle database but I get the error "ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA".

I have placed a TNSNames.ora file with the SERVICE_NAME in CONNECT_DATA parameter defined.

My questions are

1) Why is PowerBuilder not recognizing this tnsnames.ora file?

2) Should the TNSNames.ora file be placed in a specific folder? 

3) Is there any other way to pass this SERVICE_NAME in CONNECT_DATA information when trying to connect from PowerBuilder to Oracle?

Thanks to anyone who can help with this. 

PDFDocument to BLOB?
PB Project with mutiple targets
Accepted Answer
Peter Pang @Appeon
Peter Pang @Appeon Accepted Answer Pending Moderation
  1. Thursday, 10 October 2024 03:32 AM UTC
  2. PowerBuilder
  3. # Permalink

Hi Babu，

What’s your Oracle client version? Is the path in the system environment Path?
Please take a screenshot of the SQL Dev's successful connection information for me to check.


Also, you can install Instant client (Windows 32-bit, Version 19.24.0.0.0) ：
https://www.oracle.com/database/technologies/instant-client/downloads.html

Please follow the configuration below and see if the problem can be solved:
DB Driver: Oracle 19c x86 (C:\instantclient_19_24)
System path: C:\instantclient_19_24

Database Connection Syntax：
// Profile test19c
SQLCA.DBMS = "ORA Oracle"
SQLCA.LogPass ="******"
SQLCA.ServerName = "172.16.1.1/PDBORCL" // Note that the IP is followed by the service name
SQLCA.LogId = "appeon"
SQLCA.AutoCommit = False
SQLCA.DBParm = ""

Best Regards,
Peter

Comment
Babu Ambalavanan
  1. Babu Ambalavanan
  2. Thursday, 10 October 2024 13:43 PM UTC
Peter, thanks for the reply. I will try these steps and report progress.
  1. Helpful
Babu Ambalavanan
  1. Babu Ambalavanan
  2. Thursday, 10 October 2024 14:46 PM UTC
Peter, your reply helped me to connect to Oracle from PB Ver 2022 R3. I will write a detailed reply for the reference of forum users who encounter this issue. Thanks so much for your help.
  1. Helpful
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Friday, 11 October 2024 14:53 PM UTC
Thanks Babu for taking the time to write the detailed solution to help other people!
  1. Helpful 1
There are no comments made yet.
Responses (6)
Peter Pang @Appeon
Peter Pang @Appeon Accepted Answer Pending Moderation
  1. Friday, 8 November 2024 05:47 AM UTC
  2. PowerBuilder
  3. # 1

Hi Babu，
We are glad to inform you that PowerBuilder 2025R1 Beta2 (Build 3453) is ready for your test and evaluation. The bug or enhancement you reported in this ticket has been fixed or implemented in this release. Please download and install it to verify it on your side and let us know your feedback soon!

You can get the installer from our Downloads portal at https://account.appeon.com/download/beta (login required).

We welcome all feedback from you!

Note:
This beta release is for your test and evaluation only and it's not ready for production use.
We recommend using a virtual machine for testing to avoid any interference with your development environment.


Regards,
Peter

Comment
There are no comments made yet.
Babu Ambalavanan
Babu Ambalavanan Accepted Answer Pending Moderation
  1. Thursday, 10 October 2024 19:32 PM UTC
  2. PowerBuilder
  3. # 2

Peter, your reply helped me. 

The summary of the issue and the resolution is as follows: 

  • To connect to Oracle 12 from PB Ver 2022 R3.
  • I had legacy source code written using PowerBuilder Version 11
  • I needed to migrate the source code to Version 2022 R3
  • While regenerating the PowerBuilder code for a full build, the database connection is required
  • The only way I could specify the connection parameters is within the DB Profile
  • There are fields in the DB Profile painter, to input the Oracle Login and Oracle Password and Servername
  • There is no field that specify where to enter the Oracle Service name.
  • So the Oracle Listener was returning the error "ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA".
  • This error was resolved by using Peter's advice of appending /Servicename to the Server name string. For example if your server name is xxxservername and service name is sssservicename, then in the field to input the servername, enter xxxservername/sssservicename.
  • This solved the error.
Comment
There are no comments made yet.
Babu Ambalavanan
Babu Ambalavanan Accepted Answer Pending Moderation
  1. Wednesday, 9 October 2024 17:58 PM UTC
  2. PowerBuilder
  3. # 3

Chris, I am using 32 bit Oracle client on a 64 bit windows laptop. I do not have the PowerBuilder source code migrated and am only connecting from within the PowerBuilder Application's IDE using the DB Profile. There is no other parameter within the DB Profile except a servername, a database login, a database password. I am not able to pass the Service_Name to the Oracle Listener. TNSNAmes.ora is placed at the right location %Oracle_Home\\network\admin\.  Any advice is valuable and will help our organization move forward with this project. Thanks in advance.

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Wednesday, 9 October 2024 19:32 PM UTC
Hi Babu;

That is great as the PB IDE is currently only 32 bit. So your Oracle Client connectivity software must also be 32 bit.

Note though that PB can create 64 bit Apps. Thus for these, you will need the 64 bit Oracle Client installed.

Personally, I always install the Oracle "Instant Client" both bitness on my test machines for both 32/64 bit support.

Food for thought.

Regards .. Chris
  1. Helpful 1
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 9 October 2024 00:55 AM UTC
  2. PowerBuilder
  3. # 4

Hi Babu;

  PB is not responsible for locating & using the TNSNames.ora file. The PB Oracle DB interface tries to load the Oracle client (ORA.dll). Its this Oracle supplied DLL that will then try to locate the TNSNames file and look up the "Service name". From there, the TNS file's service name maps to a DNS / IP address & port where it expects the Oracle DBMS server to be listening on. If all that lines up, an Oracle connection is then made on the "Connect;" PowerScript verb.  For the Location and values to put in your TNS file, please see your organization's Oracle DBA team.

Regards .. Chris

Comment
Babu Ambalavanan
  1. Babu Ambalavanan
  2. Wednesday, 9 October 2024 12:22 PM UTC
Thanks Chris. I will work with the Oracle group and report progress.
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Tuesday, 8 October 2024 22:33 PM UTC
  2. PowerBuilder
  3. # 5

Which Oracle client are you using (and also 32 or 64 bits)?

What are all the values of your connection profile?

You might have to change something in your listener.ora file. Just do a google search on "ora-12504" .

 

Comment
Load more comments
John Fauss
  1. John Fauss
  2. Wednesday, 9 October 2024 15:15 PM UTC
FWIW, I share your sentiment to a large degree, Miguel. All too often, people will ask a question but omit key pieces of information that are needed in order to provide suggested, helpful remedies... and when you respond to ask for additional information, too frequently they do not reply. This can be very discouraging at times. Try to not let it get you down -- I greatly appreciate your efforts to help Community members, as many others here do also!
  1. Helpful 1
Babu Ambalavanan
  1. Babu Ambalavanan
  2. Wednesday, 9 October 2024 17:29 PM UTC
Miguel, I appreciate your response. I have not been able to respond to you due to work. I will reply this thread as soon as I am able to.
  1. Helpful
Babu Ambalavanan
  1. Babu Ambalavanan
  2. Wednesday, 9 October 2024 17:34 PM UTC
Miguel, I am using 32 bit Oracle client on a 64 bit windows laptop. I do not have the source code migrated and only connecting from within the PowerBuilder Application's IDE using the DB Profile. I am not able to pass the Service_Name to the Oracle Listener. Your advice is valuable. Thanks.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.