Tuesday, 13 January 2015

APEX5 Early Adopter 3 is nigh

I get this email that tells me apexea.oracle.com is about to be fitted out with an even slicker APEX5 Early Adopter 3.
It's coming soon to a browser near you
Stay tuned...

Update 2014-01-29 - I received an e-mail yesterday (along with 6k+ others) saying that EA3 will be available tomorrow and February will be an awesome month for playing with APEX5.

Friday, 2 January 2015

Oracle 12c column upgrades

While playing with 12c I tried the upgrade to the DEFAULT column syntax that now allows sequences.

I came across a basic error, but it's just a small trap for new players.

CREATE TABLE seq_test(a NUMBER)
/

ALTER TABLE seq_test MODIFY (a NUMBER DEFAULT sage_seq.NEXTVAL)
/

SQL Error: ORA-02262: ORA-2289 occurs while type-checking column default value expression

*Cause:    New column datatype causes type-checking error for existing column
           default value expression.
*Action:   Remove the default value expression or don't alter the column
           datatype.

Investigating ORA-02262 returns next to nothing. It's a red herring anyway because I didn't realise the ORA-2289 staring me in the face before I checked if my database had that sequence yet.
ORA-02289: sequence does not exist

Typical APEX table definitions will never be the same again, though an even more elegant solution is to use IDENTITY columns. See the performance benefits at oracle-base.com.

Wednesday, 31 December 2014

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

Tuesday, 30 December 2014

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 DELETEMERGEINSERT, 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.

Friday, 5 December 2014

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.

Wednesday, 26 November 2014

Chrome Device Mode and Mobile Emulation

Recently a colleague suggested I take a fresh look at the mobile emulation button in Chrome's developer tools.

Apparently this was upgraded earlier in the year and it's a shame I didn't have this a year ago!

Chrome Developer Tools Mobile Emulation
Some immediate advantages I see for APEX development:




  • Specify the device you'd like to emulate from a decent range of contemporary devices
    • This viewport can then be easily scaled to suit whatever monitor you're using
  • Apply network throtttling to simulate access on a mobile network
  • Use the emulation tab to define a media type to render as - I've been using 'print' to test how certain pages look when printed
  • The cursor changes, indicating the behaviour will emulate finger touches on a touchscreen
    • touch & drag emulated perfectly - I removed touchpunch from my page and sliders stopped working as expected without jQuery mobile
    • scrolling drifts instead of static desktop movement (happy to improve terminology here)
Full details on how to use these features are available here: developer.chrome.com

Tomomi also has a great write up on the topic.

Nice work Chrome, nice work.




Friday, 21 November 2014

ODTUG from afar

I would like to extend my apologies to those people who persevered just now with our attempt to bring to you my presentation on APEX 5 Page Designer thanks to ODTUG.

Due to the massive time zone differences, the appropriate time on my end (6am) means I connect from home - where I'm limited to an ADSL connection. My location is even a bit of a dead zone for my smartphone's 4G plan which is normally better than my home internet speed. I considered just tethering the laptop to my phone, but I don't think it would have been an improvement.

After testing the audio last month it seemed things would be fine as long as I didn't use Prezi (not powerpoint), which hogged the connection with the visuals updating - but obviously the gremlins had other ideas today.
Blame him or Tony Abbott
Melissa from ODTUG has suggested we try record it another time so you don't miss out. In the meantime you can view the slides yourself here, you just don't get to enjoy my Australian accent attempting a few jokes - particularly at the start.

Next year I plan on attending Kscope in Florida, where I've submitted a few abstracts. I think we'll hear who's been accepted next month - but I look forward to meeting so many people I've collaborated with online in some form.

Stay tuned!