APEX Interactive Grid Processing with Dynamic Actions

May 18, 2017 at 6:39 pm | Posted in Oracle Developement | Leave a comment

Here is a cool post on triggering dynamic actions from an interactive grid: http://lschilde.blogspot.com/2017/05/apex-51-interactive-grid-row-processing.html

Validating JSON in the database

April 24, 2017 at 6:04 pm | Posted in Oracle Developement | 1 Comment

Creating REST services and consuming JSON inside the Oracle database has become quite easy. The APEX_JSON package provides a number of useful procedures and functions to work with JSON. However, one thing I haven’t found is a way to validate JSON before parsing it.

To my knowledge the is not yet a PL/SQL way to check whether a JSON string is valid, i.e. to make sure that all the brackets, commas, colons, etc. are there. There is however, a column check constraint you can use in 12c that will only allow valid JSON to be inserted into a table. So here is a quick workaround for validating JSON using the check constraint: I simply take the JSON and attempt to insert it into the table. If the check constraint is violated, I’ll get an error.

I wrapped to whole thing in a function that will then simply return a boolean value for the JSON passed into it.

create table json_validator ( 
	 id   number generated always as identity primary key
	,json clob
	,constraint validate_json check (json is json format json)

create or replace function validate_json (l_json in clob)
return boolean
  l_id pls_integer;
  e_invalid_json exception;
  pragma exception_init (e_invalid_json,-2290);
  pragma autonomous_transaction;
	insert into json_validator (json) values (l_json);
	return true;
	when e_invalid_json then
	  return false;

set serveroutput on
  l_json  clob;
  l_return boolean;
  l_json := '
				{"widget": {
					"debug": "on",
					"window": {
						"title": "sample konfabulator widget",
						"name": "main_window",
						"width": 500,
						"height": 500
					"image": { 
						"src": "images/sun.png",
						"name": "sun1",
						"hoffset": 250,
						"voffset": 250,
						"alignment": "center"
					"text": {
						"data": "click here",
						"size": 36,
						"style": "bold",
						"name": "text1",
						"hoffset": 250,
						"voffset": 100,
						"alignment": "center",
						"onmouseup": "sun1.opacity = (sun1.opacity / 100) * 90;"

  l_return := validate_json(l_json => l_json );

  if l_return then 
	dbms_output.put_line('JSON is valid');
	dbms_output.put_line('JSON is NOT valid');
  end if;


How to use Font Awesome Animation Library in Applications

April 11, 2017 at 6:10 pm | Posted in Oracle Developement | Leave a comment

A very nice blog post about using Fontawesome animations by :



Expose Yourself with ngrok

April 7, 2017 at 9:01 am | Posted in Oracle Developement | Leave a comment

Need to expose a web application behind your firewall to the web? No problem with ngrok. Simply download ngrok and tell it which local port to listen to, and it will provide you with a publicly accessible URL through which you can reach your application.

I’ve tested it with a simple Node.js Hello World application running on my laptop on port 3000 behind my firewall. I then started ngrok with this simple command:

$ ngrok http 3000

Ngrok responded with my new URL and also logged all requests.


This is a great tool for development and testing, but definitely use with caution! 😉


Live JavaScript logging with Quokka

April 3, 2017 at 1:38 pm | Posted in Oracle Developement | Leave a comment

If you’re using Visual Studio Code for writing JavaScript, the handy Quokka plugin will allow you to preview values at design time.

Add the Quokka extension via the Extensions Marketplace in VS Code.quokka_ext

Then, from the command palette, either create a new JavaScript file through Quokka, or start Quokka for an existing file.quokka2

Now you can attach the special comment /*?*/ to a variable to preview it’s value. Make sure that the comment touches the variable. The preview values will magically show up in the editor and the output console.


Here is a live view:



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 (compile_bmc.sh) 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/compile_bmc.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.



Next Page »

Entries and comments feeds.