DEV Community

Connect to OracleDB from NodeJS?

Brian Hanna on July 02, 2019

I currently use Java ADF REST web services to access the data in a bunch of legacy Oracle databases. It's slow, static, one dimensional, and a huge...
Read full post
Collapse
 
janguianof profile image
Jaime Anguiano •

Hi Brian,

I think the problem is not the way you are connected from NodeJS to Oracle, but rather the problem is your Oracle host (in this case your macbook or similar). It is not so easy to have an Oracle environment in macOS.

I would try doing the following:
Use Docker to build an Oracle DB locally, so you keep the DB isolated regardless of the base operating system.
Use some instance in the cloud like AWS RDS.

With this we eliminate the possibility that the problem comes from "NodeJS".

Collapse
 
thehanna profile image
Brian Hanna •

I have absolutely no control over the DB environment, unfortunately. It's an existing legacy DB that resides in an on-prem data center.

Collapse
 
janguianof profile image
Jaime Anguiano • • Edited

good...try with the embedded connection strings


oracledb.getConnection(
  {
    user          : "hr",
    password      : mypw,  // mypw contains the hr schema password
    connectString : "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mymachine.example.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))"
  }
Thread Thread
 
thehanna profile image
Brian Hanna •

I will definitely give this a try in the morning! Thanks!

Thread Thread
 
thehanna profile image
Brian Hanna •

Jaime, THANK YOU!!! Your connection string was a huge help, I finally connected successfully! I ended up having to modify it a bit to match with a value I got from a coworkers tnsnames.ora entry file for the same database, so it ended up looking like this:

(DESCRIPTION=
  (ADDRESS=
    (COMMUNITY=subdomain.company.com)
    (PROTOCOL=TCP)
    (HOST=db.company.com)
    (PORT=port_number)
  )
  (CONNECT_DATA=
    (SID=database_name)
    (GLOBAL_NAME=database_name.company.com)
    (SERVER=DEDICATED)
  )
)
Enter fullscreen mode Exit fullscreen mode
Thread Thread
 
ianbromwich profile image
Ian B •

this worked for me, thank you so much.

Collapse
 
nigilan profile image
nigilan •

Hi Brian,

Your connectString is not right. It should look like this :

connectString : 'IP_ADDRESS/service_name'

service_name could be orcl or any value present in tnsnames.ora file. The above one will work in all scenarios.

I see that you can connect through SQL Developer. There is another way to make connection, create an environment variable path called TNS_ADMIN and point it to the location of folder location of tnsnames.ora file. Now, you can just specify the entry in tnsnames.ora file in connectString.

Collapse
 
thehanna profile image
Brian Hanna •

Thanks for the suggestion! I was able to resolve it using a modified version of the connection string provided by janguianof, but this approach seems like it might be useful when moving into a production environment. Thank you!

Collapse
 
marcgilbertsparte profile image
MarcGilbertSparte •

Hello nigilan,

I've been banging my heads on the walls for a way to access my oracle db without the wallet, and your reply is the closest I've came to finding what I need.

Except whenever I try to access it I get a "ORA-12537: TNS:connection closed".

I tried a lots of things, and here is what my string look like :
"db.eu-zurich-1.oraclecloud.com/service_name.atp.oraclecloud.com"

What am I doing wrong ? Some configuration perhaps ? Any help would be greatly appreciated.

Thanks in advance,

Marc.

Collapse
 
jmcp profile image
James McPherson •

I'm glad I read through the comments before jumping in - the connect string needs to match the tnsnames.ora entry. It's a pain, but that's just how Oracle DBs do things :|

For the record, you can dive deep into the package at its homepage github.com/oracle/node-oracledb, and its maintainer is on the east coast of Australia.