Node-oracledb: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

[dotmaik1]

Hi

At the documentation said that we can connect with the name of the instance, can you clarify an example to do so please?

Documented:
[//]host_name[:port][/service_name][:server_type][/instance_name]

I only get this errors

Errors:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

[cjbj]

There are examples of connection strings in the node-oracledb doc, e.g. "localhost/orcl"

How do other users or apps connect to your database?

What's the exact string you are using?

[Updated URL to current production doc]

[dotmaik1]

This is the connection string that I'm using but its not working with the instance name

dbserver01.customdba.com:1521/prod1 <- It fails (ORA-12514: TNS:listener does not currently know of service requested in connect descriptor)

This is the instance name

SQL> select instance_name from v$instance;

INSTANCE_NAME

prod1

The only way that I'm able to connect is by using the service name

dbserver01.customdba.com:1521/prod.customdba.com <- This works

but I need to do it with the instance name preferably

[kubo]

Documented:
[//]host_name[:port][/service_name][:server_type][/instance_name]

The documented format is ambiguous. foo/bar is parsed as two ways: host_name/service_name and host_name/instance_name.

According to examples in Oracle manual, foo/bar is parsed as host_name/service_name. If bar is instance_name, it should be foo//bar.

The format should be:

[//]host_name[:port][/[service_name][:server_type][/instance_name]]

[cjbj]

I've asked the Oracle Net team to clarify the syntax.

[dotmaik1]

When I try connecting with the instance name with that sintax it fails too

foo//bar

dbserver01.customdba.com//prod1
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

I've tried with diferent Oracle clients 11.2.0.4.0 and 12.1.0.2.0 and there is no difference

[dotmaik1]

Any update from the Oracle Net team?

[cjbj]

The update from the Net team is that service_name is required. This is true for the Easy Connect syntax and full connect strings. I'll work with them to improve their Easy Connect syntax & doc (on which node-oracledb doc is based)

[dotmaik1]

I workaround this by adding the full connection strings on the application

var oracledb = require('oracledb');

oracledb.getConnection(
{
user : "system",
password : "*",
connectString : "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver01.customdba.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=prod1)))"
},

It just worked like that

Regards

[jb92130]

Hello dotmaik1,

I'm using this plugin for my DevOps position and I got the same problem like you. I could solve it with your comment. I think, cjbj, you could update your documentation with this solution 👍 .

Kind regards,

JB.

[cjbj]

@jb92130 if you have a patch, please sign the OCA and create a PR.

Note that SIDs were replaced by Service Names many, many releases ago. Maybe I could convince you to upgrade?

[surjikal]

@dotmaik1 Awesome, that solved my problem as well. Thank you!

[crac7]

@dotmaik1 Thank you !! :)

[douniaogle]

@dotmaik1 thanks! this solved my issue.

[jhomarolo]

@dotmaik1 thanks!

[cjbj]

For the record, the current doc on Connection Strings is here, in particular for this issue see the subsection Net Service Names for Connection Strings. I've updated the link in my previous comment.

And stop using SIDs everyone! Please use service names so you get access to all the goodness. :)

[kyourselfer]

Don't forget look at logs:
grep SERVICE_NAME= /u01/app/oracle/diag/tnslsnr/$hostname/listener/alert/log.xml

[henriquesqs]

Oh my God, thank you a lot @dotmaik1. It's 2020 and you helped me. Was searching for a fix for 2 days. Thank you so much!!!!