从 nodejs 连接 oracle

2021-12-30 00:00:00 sql node.js oracle11g oracle

过去几周我一直试图从我的 nodejs 代码连接 oracle db.到目前为止，我发现了 2 个主要库，例如 https://github.com/mariano/node-db-oracle 已过时(上次更新是一年前)，第二个是 https://github.com/nearinfinity/node-oracle 这确实是最新的，但是我没有设法将 oracle 与任何这些模块连接起来.

Last few weeks I had been trying to connect oracle db from my nodejs code. What I found so far are 2 main libraries such as https://github.com/mariano/node-db-oracle which is out of date (last update was year ago) and second one is https://github.com/nearinfinity/node-oracle which is really up to date, however I didn't manage to connect oracle with any of those modules.

由于

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

我试图克隆代码并执行本地安装，然后将整个模块复制到我的项目下，安装好，但是当我将模块放在我的项目下时，我遇到了这个错误

I tried to clone the code and perform local install and then copy entire module under my project, install wen well, but when I place module under my project I run into 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)

我一直在遵循两个驱动程序的安装程序并设置了变量像这样(/var/environment)

I had been follow installation procedure for both drivers and had been setup variables like this (/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

我用的是ubuntu 12.04，node版本是v0.10.18这是我的示例 nodejs 测试文件:

I used ubuntu 12.04, and node version is v0.10.18 here is my sample 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');
    });
});

任何额外的提示都会很好.我试过 noradle (https://github.com/kaven276/noradle) 似乎很重为了我的目的.

any additional hint would be nice. I tried noradle (https://github.com/kaven276/noradle) that one seems to be to heavy for my purpose.

推荐答案

我知道这是一篇旧帖子...只是想提一下 nodejs 在没有额外模块的情况下与 oracle 通信的可靠方法.

I know this is an old post... just wanted to mention a sure way for nodejs to communicate to oracle without extra modules.

设置 oracle 以便它可以创建和接收 http 请求.有几种方法可以做到这一点:

Set up oracle so it can create and receive http requests. There are a few ways to do this:

最简单的就是开启epg网关:

The easiest is to turn on the epg gateway:

  • http://www.oracle-base.com/articles/10g/dbms_epg_10gR2.php

你也可以设置 modplsq:

Also you can setup modplsq:

  • http://www.comp.dit.即/btierney/oracle11gdoc/appdev.111/b28424/adfns_web.htm

或 Apex 侦听器:

or the Apex listener:

  • http://www.oracle.com/technetwork/developer-tools/apex-listener/overview/index.html?ssSourceSiteId=otnpt

然后在 node js 中做一个标准的 http.get:

Then in node js do a 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);

});

无论采用哪种方法...保护oracle，使其仅响应nodejs服务器的IP地址.所以如果在本地主机上运行:

Whichever approach...secure oracle so that it will only respond to the ip address of the nodejs server. So if running on localhost: 

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

还阻止对其他所有包和过程的调用.根据您采取的路径，有几种方法可以做到这一点.

Also block calls to every other package and procedure. There are few ways to do this depending on the path you take.

确保您至少执行此操作:

Make sure you do this at a minimum:

  • 创建可从网络调用的项目白名单
  • 要求所有 url 都包含架构名称，例如:myuser.myprocedure
  • 确保 url 的第一部分(直到查询路径)只包含 a-z 0-9
  • 真正好的白名单将处理大部分这些项目

你有它......无需担心模块是否会在下一个版本中损坏或停止工作.

There you have it...no need to worry if a module will break or stop working with the next release.

并且...您可以轻松地从 Oracle 通信到 Node 使用:

AND...you can easily communicate from Oracle to Node use:

  • apex_web_service.make_rest_request
  • utl_http

相关文章