Sunday, 31 December 2017

Windows Instant Client Setup

On my systems, I always opt for the instant client so I can get going with SQL*Plus as quickly as possible - the full client is just beyond my needs. If you are a regular consumer of my posts, you may have noticed I'm not primarily a Windows user, but of late I've had a need to be using Windows. So this particular post will be guiding the set up of the instant client, setting up your tnsnames and getting SQL Developer to use the OCI/thick driver.

So first, head over to the download page for Instant Client: I'm just grabbing the latest version as at the time of this article (

The packages I usually grab are:

  1. Instant Client Package - Basic Light
  2. Instant Client Package - SQL*Plus
  3. Instant Client Package - SDK

The basic and basic light packages make mention that one pre-requisite is the Microsoft Visual Studio 2013 Redistributable. So go ahead and install that before hand if necessary.

From the three files downloaded, you should end up with 3 zip files. Now, you will want to extract the contents of each into the same folder - usually I end up with a single folder named instantclient_12_2 containing all the files.

Each zip file Oracle provides contains a single folder (instantclient_12_2), so if you apply the same path during extraction, all files will go to the same location. In my example, I end up with all my instant client files at the following path:


So, I then cut the folder in the last segment of the path and put it in my C drive, such that I end up with C:\instantclient_12_2.

Now that we have all the files, we need to a couple of extra steps so we can start using SQL*Plus.

Our folder contains a binary for sqlplus, so we should add this path to our Path environment variable, so that we can type sqlplus within command prompt without needing to specify the full path. So open the Environment Variables configuration tool within Windows. On Windows 10, this is easily accessible by searching for it in the Start menu.

There are two options - User or System variables. Since I'm the only one using the system, I'll just opt for User variables. So focus on the Path line, click edit, and add the location where you stored your instant client files.

It's also not a bad idea to set an environment variable ORACLE_HOME to point to the same location.

With all that done, you should now be able to open up command prompt and type: sqlplus /nolog, to get an SQLPlus command prompt. If you have a database server you can connect to, try connecting too to make sure all is good.

Ok, the next part is how do we connect with our tnsnames entry?

I know my entry will look like this:

XE =
    (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))

So, option one is within ORACLE_HOME, make a folder in the path: $ORACLE_HOME\network\admin named tnsnames.ora with your TNS entry specifications (like above), and you don't need to do anything else - it will be automatically picked up. You can now connect to the server with the convenient shorthand of XE. E.g. sqlplus user@XE

If by chance you would prefer to keep it in another location, you just need an environment variable for TNS_ADMIN.

With the instant client now installed, I like to tell SQL Developer to use the instant client I have installed on my system, so navigate to Tools -- Preferences... -- Database -- Advanced, and set up the configuration based on where you store your instant client. 

Read more about the thick driver support within SQL Developer on Jeff Smith's blog:

Sunday, 22 October 2017

Keep the APEX builder tab and application tab in the same Firefox window

A long while ago, I switched to using Google Chrome, and I use Firefox usually only to test some feature so I literally have not run the APEX builder in Firefox since version 3.2. However, recently I heard about Firefox having a neat new feature which is called "Containers" and is part of their Test Pilot program, where you can have a set of tabs each with their own cookie/storage/session space - you can read more about the technology here -

This is good news, because it would allow be to be logged in with different user account simultaneously and thus speeding up testing time.

However, much to my surprise, unlike it Chrome when you run the application, it opens the application in a new tab, Firefox opens the application in a new window! I don't particularly like this behaviour, so after some digging I firstly found there is a preference to open new windows in a new tab instead.

