1.0.4 • Published 4 months ago
@credenceanalytics/import-oracledb-schema v1.0.4
Import of Oracle Schema to database
This Node.js module imports Oracle schema script files to Oracle database.
Introduction
This module provides a utility to import JSON structure tables & other DDL object types using the provided scripts files.
Import Features
- Create table structure using JSON schema files
- Add/Modify table columns by comparing JSON schema files & column definition in oracle database
- Create/Replace other object types like view, function etc. to Oracle database
- Selective import of objects
- Import of all objects from the schema definition folder
- Recompile invalid database objects like procedures, functions etc.
Requirements
- Node.js
- Oracle Database, Client libraries
- Scheme JSON/Script files exported using NPM Module - oracledb-to-json-schema
Installation
npm install @credenceanalytics/import-oracledb-schema
Usage
schemaToDb(dbConnObj, options, configFilePath)
dbConnObj
- Oracle database connection object created using oracledb.getConnection()options
: Additional feature based option parameters.- exportDir - Path where the
definition
folder is available which contains oracledb schema. - sql_output - To generate insert statements as .sql files. Default is false
- true - Save import script as sql files
- false - Import script without saving the script in sql files.
- is_new_setup - To validate if the import is for new schema or existing schema. Default is true
- true - Import only if the schema is blank i.e. no object available. Fresh Setup Installation.
- false - Import even if schema contains any object. It will overwrite or update the existing objects as per the import schema.
- exportDir - Path where the
configFilePath
: Optional- Default: Import of all objects from the schema definition folder
- Provide list of selective schema objects to import - schemaimport.sample.json
{ "procedure": ["APP_REFRESH_DATA", "WM_LOGPROCESSDETAIL"], "view": ["AUDIT_BANKACCOUNTS", "BANK_VIEW"], "table": ["GLOBAL_SECURITY", "WORKFLOW_SECURITYDEF"] }
Options:
- Import Schema of all objects
schemaToDb(dbConnObj, options);
- Import Schema of selective objects
schemaToDb(dbConnObj, options, schemaImpConfigFilePath);
Sample code to import full schema:
index.js
const importSchema = require("@credenceanalytics/import-oracledb-schema"); const oracledb = require("oracledb"); const impSchema = new importSchema(); const dbconfig = { username: "", password: "", host: "", database: "" }; const options = { exportDir: "./dbmodel/legacy", sql_output: true, is_new_setup: true }; async function importAllSchemaToDb() { console.time("importAllSchemaToDb"); 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 impSchema.schemaToDb(dbConnObj, options); console.log("Result:", resp); console.log("Import-status:", resp.status); console.log("Import-message:", resp.message); // console.log("Import-result:", resp.import_result); console.log("Import-summary:", resp.import_summary); } 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("importAllSchemaToDb"); } 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; } importAllSchemaToDb();
Additional Resources
Package to export full oracle database schema : oracledb-to-json-schema