LISTAGG to a CLOB, avoid 4000 chr limit

Thanks to twitter I found this post by Carsten Czarski on LISTAGG and CLOBS that helped my with the 4000 character limit with LISTAGG(), found when building JSON strings.
ORA-01489: result of string concatenation is too long

I follow a few bi-language blogs but I do wonder if English speakers may find this post when googling the issue. For me it's on page 1 when googling "listagg clob", but I knew what keyword to search (more...)

Introducing the Official LinkedIn APEX Group

Once upon a time I had a laugh on facebook when someone posted an image showing multiple groups dedicated to stopping duplicate groups, oh the irony.

Recently I've been trying to share my blog posts via LinkedIn but I'm always unsure which of the seven APEX groups I'm a member of I should post to.


It turns out only the SIG groups are owned by those involved with the APEX development team, namely David Peake (more...)

APEX Community Sample Applications

There are a bunch of sample APEX applications out there built by developers (typically with blogs) that are darn useful, but so far I haven't seen a useful central repository.

So I've started one here with a very basic form/report:
https://apex.oracle.com/pls/apex/f?p=73000:APEX_SAMPLES

At the moment it's just a list of those I happened to have bookmarked over time, in one place that others could use and perhaps contribute to.

Depending on feedback I (more...)

2014 Annual SQL Championship

Steven Feuerstein runs a great site at PL/SQL Challenge that is just another way for developers to stay up to date with their knowledge of PL/SQL, SQL and database design with a bit of fun.

PL/SQL championships are held quarterly, but the less frequent SQL and Database Design quizzes are currently held annually. Thanks to persistence and maybe a bit of experience, I was eligible to compete in both.

Unfortunately do to a timing issue, (more...)

Twitter as a work tool

Scott Spendolini conducted a little experiment last week, then Jeff Smith & Kellyn Pot'Vin whipped up a little slideshow on the topic.

I thought I'd chime in after some recent experiences were helped along by the use of Twitter, a tool it seems many are underutilising.

Why?

Exhibit A

I had an afternoon of generating JSON with SQL using LISTAGG and I kept facing this issue of being limited by 4000 characters. Knowing I occasionally (more...)

APEX Best Practices Survey

Hi All,

I was inspired by Peter Raganitsch to use the Survey Builder packaged application to create a survey that will help with my "Evidence Based APEX" presentation.

I would appreciate 5-10 minutes of your time if you could fill out my survey:

https://apex.oracle.com/pls/apex/f?p=70347:Q::AAC3
How-to Geek

You'll find a small set of questions over five major topics.

  1. Instrumentation (debugging)
  2. Performance
  3. Security
  4. PL/SQL
  5. JavaScript
In time I'll discuss the results, I look forward to reading your responses.

Scott

2014 Blog Review

Yup, it's February. Early adopter 3 for APEX5 has just become available, and I've just logged of an APEX 3.2 instance.

I've had this sitting my drafts for some time (it has company), thought it best I finally finish it off an move on.

Considering I fathered an awesome girl in January with my bare hands, surrounded by kangaroos with warm pouches waiting (some facts may have been distorted in this story), I think (more...)

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

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 (more...)

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 (more...)

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 (more...)

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 (more...)

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 (more...)

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 (more...)

Perth 2014 Professional Learning Event

There's a little over a week left to this year's major Oracle user group event in Perth, so attention all locals (or even east coasters) - if you're interested in picking up new skills & ideas - you haven't got much time left to register!

This year it's called a "Professional Learning Event" instead of a conference and the program reflects it - plenty of hands on workshops mixed in with normal sessions. Considering it's (more...)

ODTUG Webinar: APEX 5 Page Designer

I'll be presenting an online webinar for ODTUG not long after the Perth conference.

It will be the same session I did for AUSOUG back in July, which is lucky since it's 6am for us Perth locals. It's a close look at the Oracle APEX 5 Page Designer, and what it means to you as an APEX developer.

Check the webinar registration page for details on the abstract and help with the time zone (more...)

PL/SQL Challenge Roundtable

Do you write PL/SQL? Me too!

Trouble is, sometimes it's hard to decide how to structure your packages - particularly in an APEX project. Over at the PL/SQL Challenge website run by Steven Feuerstein and friends there is a page dedicated to roundtable discussions.

I submitted my question not so long ago and hope to get some interesting responses. Why don't you give the site a visit and contribute? The discussions usually last for about (more...)

The moon is a sphere

I was fairly excited at the prospect of seeing tonight's lunar eclipse but I neglected to take into account not being able to see the eastern horizon from my house.

I missed most of the total but enjoyed the crescent of the partial eclipse re-emerging. It's amazing how distinct the shadow leading to the penumbra.

My friend said there will be some good shots in tomorrow's paper. I laughed and said here in the 21st (more...)

APEX 5 not so near

It seems I may have jumped the gun a little.
Idioms - the bane of ESL speakers?

Reading Anton's comment on a Friday afternoon got me a little too excited - upon reading it again Tuesday (we had a long weekend) I realised it was tongue in cheek.

So instead I went to Twitter to find what's being said at Oracle Open World about #orclapex. Turns out there will be a third early adopter - (more...)

APEX 5 imminent?

It seems the release of APEX 5 is due within hours - just in time for OOW weekend.

To quote Anton here
Apex 5.0 is going to be released in a couple of hours
It's a long weekend here in Perth, and I have a footy grand final to watch - but no doubt I'll be installing it somewhere soon!

All that being said, apex.oracle.com is still on 4.2.5, so don't hold your breath for too long.

Stay tuned...