The bad news is, this seemed to have no bearing on the behaviour - at least from APEX (I didn't test any other sites that open new tabs).

So, this is where we can turn to some more advanced configuration options. In the address bar, go to about:config and accept the risks. From here, filter the list by applying the search "".

So, the highlighted setting "" has possible values of:

  • 0: Apply the setting under (A) to ALL new windows (even script windows)
  • 1: Override the setting under (A) and always use new windows
  • 2: Apply the setting under (A) to normal windows, but NOT to script windows with features

So, we will want to go ahead and update that value to 0, now in APEX, when you run the application, you will get a new tab instead of a new Window.

One downside with having them in tabs though is the fact that Firefox doesn't support focusing other tabs. The only way to do it that I've seen is to show an alert on the other tab, which will force the tab to steal focus. Not entirely desirable. That said, I set up an example user script to achieve this.

Saturday, 30 September 2017

ES6 with SQLcl / JDK9

In JDK 8, the Nashorn engine was still on ES5. With the latest release (JDK9), they have included optional support for ES6, which can be enabled by way of an argument. So, to test out this functionality I will be trying with a new variable/constant construct called `const`.

Before developing my script for SQLcl, I sometimes like to use jjs to verify everything. jjs is an interactive shell that invokes the Nashorn engine. So by defualt it uses ES5, so we can write a script like so:

jjs> var a = Math.floor(Math.random()*100) * 55
jjs> print (a)

With JDK9, we can invoke this tool with ES6 support via an optional argument `--language` where we can specifiy either es5 or es6.

$ jjs --language=es6
jjs> const a = Math.floor(Math.random()*100) * 55
jjs> a = 2
<shell>:1 TypeError: Assignment to constant "a"
jjs> print (a);

The above snippet shows I can use the new `const` construct that is a part of the ES6 spec within the Nashorn engine. See this article for more information about some of what's included:

This is good and all, but the question is, how do we get this working in SQLcl?

Well, thankfully, when invoking our Java programs we can set nashorn orguments with a command: `-Dnashorn.orgs=.`.

If you take a look at the SQLcl bash script, you will see that if you set some JAVA_OPTS values, that will get passed along to the call to SQLcl. So, if you want to run ES6 (which, why wouldn't you!) in your SQLcl scripts, simply set that variable accordingly.

export JAVA_OPTS="-Dnashorn.args=--language=es6"

So, to verify it works, I set up a little test script using `const` instead of `var` (which isn't supported in ES5). The script looks like:

print ("Set variable `unchangeableValue` to 55");
const unchangeableValue = 55;
try {
    print ("Trying to change variable to 0");
    unchangeableValue = 0;
} catch (e) {
    print ("Exception");
    print (e);

First pass, we will run using ES5 (the default), and the engine picks up on a unrecognized key word (const) and throws an exception:

$ sql /nolog

SQLcl: Release 17.3.0 Production on Sat Sep 30 16:27:27 2017

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

SQL> script test.js
javax.script.ScriptException: :2:0 Expected an operand but found const
const unchangeableValue = 55;
^ in  at line number 2 at column number 0

Second pass, we set the JAVA_OPTS as specified above. The output then becomes:

$ JAVA_OPTS="-Dnashorn.args=--language=es6" sql /nolog

SQLcl: Release 17.3.0 Production on Sat Sep 30 16:29:17 2017

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

SQL> script test.js
Set variable `unchangeableValue` to 55
Trying to change variable to 0
TypeError: Assignment to constant "unchangeableValue"

And the script runs as we expect!

Tuesday, 19 September 2017

Setting up Oracle XE in Docker - Lessons Learned

For a little side project I'm working on, I had the need to set up a Docker instance of Oracle. I have read about Docker a while back and understand the general concept - but up until now, I hadn't had much experience with it (other that running the basic hello-world Docker example).

So, since I'm about using the free version of the Oracle Database at the moment (Oracle XE), I wanted specifically to build that in a VM. Now, I know that Oracle has a GitHub repository with some sample Docker files - unfortunately, I didn't have success with the Oracle XE version, so it wasn't a bad time to go and build my own.

Here are some things I learnt:

1. The default storage location on my Ubuntu system is: /var/lib/docker

This is a problem as I'm developing, since the way my disk is set up, the root partition soon fills up.

2. You can change the location that images are stored, by creating a json file: /etc/docker/daemon.json.

In here, you will want an entry "data-root" with the path to your desired location. The Docker daemon runs as a service, which is effectively the `dockerd` binary. So, whilst testing your config, it's a good idea to stop that (systemctl stop docker). Then, update your config and run `dockerd`. There is also a `debug` property (true|false) that you can set so you get additional output. The first time I ran the daemon with the config file in place, my system had to set up a storage pool

DEBU[0001] devmapper: Pool doesn't exist. Creating it.

This seemed to bring my system to a hault, but leaving my system and coming back it was doing something - so keep in mind that isn't a quick process!

Something curious is that this config file allows you also to specify the storage driver. Now, I don't know much about the Docker storage drivers at this stage, but without the config file it uses the `aufs` storage driver, however if I try to set that in my config file, I get an error about the driver not being available. So it's possible I'm missing some other corresponding entry.

Once all looks good, switch back to using the daemon as a service: systemctl start docker

3. The CMD argument in your Dockerfile is best as a single executable

The CMD argument is what get executed whenever you run a new instance of your image. For the image I built, my command was to start the oracle-xe service. So I started with CMD ["/etc/init.d/oracle-xe", "start"]. This is good, but the problem is when running in detached mode, because the CMD completes, the container also exits.

After doing a bit of research and looking at the Oracle's example Dockerfile, it is a good idea to watch a file - the Oracle one was monitoring an log file, but I so another example online suggested just to watch /dev/null. So effectively I needed to run two commands:

1. oracle-xe start
2. tail -f /dev/null

When I tried chaining these in the CMD property of the Dockerfile, the container threw errors.

Again, after some more research and looking at the example on Oracle's repository, if you have more than one program to run, you should place this in a simple shell script, and pass that into the CMD property.

So, I end up with a script:

/etc/init.d/oracle-xe start
tail -f /dev/null

Then the Dockerfile referencing:

CMD ["/root/install/"]

4. The images are generally very minimal and need to have context set up

If you run the Ubuntu image:

docker run -it ubuntu:16.04

You will see there is very little environment variables set up:

root@b7535b34011e:/# pwd

So, it's not a bad idea to set up some basic environment such as the user

USER root
ENV HOME /root 

When copying files over, at least now we are placing them within the users home (/root) rather than the root of the file system.

5. It's a good idea to run interactively whilst developing

Docker supports two methods to run an image

-i, --interactive=true|false
    Keep STDIN open even if not attached. The default is false.

-d, --detach=true|false
    Detached mode: run the container in the background and print the new container ID. The default is false.

I like to run interactively most of the time to see everything is behaving as expected and to see any output. So the full command would be something like:

docker run -it <image_tag>

Replace i with d when all looks good!

If your CMD property isn't giving the desired result, you can override what is set in the image by passing the --entrypoint command line argument with the value of /bin/bash

docker run -it --entrypoint=/bin/bash <image_tag>

6. You need to set the shared memory size in the command line argument

XE requires at least 1gb of shared memory, so I am a generous guy and give 2gb. This needs to be set when running the image through an argument. So the previous example becomes:

docker run --shm-size=2g -it --entrypoint=/bin/bash <image_tag>

7. Avoid anything that depends on a hostname

When installing XE, the generated listener.ora will reference the hostname. Since each time you run an image in a new container the hostname will be something different, it's a good idea to update this so that you will be able to connect on each corresponding container.

See the example of the listener.ora from the Oracle repository here:

8. Finally, some useful commands

docker build -t   
docker run --shm-size=2g -it 

# Stop running containers
docker stop $(docker ps -q -a)
# Remove all containers
docker rm $(docker ps -a -q)
# Delete images with no tag/name
docker rmi $(docker images -f "dangling=true" -q)
# Delete all images
docker rmi $(docker images -q)

Friday, 18 August 2017

Bulk loading a template APEX application in a single workspace

I recently had a need to import the same application multiple times - 30+, so each person accessing the workspace could use their own application to play around with. And the user list was supplied to me in the format of an Excel spreadsheet. So, first in the Excel sheet, I had to make a new column to derive some sort of unique value - what better than the first letter of their first name and the surname. Like John Smith -> JSMITH.

So, first I made a new column with the value as a formula, which was: =LEFT(A2,1). That would give me "J" for "John.

Then, for the actual account ID I come up with the formula =UPPER(CONCAT(C2, B2)) Where C2=J and B2 = SMITH, giving me JSMITH.

With that list, I can copy it over to my trusty text editor in order to manipulate to create an array.

The first part I wanted to do was create all the users, so for that I can use raw PL/SQL, and come up with a block of code like so:


    type t_users is table of varchar2(200)
        index by PLS_INTEGER;
    l_all_users t_users;
    l_ws_id NUMBER;

    procedure append_list(
        p_emps in out t_users,
        p_new_person in varchar2
        p_emps(p_emps.count + 1) := p_new_person;
    end append_list;


    l_ws_id := apex_util.find_security_group_id('WORKSPACE_NAME');

    append_list(l_all_employees, 'USER1');
    append_list(l_all_employees, 'USER2');

    for i in 1..l_all_employees.count

            p_user_name => l_all_employees(i),
            p_web_password => l_all_employees(i),
            p_developer_privs => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL',
            p_change_password_on_first_use => 'N',
            p_allow_app_building_yn => 'Y',
            p_allow_sql_workshop_yn => 'Y',
            p_allow_team_development_yn => 'Y'
    end loop;


Run that, and you should find all the users now have accounts in the workspace - I just assigned the password value as the same as the username to keep things simple.

So, the next part was loading the applications. APEX comes with the API APEX_APPLICATION_INSTALL, so if we look at the documentation, we can see a simple import script as:

  apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id );

So, we need to run an anonymous block, and then the actual application export script - which we can't put inside an anonymous block.

So, there comes in the scripting functionality of SQLcl. It support running JavaScript, so from the first step, I can make an Array of all the users, but this time in JavaScript. Then we can just loop over that structure and set the statement to run - which we do two separate statements, 1. the anonymous block; and 2. the application import script:

function runCmd(cmd){

var people = [

for (var i = 0; i < people.length; i++){

    print ('Loading application for: ' + people[i]);

            "    l_ws_id number;",
            "    l_ws_id := apex_util.find_security_group_id('WS_NAME');",
            "    apex_util.set_security_group_id(l_ws_id);",
            "    apex_application_install.generate_application_id;",
            "    apex_application_install.generate_offset;",
            "    apex_application_install.set_application_alias( 'F' || apex_application_install.get_application_id);",
            "    apex_application_install.set_application_name( '" + people[i] + " - Workshop Application' );",


    print (' ->');

Then, I just connect to my database in SQLcl and run that script (named appLoader.js)

conn user/pass@DEV
script appLoader.js

note: I could have just run everything (creating the user account and importing the app) in the SQLcl script, but this is just the process I followed as I was trying to figure things out :)

Thursday, 13 July 2017

Report row buttons firing a dynamic action

A lot of the time on reports of data, we'll define a link column, and specify an image which is the beloved pencil icon.

Which is good and all, but looking in the Sample Database Application (packaged application), they have taken a different approach to style links - a button that stretches the width of it's containing cell. So, the UI ends up looking like so:

So, is this done?

Well first, you define a new column for the placeholder. Suppose I want my link column to be Report, I define an extra column in my query:

, 'Report' report_link

Then I need to go to my new column's attributes, and change the column type to a link.

And, in the link attributes, set the text to what you want displayed. Here, I'm just going to display the value of the new column and point to my desired page. So I set the target page, then also the Link text as #REPORT_LINK#.

At this point the report will just show link text, as we expect.

So, then to replicate the same style, we just need to apply the same classes, which happen to be:

  • t-Button 
  • t-Button--simple 
  • t-Button--hot 
  • t-Button--stretch
So, in the link, set the link attributes as:

class="t-Button t-Button--simple t-Button--hot t-Button--stretch".

Now when we run the page, we get the desired behaviour:

How about for a dynamic action?

Well, in the columns link, there is no option for "Defined by Dynamic Action" as we usually see in regular page buttons. We just get page or URL options.

Typically, you will assign a special class to your element so that you can specify a jQuery selector on your dynamic action target that will fire and optionally a data attribute used to store the ID of the row (such as data-order-id="xx"). More on this later.

So, back to the column action. A typical pattern here will be to specify URL and that a target of #, javascript:void(0);, or javascript:;.

The # is usually a trick when you want to go to a position in the same page, so not using href as it was designed for.

The void is a JavaScript operator that returns void, and the other one is obviously an empty javascript expression!

When using void, it is important to pass in 0 to the function, or it's likely you will receive the following error:

Uncaught SyntaxError: Unexpected token )

With all that said, I think it is better to avoid these (hacky?) solutions. It is not a "link" so the href tag should ideally not be there. If you want a row button that will fire a dynamic action, instead you should set the column type to plain text and then set a HTML expression on the column.

Since it is a button we want, it is a button we shall use. We will end up with markup as follows,  that we add into the HTML expression:

<button class="orderReportButton
           t-Button--stretch" data-order-id="#ORDER_ID#" type="button">
    <span class="t-Button-label">Report</span>

There we have.

Now, we just need to finish off our dynamic action. We create a click dynamic action based on a jQuery selector.

Then, in our true action, we can just reference the expression "this.triggeringElement.getAttribute('data-order-id')" or jQuery "$(this.triggeringElement).data('order-id');

Monday, 10 July 2017

Custom workflow to download or upload specific Google Drive files

I had an interest in downloading and uploading a specific file into my Google Drive at certain timing points. For examples sake, let's call the files "Designs.cad". There is a command line project on GitHub which is Go based. So if you have Go installed, it is just a matter of running:

go get

This will download from source, so if you don't want to do this or if you don't have Go installed (you should - there are lots of great go projects), you can see a list of releases on the project page - - which you can download and place in your system somewhere, with the command becoming "gdrive".

When you first install it, you will need to authenticate your account. Running any command will prompt you to go to a URL to get an authorization code and paste in back on your console. So for example, if I run:

gdrive list

Which is designed to list files in my Drive. After running the command, if you haven't previsouly authenticated, you will be prompted to go to a URL to paste the authorization code. Sample output:

trent@birroth:~$ gdrive list
Authentication needed
Go to the following url in your browser:

Enter verification code: xxx

Where xxx is the verification code I pasted back in.
If successful, you will end up with a file at: $HOME/.gdrive/token_v2.json, containing on the necessary authorization codes so the program can continue to function whilst ever the authorization exists for this application.

So, looking at the help, there are two commands that will be useful - download and update.

We can find out the program usage with the help command, like so for the download operation.

$ gdrive help download
Download file or directory
gdrive [global] download [options] 

  -c, --config          Application path, default: /home/trent/.gdrive
  --refresh-token       Oauth refresh token...
  --access-token        Oauth access token...
  --service-account     Oauth service account filename...

  -f, --force           Overwrite existing file
  -s, --skip            Skip existing files
  -r, --recursive       Download directory recursively...
  --path                Download path
  --delete              Delete remote file when download is successful
  --no-progress         Hide progress
  --stdout              Write file content to stdout
  --timeout             Set timeout in seconds...

To use this, I need to find the file ID, which I can do by using the list command, or looking at the web interface of Google Drive. The filename will be the same as that as it is on Google Drive. So, with that information I end up with the command:

gdrive download --path /home/trent/ --force 1GUG3Y3Ce56Pa0k2

Which gives output like so:

Downloading Designs.cad -> /home/trent/Designs.cad
Downloaded 1GUG3Y3Ce56Pa0k2 at 18.2 KB/s, total 18.2 KB

Similarly, to send the file in the other direction, we need to look at the usage for the command update

$ gdrive help update
Update file, this creates a new revision of the file
gdrive [global] update [options]  

  -c, --config           Application path, default: /home/trent/.gdrive
  --refresh-token        Oauth refresh token...
  --access-token         Oauth access token...
  --service-account      Oauth service account filename...

  -p, --parent           Parent id...
  --name                 Filename
  --description          File description
  --no-progress          Hide progress
  --mime                 Force mime type
  --timeout              Set timeout in seconds...
  --chunksize            Set chunk size in bytes...

That means, our command will become:

gdrive update 1GUG3Y3Ce56Pa0k2 /home/trent/Designs.cad