How to Reference Package Variables in SQL

A long time ago (pre-12c) I wrote about How To Reference Package Variables Outside of PL/SQL. This technique used an execute immediate function to reference a given package variable. Another alternative at the time was to create individual get functions for each variable.

Starting in Oracle 12c you can directly use PL/SQL in SQL and thus reference package variables in SQL. The following example shows how:

create or replace package pkg_demo as
gc_first_name constant varchar2(255) := 'Martin';
end pkg_demo;
function get_name return varchar2 as
return pkg_demo.gc_first_name;
select get_name() my_name
from dual;
-- Will return

You can also use this concept in views.

How to Reference JavsScript and CSS Files for Entire Application

Kim Mertens recently posted the following question today on Twitter: Where to put JS-file that has to be loaded on every page in #orclapex 5.0? Tried “Global Page - Static region” but jQuery isn’t loaded yet

The answer isn’t as straight forward as you think. Most people are used to referencing web files (JavaScript and CSS files) on Page 0 from legacy APEX 4. As Kim hinted at in his question this isn’t always the best thing to do.

In APEX 5 there’s a specific area dedicated to reference custom web files. It’s not the easiest thing to find at first (it takes me a few tries to find it sometimes if I haven’t used it in a while).


New Blog Platform Based on Markdown and Git

If you’ve followed this blog for a while you’ll notice that I had a significant drop in articles over the past year. So much so that I went from 35 posts in 2015 to just 4 posts in 2016. They’re various reasons for the reduction of posts which all came down to time, more specifically the lack of it.

