ORA-12514 error while using node oracle-db npm packagae

Question

Currently, I am working on a project which requires the backend to be done in oracle. I used the given link and installed the node-oracledb using npm on my mac. My file contents are as follows

var oracledb = require('oracledb');

oracledb.getConnection(
{
user          : 'username',
password      : 'password',
connectString : 'username/password//hostname:port/sid'
function(err, connection)
{
if (err) {
  console.error(err.message);
  return;
}else{
    connection.execute(
  "SELECT * from TableName",
  function(err, result)
  {
    if (err) { console.error(err); return; }
    console.log(result.rows);
  });
 }
});

When I run node filename.js I get the following error

ORA-12154: TNS:could not resolve the connect identifier specified

I am using node version is v7.0.0 and npm version is v3.10.8. Also my oracle database is a 11g instance on the cloud. Can somebody let me know as to what am I doing wrong?

I know this issue has been resolved but I am facing similar problem. I have follwed the direction mention in node-oracledb. I have installed instantclient basic and sdk and set their path. As soon as I install oracle 11g express I get error `oracledb module cannot be found`. When I uninstall 11g express then I get error `ORA-12541: TNS:no listener`. Can you please guide how I can install 11g and what would be my connection string? — user557657, Apr 07 '17 at 12:40

score 10 · Accepted Answer · edited Jul 05 '19 at 05:09

It looks like your connectString is wrong, according to the Docs it's just hostname:port/sid

var oracledb = require('oracledb');

oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "hostname:port/sid"
  })
  .then(function(conn) {
    return conn.execute(
      "SELECT department_id, department_name " +
        "FROM departments " +
        "WHERE manager_id < :id",
      [110]  // bind value for :id
    )
      .then(function(result) {
        console.log(result.rows);
        return conn.close();
      })
      .catch(function(err) {
        console.error(err);
        return conn.close();
      });
  })
  .catch(function(err) {
    console.error(err);
  });

Edit:

As of atleast July 2019 (probably sometime before July) connectString : "hostname:port/sid" no longer works with oracledb printing the error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor Instead, as found here you can to set connectString to (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = YOUR_HOST)(PORT = YOUR_PORT))(CONNECT_DATA =(SID= YOUR_SID))) to connect to a database using SID.

So, the updated getConnection (in regards to the question) would be:

oracledb.getConnection(
 {
   user          : "hr",
   password      : "welcome",
   connectString : "(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = port))(CONNECT_DATA =(SID= sid)))"
 })

Technically the bit of the connection string after the slash is a 'service name', not a SID. The full Easy Connect syntax is given here: http://docs.oracle.com/database/122/NETAG/configuring-naming-methods.htm#NETAG255 Using a service name lets Oracle Network do more magic in connecting the app to a running instance. Service names were introduced long ago (version 8?) and have become standard. — Christopher Jones, Jan 17 '17 at 01:31
I know this issue has been resolved but I am facing similar problem. I have follwed the direction mention in node-oracledb. I have installed instantclient basic and sdk and set their path. As soon as I install oracle 11g express I get error oracledb module cannot be found. When I uninstall 11g express then I get error ORA-12541: TNS:no listener. Can you please guide how I can install 11g and what would be my connection string? — user557657, Apr 07 '17 at 13:47

ORA-12514 error while using node oracle-db npm packagae

1 Answers1