OraNA :: Oracle News Aggregator

In 2013 the Insync Conference Series has expanded to include six capital cities within Australia, thereby bringing Insync to multiple east coast locations and incoporating the Oracle with 20:20 Foresight Perth leg.

The website has been updated, and the call for papers is coming to a close this Friday 24th (more...)

Today I found that SQL analytics go hand in hand with PIVOT statements.

I had percentage data pivoted by a certain column, but I needed to sort it by
the total across each row.

For the purpose of this demo I create a table with some random monthly data

create  (more...)

This isn't really an easter egg, just more of a mild bug that makes you think - hopefully a little more about your own applications.

If you type in "0" in the page navigation bar, it takes you to the typical global page / page zero for desktop.
Instead (more...)

Oracle Application Express for Mobile Web Applications

If you haven't heard about this book yet, you're not reading enough media on-line.

APEX regulars Roel Hartman, Christian Rokitta and APEX product manager David Peake are the published authors, but I think it’s safe to say we can also thank Dan McGhan (more...)

Recently I blogged about a nifty CSS only menu that is a great alternative to the horrible APEX tab-sets. Unfortunately I had to ditch it for a recent project because it didn't work well on touch screen devices, so I picked up Enkitec's Navbar plugin instead.

I wanted to freeze (more...)

I find APEX tab sets cause all sorts of issues in applications, either through management or behaviour. A common request is to create some pull-down menus as a replacement.
There are plenty of options for this, including a number of jQuery plugins, but here is an example that uses only (more...)

This post details the implementation of an extended version the Enkitec Sparklines Plug-in to include bullet charts. Previously I posted how this extension was made.

First you need to download and import the plug-in. Once this is done, you can define the data used, then create a dynamic action to (more...)

Dan McGhan recently announced a new plug-in using sparklines. This post details how I extended the plug-in to support another chart type. A separate post details how I implemented this in an application.

If you haven't seen them, check out his brief summary - there's a great image showing examples (more...)

Here is a simple example for when SQL Analytical Functions are simple yet useful.

I wanted a basic 1,2,3,4 count so I could alternate colours in a report.

select ename, sal, rownum rn
  ,mod(rownum,4) mod_rn
  ,mod(row_number() over (order by sal),4) mod_rna
from emp
order by sal

ENAME      SAL     RN     MOD_RN   (more...)

For those interested, here is a prezi-style presentation on why I think you should be upgrading your APEX environment to at least 4.x.
Hopefully you understand the messages without listening to my babble.

I presented this to our local Perth user group as part of a double header with (more...)

How ready is tomorrow's presentation?

...never to reach 100%

Create Chart page
Gauge - dial %
Look 5
Series source: select 95, 100 from dual

Edit Chart
Gauge pointer - bar
Uncheck Values
Major/minor interval 10/5

When creating LOVs for APEX I sometimes debate to myself whether to make a static or dynamic LOV.

I had one scenario where having some SQL was handy, so I started with this

SELECT TO_CHAR(NEXT_DAY(sysdate, 'MON')+ROWNUM-1,'DY')
FROM dual
CONNECT BY LEVEL <= 7;

It's possible to then place this as (more...)

Back in my Oracle Forms days, we had a library function associated with our date fields that accepted a value of "t", which then returned today's date.

We had further variations on this, but I thought I'd see how I'd go at implementing this in the APEX environment.

First, well, (more...)

Migrating data between environments sometimes requires the need to update the sequence next value.

I can't remember where I adopted this code, but I've had it for a while now and I've improved it a little.

create or replace procedure reset_seq
  (p_seq_name  IN VARCHAR2
  ,p_new_value IN NUMBER DEFAULT NULL ) IS
  l_val number;
begin
  execute immediate
  'select ' || p_seq_name || '.nextval from dual' INTO l_val;

  l_val := -l_val+COALESCE(p_new_value,0);

  --debug( 'alter sequence ' || p_seq_name || ' increment by ' || l_val ||' minvalue 0');
  execute immediate
  'alter sequence ' || p_seq_name || ' increment by ' || l_val  (more...)

I'd like to encourage all Perth AUSOUG members to come join us for breakfast at the West Perth Oracle offices on Wednesday March 27.

If you missed Connor McDonald in February you can make up for it with a double header with a focus on "why upgrade?"

11 reasons to standardise on Database 11gR2 
Mark Randell - Oracle

With the announcement of Database 12c at OpenWorld 2012, many people are holding back upgrading their database environments until they evaluate the latest Oracle Database release. However, with 12c introducing major architectural changes, 11gR2 will continue to be recommended as the (more...)

I've been using my local APEX install infrequently recently, but I have encountered occasional issues that I'm sure are attributed the the APEX Listener stand-alone.

Tonight I tried to import some plug-ins but received "Internal server error 500". I found a related OTN forum post with a recent comment saying it's solved in 2.0.1 - I'm pretty sure I didn't have that version.

So I just downloaded the latest APEX Listener and had immediate (successful) results.

Since I run the listener stand-alone, so all I did was unpacked the file to C:\apex_listener2.0.1, changed my "start apex (more...)

Many APEX developers would love to customise the first thing they see when logging in each day - the workspace login page.

Peter Raganitsch has done some awesome things for the APEX community, and one of them was to provide a script that can spruce up this page.

Trouble is, the APEX development team keep changing the look and feel of the environment, stuffing up training manuals world-wide ;-)

By default, the APEX 4.1 login looked like this

APEX 4.1 Workspace Login

 And 4.2 looks like this

APEX 4.2 Workspace Login

Last time I checked, Peter's (more...)

While experimenting with the APEX tree region, I came up with a use case that demonstrated some of the related features, as well as providing some useful information about the roles defined in my database.

I will note that to do so my parsing schema required access to DBA_ROLE_PRIVS and DBA_TAB_PRIVS - something that was fine in my development environment, but would be rightly questioned in a production scenario, but I will demonstrate nonetheless.

Final layout

My page looks like the following screen grab, and below I break down the steps to get there.

The tree lists roles assigned to (more...)

In Oracle APEX there are probably a few broad classifications of technologist, and I think to create plug-ins as an APEX shared component is probably one of them.

While I have consumed many plug-ins, I haven't created any that aren't security related. And just quietly, the security plug-ins are a fair simple encapsulation of a known entity.

If you want to learn how to create plug-ins using the tools available, I would recommend reading Expert Oracle Application Express Plugins, by Martin Giffy D'Souza.

I started a technical review for this book, but had to pull out due to poor (more...)

The Western Australian AUSOUG page has recently been updated to detail some activities for 2013.