Compile PL/SQL with VS Code using SSH

March 27, 2017 at 8:04 am | Posted in Oracle Developement | Leave a comment

First of all thanks to Morten Braten’s blog post Using VS Code for PL/SQL development. I had been wanting to try this editor for a while but still felt loyal to Sublime.

Morten described how to use Visual Studio Code for PL/SQL development, and how to use its task runner to compile your code via SQL*Plus. I would like to expand on this aspect and show how I used the task runner to compile code through an SSH tunnel via SQLcl. I’m using a Mac, so you may have to do some adjustments for other operating systems.

Basic requirements:

  • SSH key to your database server
  • SQLcl
  • VS Code

First I created an SQL file (bmc_login.sql) that will log my SQLcl session into my remote database via SSH:

sshtunnel christoph@bmcdb -i /Users/cruepprich/bmc/bmc_key -L 8888:localhost:1521
connect scott/tiger@localhost:8888/orcl

For more information about how this works see OraOpenSource.

Then I needed a shell script ( that the task runner can execute:

~/oracle/sqlcl/bin/sql -S /nolog @bmc_login <

This file calls the SQLcl executable in silent mode (-S) without logging in (/nolog), and runs the bmc_login.sql to connect to the remote database. Then it executes the first command line argument ($1) which it will get from the task runner. Finally it will display any compilation errors and exit.

The last thing to do is to configure the task runner in VS Code. Edit the tasks.json file and add the following task. Note that I’m showing an example with multiple tasks. The task of interest is the one named Compile in bmc.

    "version": "0.1.0",
    "tasks": [
            "taskName": "print hello",
            "command": "echo",
            "args": ["Hello"],
            "isShellCommand": true
            "taskName": "Compile in bmc",
            "command": "/Users/cruepprich/sh/",
            "isShellCommand": true,
            "args": ["${file}"]
 The ${file} argument refers to the file you are currently editing. So you want to make sure that you are editing a PL/SQL program and then you can run this task now from the VS Code Command Pallete:
command Pallette
You can also add a keyboard shortcut (ctrl-c) to run this task.
   "key": "ctrl+c",
   "command": "workbench.action.tasks.runTask",
   "args": "Compile in bmc"

Now I can edit PL/SQL programs in VS Code and compile them directly on my remote database via SSH tunnel.

Bulk Updating Interactive Grid Records

March 23, 2017 at 9:36 am | Posted in Oracle Developement | Leave a comment

After my previous post on Updating Interactive Grid Cells, I wanted to try bulk updating IG records.

In this example I want to bulk update the salaries and commissions for the employees. I created a new button called Double Salaries. When the button is clicked, the salaries of the selected employees get doubled. The employees with no salary get a salary of 1000. Sales people will get their commission updated to 10% of their new salary.

Employees are selected by clicking the row selector checkbox of the interactive grid. The interactive grid’s static ID is emps.

The work is done by the JavaScript action of a dynamic action on the Double Salaries button:

var record, sal;
//Identify the particular interactive grid
var ig$     = apex.region("emps").widget();

//Fetch the model for the interactive grid
var grid    = ig$.interactiveGrid("getViews","grid");

//Fetch the model for the interactive grid
var model   = ig$.interactiveGrid("getViews","grid").model;

//Fetch selected records
var selectedRecords = apex.region"emps").widget().interactiveGrid("getViews","grid").view$.grid("getSelectedRecords");

//Loop through selected records and update value of the salary column
for (idx=0; idx < selectedRecords.length; idx++) {
    //Get the record
    record = model.getRecord(selectedRecords[idx][0]);

    //Get the current salary and commision values
    sal  = model.getValue(record,"SAL");
    job  = model.getValue(record,"JOB");
    comm = model.getValue(record,"COMM");

    //If there is no salary, set it to 1000, else double it
    if (sal === '') {
        sal = 1000;
    } else {
        sal = sal * 2;

    //Update the record with doubled salary and new commission
    model.setValue(record,"SAL", sal);

    //Set commission to 10% of salary for sales people
    if (job = 'SALESMAN') {
        comm = sal *.1;
        model.setValue(record,"COMM", comm);



Node Newbie Error – NPM Refusing to Install Package as a Dependency of Itself

March 22, 2017 at 10:36 am | Posted in Oracle Developement | Leave a comment

Source: Node Newbie Error – NPM Refusing to Install Package as a Dependency of Itself

APEX: Updating Interactive Grid Cells

March 9, 2017 at 5:34 pm | Posted in Oracle Developement | 4 Comments

Moving from the old tabular form to the interactive grid takes some getting used to. Especially when trying to manually update cells with JavaScript.

Here is an example of how to access and modify particular cells in an interactive grid with JavaScript. The example promotes an employee when the Promote link is clicked, which doubles the employee’s salary, and assigns him a new job.

First I built an interactive grid on the EMP table. I’ve used all the defaults except:
Enable editing with Update Row.
Set the region’s static ID to emps.
Added a link column with target type: URL, URL: #


The link text is set to Promote, and I added class (ig-link) so that I can connect a dynamic action to it:


Next I created a dynamic action that fires when the Promote link is clicked.


The true action is a Execute JavaScript Code action, with the following code:

//Get the link element that was clicked
var $te = $(this.triggeringElement);

//Get the ID of the row
var rowId = $te.closest('tr').data('id');

//Identify the particular interactive grid
var ig$ = apex.region("emps").widget();

//Fetch the model for the interactive grid
var model = ig$.interactiveGrid("getViews","grid").model;

//Fetch the record for the particular rowId
var record = model.getRecord(rowId);

//Access the cell value via the column name
var sal = model.getValue(record,"SAL");

//Set the values for the JOB and SAL cells

Here is the working example:


It is worth noting that the <tr> element of the interactive grid row contains the row’s unique identifier. This could be a row ID or a primary key value, depending on how you configured the grid.


Tuning Tomcat for APEX/ORDS in Production

January 16, 2017 at 10:44 am | Posted in Oracle Developement | Leave a comment

Excellent blog post by Johnathan Dixon about configuring Tomcat for use with ORDS.


Creating ORDS REST Services with PL/SQL

December 30, 2016 at 11:31 am | Posted in Oracle Developement | Leave a comment

Here is an excellent writeup for creating REST services with PL/SQL:


SQL*Plus Error OID generation failed

November 21, 2016 at 9:40 am | Posted in Oracle Developement | Leave a comment

I received this error on a MacBook, when trying to connect with SQL*Plus to my database:

SQL*Plus: Release Production on Mon Nov 21 08:27:22 2016

Copyright (c) 1982, 2016, Oracle. All rights reserved.

ORA-21561: OID generation failed

The issue comes from not having the MacBook’s hostname associated with the home IP address. To fix this, simply find your hostname:

~$ hostname

Then edit your /etc/hosts file and add the home name to the home IP address:       localhost MY_MACBOOK

Also make sure that when you edit the hosts file, that you are not connected to a VPN. Some VPN clients may overwrite the hosts file.


Create your own Font Awesome Icon

October 17, 2016 at 2:02 pm | Posted in Oracle Developement | Leave a comment

There may be a special character you wish to use in the same way you would use a Font Awesome icon. To do this you need to find the hex code for the character and a little bit of CSS.

  1. Find the hex code for your character
    There are many sites that show you the ASCII values for various letters, numbers, or characters. I needed to have the greek letter Sigma, (Σ) so I looked it up here, and copied the hex value 03A3.
  2. Add CSS to your page or global CSS file:
    .sigma:before {
       content: "\03a3";
  3. Use the new CSS class like any other Font Awesome icon:
    <i class="fa sigma"></a>

You can also use any of the Font Awesome modifiers such as size with this method.

Please leave a comment if this helped you.


APEX 5 DA when cancelling modal dialog

October 11, 2016 at 11:15 am | Posted in Oracle Developement | 1 Comment

I needed to have a dynamic action that fires when a modal dialog is cancelled or the escape key is pressed. To do this in APEX 5, on the parent page, you can create a dynamic action based on a custom event called dialogclose.

Parent page:
Custom Dynamic Action

Event: Custom
Custom Event: dialogclose
Selection Type: JavaScript Expression
JavaScript Expression: document

The True/False actions can be set as usual, such as refreshing a report or setting some value.




October 11, 2016 at 10:51 am | Posted in Oracle Developement | Leave a comment

In light of the OTN Appreciation day I want to start a little initiative of appreciation myself. I know my job would be quite a bit more difficult if it weren’t for the help I receive from other people’s blogs.

Writing helpful blog posts takes time and effort. In order to show a little bit of appreciation to the bloggers who take the time and make the effort to post helpful articles, I’m going to let them know via twitter, using their handle and the hashtag #YourBlogHelpedMe.

I encourage others to do so as well. I’m sure it will not only allow us to show gratitude, but it will encourage more bloggers to write, and built up the community.


Next Page »

Entries and comments feeds.