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.
• 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