All geek questions in one place

Connect oracle from nodejs

In the past few weeks, I have been trying to connect Oracle db from my nodejs code. So far I have found two main libraries, such as https://github.com/mariano/node-db-oracle , which is deprecated (the last update was a year ago), and the second is https://github.com/nearinfinity/ node-oracle , which is really updated, however I was not able to connect oracle with any of these modules.

Mayor issues npm install oracle // pr db-oracle fails due

../src/connection.h:10:18: fatal error: occi.h: No such file or directory

I tried to clone the code and perform a local installation, and then copy the entire module to my project, install wen well, but when I put the module in my project, I encounter this error

  module.js:340 throw err; ^ Error: Cannot find module './build/Release/oracle_bindings' at Function.Module._resolveFilename (module.js:338:15) at Function.Module._load (module.js:280:25) at Module.require (module.js:364:17) at require (module.js:380:17) at Object.<anonymous> (/var/www/node-test/node_modules/db-oracle/db-oracle.js:18:15) at Module._compile (module.js:456:26) at Object.Module._extensions..js (module.js:474:10) at Module.load (module.js:356:32) at Function.Module._load (module.js:312:12) at Module.require (module.js:364:17)

I followed the installation procedure for both drivers and was setting up variables for example (/ var / environment)

 OCI_HOME=/opt/instantclient_12_1 OCI_VERSION=12 OCI_INCLUDE_DIR=/opt/instantclient_12_1/sdk/include OCI_LIB_DIR=/opt/instantclient_12_1 LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client/lib

I used ubuntu 12.04, and node version v0.10.18 here is an example nodejs test file:

 var oracle = require('oracle'); new oracle.Database({ hostname: 'myserver.com', port: 1521, user: 'myuser', password: 'mypass', database: 'XE' }).connect(function(error) { if (error) { return console.log("CONNECTION ERROR: " + error); } this.query("select * FROM `store`").execute(function(error, rows) { if (error) { return console.log('ERROR: ' + error); } console.log(rows.length + ' ROWS'); }); });

any additional hint would have been nice. I tried noradle ( https://github.com/kaven276/noradle ), which seems like it is heavy for my purpose.

+4
source share
4 answers

I know this is an old post ... just wanted to mention the correct way to connect nodejs to oracle without additional modules.

Configure the oracle so that it can create and receive HTTP requests. There are several ways to do this:

The easiest way is to enable the epg gateway:

You can also install modplsq:

or Apex listener:

Then in node js do standard http.get:

 http.get("http://localhost/accessor/myschema.my_procedure?x=1&y=2", function(res) { console.log("Got response: " + res.statusCode); }).on('error', function(e) { console.log("Got error: " + e.message);

});

Whatever the approach ... protected oracle so that it only responds to the nodejs server ip address. Therefore, if you are working on localhost:

 if owa_util.get_cgi_env('REMOTE_ADDR') = '127.0.0.1' then --ok else -- fail end if;

Also block calls for every other package and procedure. There are several ways to do this, depending on the path you are taking.

Make sure you do this at a minimum:

  • Create a whitelist of items that you can call from the Internet.
  • require all urls to contain the schema name, for example: myuser.myprocedure
  • make sure that the first part of the URL (all the way to the request path) contains only -z 0-9
  • a really good whitelist will take care of most of these items.

There you have it ... no need to worry if the module breaks or stops working with the next version.

And ... you can easily contact Oracle before node use:

  • apex_web_service.make_rest_request
  • UTL_HTTP
+5
source

Did you get an instant client as described here ? If I read this correctly, you need the Basic (or Basic Lite) and SDK Instant Client packages. The SDK may have the header file you encountered.

Side note: in my experience, you do not always need to add Instant Client to PATH ; you can usually leave just by making sure your executable can find it (which often means just dropping it in the same directory).

0
source

Set the oracle client path on the command line as shown below before running the Node application

Set PATH = C: \ oraclexe \ app \ oracle \ instantclient_12_1;% PATH%

I had the above problem, this solved mine, and now I can connect to the oracle.

But one problem that I still run into is executing the select statement. It always returns an error. I can execute SP, create instructions and everything else without errors. I am stuck here and disappointed.

0
source

From what I understand, Set PATH places the line you specify at the beginning of the Path environment variable. I first entered the path to the client folder.

C: \ oraclexe \ application \ oracle \ instantclient_12_1;

The bug of oracle binding is fixed, but the requests did not work. Then I added the path to the vc11 folder BEFORE the client, so it looks like this:

C: \ oraclexe \ application \ oracle \ instantclient_12_1 \ VC11, C: \ oraclexe \ application \ oracle \ instantclient_12_1;

Now my queries are working!

0
source

Source: https://habr.com/ru/post/1501273/

More articles:


All Articles