OTN Appreciation Day: Oracle JET Cookbook

October 11, 2016 at 9:35 am | Posted in Oracle Developement | Leave a comment

I’m taking part in the Massive Open Online Course (MOOC) for learning about the Oracle Javascript Extension Toolkit (JET). I’m impressed with the quality of the learning materials and the methodology by which the course is taught. I’m especially impressed with the Oracle JET Cookbook, the site that allows you to browse, explore, and learn about the many available components available in the JET.

It is amazing how well the cookbook is laid out, and how easy it is to find component examples and code samples. There are also previews available for various mobile devices, and the JavaScript documentation intelligently linked for each example.

All in all: Well done, and thank you for providing such an awesome resource!



Handy Regexp trick for Listaggs

September 27, 2016 at 7:46 pm | Posted in Oracle Developement | Leave a comment

I use the listagg function to generate a comma separated list of employees who work in department 10 from the EMP table.

To make the list more readable, I want to substitute the last comma with the word and.

 with q as (
 select listagg(ename,', ') within group (order by 1) ret
from emp
 where deptno = 10
 select regexp_replace(ret,',',' and',instr(ret,',',-1)) ret
 from q;




APEX 5 Theme/Static files issue (503 error)

September 7, 2016 at 10:32 am | Posted in Oracle Application Express (Apex), Oracle Developement | Leave a comment
Tags: , , , , ,

I’ve recently come across the issue of static images not working and my custom theme roller theme not being applied to my APEX 5 app.

When looking at the browser’s console, I noticed a bunch of errors:
Failed to load resource: the server responded with a status of 503 (Service Unavailable)

After digging around some and scratching my head for a while, the issue turned out to be expired APEX_REST_PUBLIC_USER and APEX_LISTENER database accounts.

When the passwords were reset for these accounts, the images came back and the custom theme was applied.

Display Checkmarks for Yes values in APEX report.

July 25, 2016 at 1:13 pm | Posted in Oracle Developement | Leave a comment

I have a table with an “active” flag column that stores either Y or N. In an APEX report, rather than displaying the literal letter in an APEX report, I want to display a green checkmark for Y, and a red X for N. To do this, I wrote a couple of custom classes utilizing the Font Awesome icon library.

I placed these two CSS classes into my global CSS file:

.check-Y:before {
 content: "\f00c";
 color: green;
.check-N:before {
 content: "\f00d";
 color: red;

The values for the content attributes can be gotten from the Font Awesome cheatsheet (ignore the &#X characters).


In my APEX report ACTIVE column, I added the following HTML Expression:


When the report runs, the HTML expression will return either check-Y or check-N, thus referencing the classes with the green check mark, or the red X.







Convert .ppk to .pem

June 23, 2016 at 4:08 pm | Posted in Oracle Developement | Leave a comment

If you’re working on a Mac, you may have encountered the problem of trying to use ssh with a .ppk (PuTTY key file). You can easily convert this file to a Mac friendly .pem file.

  1. Install Homebrew
  2. Install putty, which also installs puttygen:
    brew install putty
  3. Generate .pem file:
    puttygen keyfile.ppk -O private-openssh -o keyfile.pem
  4. Change permissions:
    chmod go-rw keyfile.pem
  5. Connect:
    ssh -i keyfile.pem username@server


Configure AWS Cloud Instance for SSH access

June 14, 2016 at 2:49 pm | Posted in Oracle Developement | Leave a comment

In a previous post I explained how to set up a SQL Developer connection with SSH using a .pem key file. In this post I’ll show you how to generate the .pem file and what you have to do on your remote AWS instance in order to get this to work.

In my case, I use an EC2 instance on which my Oracle database is installed. Via the EC2 online dashboard, I created a Key Pair:


After clicking Create the private key file sqldev-key.pem downloaded. Note that you can only get the key file during the creation process. You’ll never be able to re-create the key file again.
I then placed the downloaded file in a directory on my laptop. Using terminal, I then generated the public key using ssh-keygen -y. When asked for a file, I gave it the sqldev-key.pem file name. The command then output the pubic key string, which I copied to my clipboard.

In order to limit access to the instance, I created a user account with no privileges on my database host, through the command line:

$ useradd sqldev
$ passwd secret

I then logged in as sqldev and created the file ~/.ssh/authorized_keys, into which I pasted the key from my clipboard. I then changed the permissions of the authorized_keys file:

$ chmod 400 authorized_keys

Now I tested the connection from my laptop:

$ ssh -i sqldev-key.pem sqldev@[aws host ip]

Now that the connection is verified, I setup SQL Developer to use the new key file for my database connection. See SQL Developer SSH Connection.


APEX 5.0: Enable RESTful Access to Report

June 13, 2016 at 5:11 pm | Posted in Oracle Developement | Leave a comment

Interactive reports can be enabled to be accessed through REST. Doing so is pretty easy if you know where to look.

  1. Make sure the page containing the report is public.
  2. Give the report a static ID
  3. Switch the page designer to Component View component.
  4. In the Attributes section set Enable RESTful Access to Yes.
  5. The access URL will be provided for you. Copy and paste it into a browser to see the report results in JSON format.



Raspberry Pi: Change Screen Resolution

June 12, 2016 at 6:45 pm | Posted in Oracle Developement | Leave a comment

This is just a quick & dirty re-blog of a handy article I found.



May 25, 2016 at 6:16 pm | Posted in Oracle Developement | Leave a comment

Here is a great post by Dimitri regarding HTTPS:

Dimitri Gielis Blog (Oracle Application Express – APEX): Please, use HTTPS for your APEX apps

Color Spectrum in APEX report

April 22, 2016 at 9:06 am | Posted in Oracle Application Express (Apex), Oracle Developement, PL/SQL | Leave a comment
Tags: ,

I recently had the need to display a color spectrum, depending on a column value, in a report. The colors had to go from red (column value zero) to green (column value 100).


To achieve this I added a formula to my query that would generate an RGB value based on the column value. I then used this value to render a circle of that color, with the column value inside of it.

Here  is the sample query for the report. The report has two columns:
VALUE: The value I wish to display.
VALUE_COLOR: The generated RGB color that gets used in the VALUE’s HTML Expression.

select level as value
      ,'rgb('||round((255 * (100 - level)/100),0)
       ||','|| round((255 * level)/100,0) ||',0'||')' 
       as value_color
from dual
connect by level <= 100
order by 1

I used some custom HTML in the Value column’s HTML Expression to render a Font Awesome circle in the color of the generated RGB value, with the column value inside.


That’s it. Have fun with it.





« Previous PageNext Page »

Entries and comments feeds.