Docker部署Node.js应用连接远程OracleDB遇ORA-12504错误求助

问题现象 #

将Node.js应用部署到Docker容器后，连接远程Oracle数据库时出现两个错误：

1. ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
2. connection is not defined

无法确定错误源于Docker配置还是数据库服务器，已尝试多个在线方案未解决，相关代码与操作命令如下：

dbConfig.js #

module.exports = {
user          : process.env.NODE_ORACLEDB_USER,
password      : process.env.NODE_ORACLEDB_PASSWORD,
connectString : process.env.NODE_ORACLEDB_CONNECTIONSTRING,
poolMax: 2,
poolMin: 2,
poolIncrement: 0
};

Dockerfile #

# bring latest node in alpine linux image
FROM oraclelinux:7-slim
# app directory
WORKDIR /app
#copy pacakge*.json files
COPY package*.json ./

# Update Oracle Linux
# Install Node.js
# Install the Oracle Instant Client
# Check that Node.js and NPM installed correctly
# Install the OracleDB driver
RUN yum update -y && \
yum install -y oracle-release-el7 && \
yum install -y oracle-nodejs-release-el7 && \
yum install -y nodejs && \
yum install -y oracle-instantclient19.3-basic.x86_64 && \
yum clean all && \
node --version && \
npm --version && \
npm install && \
echo Installed

# Copy project
COPY . .

EXPOSE 9090

CMD ["node", "index.js"]

index.js #

const express = require('express');
const oracledb = require('oracledb');
const dbConfig = require('./dbConfig.js');
const app = express();

oracledb.outFormat = oracledb.OUT_FORMAT_OBJECT;
oracledb.autoCommit = true;

async function getData(req, res) {
    try {
        connection = await oracledb.getConnection(dbConfig);
        data = await connection.execute(`SELECT * FROM DEPARTMENT`)
    } catch (error) {
        return res.send(error.message)
    } finally {
        try {
            await connection.close();
            console.log('Connection closed');
        } catch (error) {
            console.error(error.message)
        }
    }
    if (data.rows.length == 0) {
        return res.send('No data found');
    } else {
        return res.status(200).json({
            data: data.rows, 
        })
    }
}

app.get('/', function(req, res) {
    getData(req, res);
})

环境变量配置 #

export NODE_ORACLEDB_USER=NotMyRealUser
export NODE_ORACLEDB_PASSWORD=NotMyRealPassword
export NODE_ORACLEDB_CONNECTIONSTRING=hostname:1521/servicename

注：hostname为数据库服务器别名

Docker构建命令 #

docker build --no-cache --force-rm=true -t node_oracle_app .

Docker运行命令 #

docker run -it -p 9090:9090 -e NODE_ORACLEDB_USER=$NODE_ORACLEDB_USER -e NODE_ORACLEDB_PASSWORD=$NODE_ORACLEDB_PASSWORD -e NODE_ORACLEDB_CONNECTIONSTRING=$NODE_ORACLEDB_CONNECTIONSTRING ade_openlot3_server_oracle

解决方案 #

1. 修复ORA-12504错误 #

该错误由连接字符串或网络问题导致，按以下步骤排查：

• 验证网络连通性：进入容器执行ping hostname和telnet hostname 1521，确认数据库服务器地址可解析、端口开放。如果是自定义别名，可直接替换为数据库服务器IP地址测试。
• 确认连接字符串格式：当前使用的hostname:1521/servicename格式需要确保servicename是数据库的真实服务名（而非SID）。若不确定，改用TNS格式连接字符串：

  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=servicename)))
  

2. 修复connection is not defined错误 #

原代码中connection和data未声明为局部变量，连接失败时connection未初始化，进入finally块会报错。修改代码如下：

async function getData(req, res) {
    let connection;
    let data;
    try {
        connection = await oracledb.getConnection(dbConfig);
        data = await connection.execute(`SELECT * FROM DEPARTMENT`)
    } catch (error) {
        return res.send(error.message)
    } finally {
        // 先判断connection是否存在再执行关闭操作
        if (connection) {
            try {
                await connection.close();
                console.log('Connection closed');
            } catch (error) {
                console.error(error.message)
            }
        }
    }
    // 可选：添加可选链避免data未定义报错
    if (data?.rows.length == 0) {
        return res.send('No data found');
    } else {
        return res.status(200).json({
            data: data.rows, 
        })
    }
}

3. 修正Docker运行命令的镜像名 #

构建命令生成的镜像名是node_oracle_app，但运行命令中使用的是ade_openlot3_server_oracle，两者需保持一致，否则会出现镜像找不到的问题。

内容的提问来源于stack exchange，提问作者Boomerang20thCentury