Node.js Oracle API

Node.js API to connect to Oracle Database and retrieve data from views.

Features

• Express.js REST API
• Oracle Database connection using oracledb
• CORS enabled for Vue.js frontend
• Environment-based configuration
• Connection pooling for better performance

Prerequisites

• Node.js (v14 or higher)
• Oracle Instant Client installed on Windows Server
• Oracle Database access

Installation

1. Clone the repository:

git clone <your-repo-url>
cd nodejs-oracle-api

2. Install dependencies:

npm install

3. Configure environment variables:

• Copy .env.example to .env
• Update the values in .env with your Oracle database credentials:

DB_USER=your_oracle_username
DB_PASSWORD=your_oracle_password
DB_HOST=your_database_host
DB_PORT=1521
DB_SERVICE_NAME=ORCL
PORT=3000
FRONTEND_URL=http://localhost:8080

4. Update the view name in controllers/dataController.js:

• Replace YOUR_VIEW_NAME with your actual Oracle view name

Oracle Instant Client Setup (Windows Server)

1. Download Oracle Instant Client from Oracle website
2. Extract to C:\instantclient_23_0 (or your preferred location)
3. Add the path to System Environment Variables (PATH)
4. Uncomment and update the path in config/database.js:

oracledb.initOracleClient({ libDir: 'C:\\instantclient_23_0' });

Running the API

Development mode (with nodemon):

npm run dev

Production mode:

npm start

API Endpoints

• GET / - API information
• GET /api/health - Health check
• GET /api/data - Get data from Oracle view

Response Format

{
  "success": true,
  "count": 10,
  "data": [
    {
      "NAME": "Item 1",
      "STATUS": "Active"
    },
    {
      "NAME": "Item 2",
      "STATUS": "Inactive"
    }
  ]
}

Deployment on Windows Server IIS

1. Install iisnode module for IIS
2. Create a new website in IIS
3. Point the physical path to your project directory
4. Create a web.config file in the root directory
5. Configure the .env file with production values
6. Restart IIS

Project Structure

nodejs-oracle-api/
├── config/
│   └── database.js        # Database configuration
├── controllers/
│   └── dataController.js  # API controllers
├── routes/
│   └── api.js            # API routes
├── .env                  # Environment variables (not in git)
├── .env.example          # Environment variables example
├── .gitignore
├── package.json
├── server.js             # Main server file
└── README.md

Troubleshooting

Oracle Client Error

If you get "DPI-1047: Cannot locate a 64-bit Oracle Client library":

• Make sure Oracle Instant Client is installed
• Add the Instant Client path to System PATH
• Uncomment the initOracleClient line in config/database.js

Connection Error

If you get connection errors:

• Verify database credentials in .env
• Check if the database is accessible from the server
• Verify the service name is correct

License

ISC