Export Oracle table data to JSON file

This module offers a utility for extracting table data in JSON format, using the table list specified in the configuration file. It provides a straightforward and efficient method for exporting data from Oracle tables to JSON files.

Features

• Export table data as JSON in .json files
• Export table data as INSERT STATEMENTS in .sql files

Requirements

• Node.js
• Oracle Database, Client libraries

Installation

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

Usage

• dataToJsonFile(dbConnObj, configFilePath, options)

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

  • configFilePath : Export of table list - dataexport.sample.json

    {
    	"table": [
    		{
    			"tableName": "<table_name1>",
    			"filterCondition": "< where condition e.g. col1='Y' >"
    		},
    		{
    			"tableName": "<table_name2>",
    			"filterCondition": ""
    		}
    	]
    }

  • options: Additional feature based option parameters.

    • exportDir - Directory path where the data folder will be created to export table data in .json or .sql files. Default is ./
    • datetimeFormat - DATETIME format to export DATE & TIMESTAMP columns datatypes. Default is dd-mm-yyyy hh24:mi:ss
    • isSQL - To generate insert statements as .sql files. Default is false
      • true - Export in .json & .sql files
      • false - Export only in .json files

  • Sample Code:

    const exportTable = require("@credenceanalytics/export-table-to-json");
    const oracledb = require("oracledb");
    const expData = new exportTable();
    const dataExpConfigFilePath = "config/dataexport.json";
    const dbconfig = {
    	username: "",
    	password: "",
    	host: "",
    	database: ""
    };
    const options = { exportDir: "./dbmodel/legacy", datetimeFormat: "dd-mm-yyyy hh24:mi:ss", isSQL: true };
    
    async function exportJsonData() {
    	console.time("exportJsonData");
    	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 expData.dataToJsonFile(dbConnObj, dataExpConfigFilePath, 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("exportJsonData");
    }
    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;
    }
    exportJsonData();

• Note:

  • Column type are not supported.
    • CLOB
    • BLOB

Test

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

  • Sample

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

Additional Resources

• Package to import Oracle table data from JSON file : import-json-to-table