Skip to content

Howtos

Jump to bottom
Mickey Pearce edited this page Aug 2, 2021 · 1 revision

Welcome to the oradew-vscode how-to!

Table of contents

1. How to add DB objects from another schema to existing project?

  • First add new schema user to "users" property in dbconfig.json...

  • Then you can run create command (Oradew: Create Source from DB) which creates files from all DB objects for all users in your project, including the newly added schema, but at the same time it will overwrite also the existing schema objects. Maybe you don't like that...

  • You can be more specific with --file parameter. For example, to add only objects from HR schema, run from CLI:

> oradew create --file ./src/HR/*

The --file parameter is actually a glob. Configuration value "source.input" is used from dbconfig.json ("./src/**/*.sql" by default), if the --file paramter is ommited.

2. How to make a deployment script from files changed by specific commit(s)?

  • You can manually edit "package.input" configuration (in dbconfig.json), add file paths (globs) that you wish to include and then run package command (Oradew: Package) to create a deployment script from those files.

  • But there is an easier way with --commit paramater that populates input configuration array automatically. For example, to extract changed .sql files from ./src or ./scripts directory affected by two commits (specified by hash ID), run from CLI:

> oradew package --commit 4d78419c0d10fee38fd7d --commit 9838df36f699d6c8108fe8

You can manually edit "package.input" afterwards, if you wish to reorder or add additional paths, and then run package command again to get the desired output script.

  • To add yet another commit but not replace entire "package.input" configuration use --append parameter:
> oradew package --commit 1c78419c0d10fee38fd7d --append

This will add only changed paths from desired commit that are not already included in the current configuration.

3. How to customize Oradew commands in VS Code?

  • You can customize commands by configuring oradew tasks. First run VS Code command Tasks: Configure Task and choose oradew task to configure. For example, select: oradew: import--object, it will create a new entry in tasks.json with default task-command parameters.

  • You should edit parameters to suit your needs. First rename label property to a new name that will be used as a reference for your new task.

In this example, we replace ${selectedText} substitution variable to make an input prompt dialog. See: Tasks: Input variables for more information.

"tasks": [
  {
    "type": "oradew",
    "name": "import--object",
    "params": [
      "import",
      "--env",
      "${command:oradew.getEnvironment}",
      "--object",
      "${input:objectName}",
      "--user",
      "HR"
    ],
    "label": "oradew: import object from Input prompt"
  }
],
"inputs": [
    {
      "type": "promptString",
      "id": "objectName",
      "description": "Type Object name to import.",
      "default": ""
    }
  ]
  • Run newly created task with Tasks: Run task command and type oradew: import object from Input prompt.

4. How to customize Source file structure?

Use source.pattern confuguration to define a different source structure. Source files are mapped to object type directories by default.

Let's say we need only packages, functions, procedures and views in our project. {schema-name} variable can be ommited from path pattern because we will have a single-schema project. We group objects into 3 directories: packages, views and procedures, in this example:

"source.pattern": {
    "packageSpec": "./src/packages/{object-name}-spec.sql",
    "packageBody": "./src/packages/{object-name}-body.sql",
    "view": "./src/views/{object-name}.sql",
    "function": "./src/procedures/{object-name}-func.sql",
    "procedure": "./src/procedures/{object-name}-proc.sql"
  }

This pattern is then used as a "filepath-to-DBobject" mapping by oradew commands (create, import and compile, etc.).

5. How to customize DDL source of imported DB objects?

Oradew's import command uses DBMS_METADATA.get_ddl function to import objects by default. This functionality can be customized with setting "import.getDdlFunction" in oradewrc.json in a way that you write your own function on DB that will be used by Oradew to import objects. This function should have 3 parameters - first 3 paramaters of DBMS_METADATA.get_ddl (example bellow).

To add a terminator at the end of each imported object you could make a function on your DB as follows, for example:

FUNCTION my_get_ddl (
  object_type         IN VARCHAR2,
  name                IN VARCHAR2,
  schema              IN VARCHAR2 DEFAULT NULL
RETURN CLOB
is
begin
  -- transform parameter to add / terminator at the end
  DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
  -- call get_ddl to return object ddl
  return DBMS_METADATA.get_ddl(object_type, name, schema);
end;

and then add a property with "import.getDdlFunction": "my_get_ddl" to your oradew project (oradewrc.json).

6. How to change character set in which commands that use Oracle DB CLI executes?

Commands Oradew: Run Current File As Script and Oradew: Deploy use Oracle DB CLI to execute. CLI executable path can be configured with "oradew.cliExecutable" setting. It is set to sql by default (SQLcl) but can be changed to sqlplus, for example.

You can write a custom command with setting "oradew.cliCommand", to pass additional parameters to DB CLI:

"oradew.cliCommand": "chcp 1250 && exit | \"${cliExec}\" -S ${connString} @\"${filename}\""

Here we added chcp 1250 to change the code page before executing. Template literals ${cliExec},${connString} and ${filename} are substituted on the fly.

Another option would be to create an intermediate bat script; a sql.bat file with content:

chcp 1252
set NLS_LANG=.WE8MSWIN1252
sqlplus %*

and then set "oradew.cliExecutable": "sql.bat" to execute.

Clone this wiki locally