Import Oracle table data from JSON file

This module offers a utility for importing Oracle tables from a JSON data file, utilizing the table list specified in the configuration file. It furnishes a straightforward and effective method for importing data from JSON files into Oracle tables.

Features

• Import table data from JSON data files

Requirements

• Node.js
• Oracle Database, Client libraries

Installation

npm install @credenceanalytics/import-json-to-table

Usage

• jsonToTable(dbConnObj, configFilePath, options)

  • dbConnObj - Oracle database connection object created using oracledb.getConnection()

  • configFilePath : List of tables to import - dataimport.sample.json

    {
    	"table": [
    		{
    			"tableName": "<table_name1>",
    			"sequences": {
    				"<COLUMN_NAME>": "<SEQUENCE_NAME>.NEXTVAL"
    			}
    		},
    		{
    			"tableName": "<table_name2>"
    		}
    	]
    }

  • options: Additional feature based option parameters.

    • exportDir - Directory path where the data folder is available which contains json files.
    • datetimeFormat - DATETIME format to import DATE & TIMESTAMP columns datatypes. Default is dd-mm-yyyy hh24:mi:ss
    • is_new_setup - To validate if the import is for new table or existing table. Default is true
      • true - Import only if the table is empty i.e. no records available.
      • false - Import even if the table contains any record.

  • Sample Code:

    const importTable = require("@credenceanalytics/import-json-to-table");
    const oracledb = require("oracledb");
    const impData = new importTable();
    const dataImpConfigFilePath = "config/dataimport.json";
    const dbconfig = {
    	username: "",
    	password: "",
    	host: "",
    	database: ""
    };
    const options = { exportDir: "./dbmodel/legacy", datetimeFormat: "dd-mm-yyyy hh24:mi:ss", is_new_setup: true };
    
    async function importJsonData() {
    	console.time("importJsonData");
    	if (!validateDbConfig(dbconfig)) return;
    	const connectString = `${dbconfig.host}/${dbconfig.database}`;
    	let dbConnObj;
    	try {
    		dbConnObj = await oracledb.getConnection({ user: dbconfig.username, password: dbconfig.password, connectString });
    		var resp = await impData.jsonToTable(dbConnObj, dataImpConfigFilePath, options);
    		console.log("Result:", resp);
    	} catch (error) {
    		console.error("Oracle DB execution exception:", error);
    		throw error;
    	} finally {
    		if (dbConnObj) {
    			try {
    				await dbConnObj.close();
    			} catch (error) {
    				console.error("Error closing connection:", error);
    				throw error;
    			}
    		}
    	}
    	console.timeEnd("importJsonData");
    }
    function validateDbConfig(config) {
    	const requiredKeys = ["username", "password", "host", "database"];
    	for (const key of requiredKeys) {
    		if (!(key in config) || config[key] === "") {
    			console.error(`Error: ${key} is missing or empty in dbconfig.`);
    			return false;
    		}
    	}
    	return true;
    }
    importJsonData();

Test

• npm test - <dbusername> <dbpassword> <dbhost> <dbname> <options>

  • Sample

  npm test - dbusername dbpassword dbhost dbname {\"exportDir\":\"test\"}

Additional Resources

• Package to export Oracle table data to JSON file : export-table-to-json