Oradew Code Generators

PL/SQL Generators to use with Oradew extension for VSCode:

• Update Statement - Generate an Update statement for a table
• Insert Statement - Generate an Insert statement for a table
• Script to Run Procedure - Generate a Run script to execute procedure or function. Useful to print refcursors, etc.
• Script to Import Dependencies - Generate a Shell script to import schema dependencies
• Add Debug Parameters - Generate statements to log params of procedure or function

Install

Generators are stored as functions in a package named UTL_GENERATE.sql. The package has to be saved (compiled) on your database (DEV environment) before usage.

First clone the repo:

> git clone https://github.com/mickeypearce/oradew-generators.git
> cd oradew-generators

Create dbconfig.json with your connection credentials, then package source and deploy it to database:

# substitue "localhost/orclpdb", "hr" and "welcome" with your database information
> echo {"DEV": {"connectString": "localhost/orclpdb", "users": [{"user": "hr", "password": "welcome"}]}} > dbconfig.json

# generate deploy/run.sql script
> oradew package

# execute script on "DEV" env
> oradew deploy

Package UTL_GENERATE is now saved on your DEV DB and ready to use!

Usage

To use generators in your project simply copy configuration file oradewrc-generate.json to your workspace root. Then use Oradew: Generate... command to select a generator to run on a selected object.

Specification

The DB generator function has the following specification:

FUNCTION updateStatement(
  object_type IN VARCHAR2,    -- derived from path of ${file}
  name IN VARCHAR2,           -- derived from path of ${file}
  schema IN VARCHAR2,         -- derived from path of ${file}
  selected_object IN VARCHAR2 -- ${selectedText} in editor
) RETURN CLOB;

The first three function parameters (object_type, name, schema) are derived from path of the currently opened file: ${file} as ./src/${schema}/${object_type}/${name}.sql. Whereas selected_object is the currently selected text in the editor: ${selectedText}.