Speakers looking for New Year's Resolutions?

If you're a semi-regular speaker looking for a new year's resolution, how about learning this little ditty?



I never opened the clip of Daniel Radcliffe doing the same song not long ago on Jimmy Fallen - I don't really watch his movies I skipped by, but I did get baited by something on Facebook mentioning the reporter's (Kim Powell) a capella warm-up method.

Turns out it's a 1999 rap by Blackalicious, lyrics for your practicing pleasure. I don't know if this is the original video clip, but it's a rather creative and would have been cutting edge for it's time.

Anyone up for doing this prior to Kscope15? ;p

Oracle 12c WITH inline PL/SQL

I've been having a bit of a play with the Oracle 12c database over the past few days and I thought I'd mention a gotcha I encountered.

Of course, oracle-base is a great place to start for clear & concise information on new features and I was trying out some of the WITH clause enhancements (a.k.a. subquery factoring clause). As a developer I'm pretty excited about these in particular.

Creating inline functions within a SQL statement was relatively easy.

WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(event_no)
FROM   events
/

However a slight adjustment is required for DML. The documentation suggests that

• If the top-level statement is a DELETE, MERGE, INSERT, or UPDATE statement, then it must have the WITH_PLSQL hint.

but does not give any examples, which I think is unfortunate - but thanks Tim for getting us started ;-)

UPDATE /*+ WITH_PLSQL */ events e
SET e.org_id =
  (WITH
     FUNCTION inline_fn(p_id IN NUMBER) RETURN NUMBER IS
     BEGIN
       RETURN p_id;
     END;
   SELECT inline_fn(e.org_id)
   FROM   dual);
/

Without the hint Oracle returns

ORA-32034: unsupported use of WITH clause

but I was getting

ORA-00933: SQL command not properly ended

What clued me in was the brief highlight SQL Developer makes over the statement before it executes. For me this paused at the return statement within the function.

I happened to be using one of the pre-built Oracle Developer VMs to play around, and it turns out the one I'm using has SQL Developer 4.0.0.13 supplied.

That particular version doesn't seem to be aware of this bleeding edge feature. I vaguely recall seeing this mentioned somewhere probably in the vicinity of thatJeffSmith fellow. I tried it in the command line SQL*Plus and it worked fine against the 12.1.0.1 instance.

It does ring a clear bell for once upon a time circa 2006 working on Oracle 9i or 10g when I sent an email to a colleague containing a SQL statement including a WITH clause.

He didn't have success in his Oracle 8i SQL*Plus windows client either... oh how I miss thee.

Boosting APEX menu SQL performance

If you're using dynamic SQL to source your menus and you're utilising the apex_application_pages dictionary view to build you list of menu options - I suggest you consider using Materialized Views.

We have a reporting application that does just this; and the query also links to an authorisation table that decides what types of users see which reports.

You can create a snapshot (materialized view) using something as simple as

create materialized view apx_application_pages
as select * from apex_application_pages;

Then you can add an index on application_id and page_id, then you have the perfect substitute for your menu SQL.

In our development instance containing all sorts of experimental applications - apex_application_pages returns about 1500 rows - but it's a relatively complex view. Using the snapshot instead of the supplied view makes the following difference in throughput when running our query 50 times (on dev)

    33.93 secs (.6786 secs per iteration)
     0.17 secs (.0034 secs per iteration)

Just don't forget to refresh it when your application grows.
exec dbms_mview.refresh('apx_application_pages');

A simple concept, but it can make a very positive difference when done right. It's also applicable to a number of other related scenarios.