I realized that I had the time to write the content for posts but I didn’t have the time to deal with all the formatting that was required on the platform (which used to host It’s a great platform when writing basic text articles however when adding code samples it becomes an absolute pain to manage code formatting, especially editing an existing post.

Since working on many open source projects I’ve discovered Markdown which is an easy-to-write plain text format that has special syntax for developers. If you’ve never used it I highly recommend that you look into it as it’ll change how you write technical documentation.

Over the weekend I moved this blog from the Blogger platform to Hexo and its now hosted on Github Pages. This allows me to quickly write my blog posts in Markdown, manage the entire blog as a git repository on Github, and use Github Pages to host the site. If I find some time I’ll try to write about the migration process.

A few notes on the new

  • Older posts may not be formatted correctly. I’m working towards cleaning them as time permits
  • Comments were not retained and the site is using a new comment platform: Disqus
  • Tags have all been cleaned up
  • All the old URLs should work
  • If you need to reference a comment or view the old site you can do so here.

If you do find something out of place or a broken link let me know or leave a comment on the article.

Going forward I’m hoping to write a lot of new posts quickly with this platform and time will tell if this migration solved the goal of allowing me to write more in 2017.

Special thanks to Adrian Png for introducing me to Hexo

APEX OOW 2016 Meetup

Each year the Oracle APEX community gets together at Oracle Open World (OOW) to have a few beers and catch up. This is a great informal event and its your chance to network with a lot of people in the APEX community and meet some of the gurus and members of the core APEX development team.

We’ll be having the annual APEX OOW Meetup at Johnny Foleys on Tuesday September 20th at 7:30 onwards. Please let us know if you’re coming by signing up on the Meetup for this event.

If it’s your first time attending, we’re in the basement and there will be dueling piano’s. Show up early as it gets packed pretty!

243 O’Farrell Street
San Francisco, CA 94102

Looking forward to seeing everyone there!


ODTUG Leadership Program

ODTUG has long been recognized as an outstanding source for the technical content Oracle professionals desire, but that’s only half of the story. We’re providing our members with yet another avenue to add to their skills and increase their value to their employer or client. ODTUG is pleased to open enrollment for the fourth class of the ODTUG Leadership Program

I’ve had the opportunity to work with some of the classes and individuals from the Leadership Program and have heard some amazing stories on how this program changed peoples lives and carriers!

If you, or someone you know, is interested I encourage you to submit an application. Applications must be submitted by August 14th so hurry up!

APEX and the Nest Thermostat

At ODTUG Kscope 15 last year I gave a presentation about APEX plugins. During the presentation I built a plugin from scratch to control my Nest Thermostat. Everything went well until I did a live demo connecting the APEX plugin with my home Nest. I forgot to enable a service and unfortunately the demo didn’t work.

I recently gave a talk to the Vancouver Oracle User Group on APEX and the Future of Enterprise Development. During this presentation I pre-recorded a video of APEX controlling my home Nest to highlight that APEX can work with the Internet of Things (IoT).

There has been some interest to see this in action so I have included a copy of the video below. On the right is a Nest plugin that I wrote for APEX, the left is the Nest app on an Android tablet, and in the center is the actual Nest.

Eh?PEX Unofficial Tour

This year I will be taking some trips across Canada and have been given the opportunity to speak in various cities. Thus the unofficial Eh? PEX tour.

Here are the different cities and links to each event:

Special thanks to Insum Solutions for helping make this all possible.

For those located in Calgary we have our monthly YYC APEX Meetup group.

ClariFit From/To Date Picker Plug-in Moved to OraOpenSource

This article is for people looking for help with the ClariFit From/To Date Picker.

I’m currently in the process of updating some of the plugins I created for ClariFit to support APEX 5.0. As part of these changes some additional things are happening, mainly listing the plugins in the new site and moving the plugins ownership over to OraOpenSource.

The first plugin that has been moved/updated is the ClariFit From/To Date Picker. The plugin is now called OraOpenSource From/To Date Picker and is hosted here. To help with backwards compatibility the plugin’s internal name has remained the same. This means that upgrades will be seamless.

Please post all bugs and enhancement requests on the project’s issue page.

The next plugin that will be updated is the ClariFit Dialog (to be called OraOpenSource Dialog). It is currently being tested and will be officially released pending any major bugs. If you’d like any other ClariFit plugins updated to APEX 5.0 please leave a comment and I’ll look into it.

ODTUG Technical Journal - Authors Wanted

If you’ve ever thought of starting your own blog or wanted to write a detailed article on some cool APEX or Oracle feature but didn’t know where to start, now’s your big chance! The ODTUG Technical Journal is looking for some new articles and we’d like to see articles from first time contributors.

Signing up is easy. Simply email Karen Cannell ([email protected]) and let her know that you’re interested in writing an article for the journal. Then you can work out a schedule with her. After that, all you need to do is write the article, Karen will take care of the rest.

This is a great opportunity to get your name out there and also promote what you and your organization have been been working on. I’m looking forward to seeing some great content from the APEX community.

ODTUG Election

If you were at Oracle Open World a few weeks ago you may have missed that the ODTUG Board of Directors election results were posted.

The newly elected directors are:

I first must thank all the people that voted for me again this year. It means a lot that I have the support of the APEX and Oracle communities

They’re a few board members who will not be returning next year. They all have done a lot for ODTUG and its members.

  • Monty Latiolais
    • Monty has been the President of ODTUG for the past few years. He’s done an amazing job leading the organization. Monty’s lasting legacy at ODTUG will be the Leadership Program. He took a risk trying it out and it has been a very big success helping develop leaders within the ODTUG community.
  • David Schleis
    • David has helped re-energize the traditional developer and DBA communities. Over the past two years he helped merged them into one united “database” community. I look forward to David to continue help out with the database community in the coming years.
  • Mia Urman

I also need to say a special thank you to the current board for supporting me over the past year. Last year was a very difficult time for me personally and they all stepped up and supported me along the way. This is something I will never forget and will always be thankful for.

As ODTUG has a six year term limit for directors, this will be my last two years. I look forward to making them as good as the first four. If you have any feedback or things you’d like to see happen within the community please let me know.

– Martin