Oracle APEX build options

Sometimes while developing for a release, components aren't ready for migration for whatever reason. You may also wish to make available development only pages, for instance.

Oracle Application Express has a feature called "Build Options" that are designed explicitly for this purpose. There are other methods of doing this, such as utilising authorisation schemes, but this is neater.
Here is a simple example of a build option defined in the application:

This option can be assigned to application components, such as pages, as follows (within edit page attributes)

This means that within the development environment, these pages are available.

When the application is exported, the components are present within the builder - but not available at runtime.

When I was first exploring this feature I got confused on this concept.:

I exported the application, checked the file - my component was there.

I imported the file, checked the application - it was there.

It wasn't until I ran the application that I noticed the components were missing. This allows you to configure further with your new application. In a way highlighting how important testing even when you're "sure" it works in theory.

The easiest way to think of the include/exclude options is as on/off, just a like a regular condition.

I think the documentation on the build option has improved since I last looked.

This file does absolutely nothing.

Now since I obviously didn't have anything else better to do on a sunny Saturday afternoon, I thought I'd share something I found while upgrading by 11gR2 database with Apex4.0.

Now, I have to give credit to Tyler Muth, whom put his name to this script that gave me a slight chuckle.

Now, unlike in that photo of Tyler, it's not snowing in our Perth winter, it never does - but last night it got a little chilly.

Anyway, don't ask but I stumbled on a file in the core folder of apex - null1.sql

The bit that made me chuckle was the comment

Rem    DESCRIPTION
Rem      This file does absolutely nothing.  It is simply a file that can be run when different scripts are conditionally run
Rem      and the desired effect of a condition is for nothing to happen.

After looking through seeing where it was used, I can see what a useful little catalyst it must be. I found one reference on OTN. But I wonder what the thought was around the table when they reviewed the need for that file.

Or should I go back outside ;-)

SIGNs of Friday

I just thought I'd share this thought that came to mind when looking for a solution to a simple problem.

I learnt DECODE before I encountered CASE, so often my brain thinks that way first.

Therefore, I came up with an expression that uses SIGN, which I'm sure you don't see often.

SELECT SUBSTR(product_description,1,40)
    ||DECODE(SIGN(LENGTH(product_description)-40),1,'...') product_description
   -- CASE WHEN LENGTH(product_description) > 40 THEN '...' END product_description
FROM oe.product_information;

The case equivalent is commented.

This place ellipses at the end of truncated strings longer than 40 characters.

Oracle Apex 4.0 has arrived

Gee, I take a night off from setting up my new laptop to finally watch Taken, and it takes until after lunch for an e-mail from Connor to let me know about the flood of entries in my RSS about the launch of Apex 4.0!

I was expecting a little more fanfare than just a newsroom entry. I thought it may have been paired with a conference - but I'm sure it will be the talk of ODTUG.

Joel announces it here.

OTN has the best starting point.

I was hoping for a vastly rearranged set of documentation,  that was my wish - but I'll see how the updated set of documents look.

New features is always a great place to start when looking at a new product from Oracle.

Now I'm even more undecided how to configure my laptop: 10g with Apex3.2, 11g with Apex 4.0 - but with what HTTP server?! Apex listener is still under Early Adopter...

In other news, apparently Australia has a new P.M...

Better practices within APEX Forms

When developing in Apex, sometimes it's good to check out the little tips, suggestions and reference information that pops up below
It's just a shame some of the examples the present show some better practices, even in Apex 4.0.

SELECT MAX(empno) + 1 ?

How many years have we been telling people to never use that in their code?

And if you select "Existing sequence" you get an ugly looking page process with

declare 
  function get_pk return varchar2 
  is 
  begin 
    for c1 in (select EMP_SEQ.nextval next_val
               from dual)
    loop
        return c1.next_val;
    end loop;
  end; 
begin 
  :P1_EMP_NO := get_pk; 
end; 

As a better alternative, select "Custom PL/SQL function", or if you want to obfuscate it within a trigger you could do it as follows:

CREATE OR REPLACE TRIGGER emp_bi
BEFORE INSERT ON sage.emp
FOR EACH ROW
BEGIN
  IF :NEW.emp_no IS NULL THEN     
    SELECT emp_seq.NEXTVAL 
    INTO :NEW.emp_no 
    FROM dual;
  END IF;
END;
/

With the key line here being 5 - only source from the sequence if the emp_no is not already supplied. If your table is also being populated from another source, it would be more efficient to use
INSERT INTO emp (emp_no, ...) VALUES (emp_seq.NEXTVAL, ...);

I think within the context of most applications in Application Express, where your users are performing data entry at a page level, I don't think it matters if the sequence is populate like this in a page process or trigger.

Also not that in Oracle 11g, in lieu of the implicit cursor you can use
:NEW.emp_no := emp_seq.NEXTVAL
However, last time I ran a trace on this, it was the same as selecting from dual - handy, not a performance improvement.

Any other preferred methods?

Update March 2016
Sven Weller provides the perfect APEX 'trigger'

Conditional Compilation in 11g

If yesterday's post got you in the mood to investigate Oracle 11g, here is a quick demonstration of the resultant end of your conditional compilation usage.

CREATE OR REPLACE FUNCTION return_junk RETURN dual.dummy%TYPE
$IF dbms_db_version.ver_le_10 $THEN 
$ELSE RESULT_CACHE $END 
IS
  lc_dummy  dual.dummy%TYPE;
BEGIN
   SELECT dummy
   INTO   lc_dummy
   FROM   dual;
 
   RETURN lc_dummy;
END return_junk;
/

In the accompanying image, you can see I've determined what the interpreted code will be, compiled in 11g, the RESULT_CACHE feature is present. Had I compiled this in 10g, line 2 & 3 will be completely clear and you would not have 11g technology causing syntax errors in your 10g database.

Of course, so you can remind yourself during the migration to 11g to test these concepts in development first, you would code with an error directive:

CREATE OR REPLACE FUNCTION return_junk RETURN dual.dummy%TYPE
$IF dbms_db_version.ver_le_10 $THEN 
$ELSE RESULT_CACHE $END 
IS
  lc_dummy  dual.dummy%TYPE;
BEGIN
$IF dbms_db_version.ver_le_10 $THEN 
$ELSE
   $ERROR '11g upgrade in process. This component needs further testing'
   $END
$END  

   SELECT dummy
   INTO   lc_dummy
   FROM   dual;
 
   RETURN lc_dummy;
END return_junk;
/

Which on compilation will give
Error(9,4): PLS-00179: $ERROR: 11g upgrade in process. This component needs further testing

At least you added in the code when you became aware of it a it didn't get forgotten about!

It's a great time to learn about PL/SQL!

Yesterday I talked about a few good links I've seen for people who are interested in or want to learn more about Apex. Another good place to start is documentation.

Today I thought I'd offer some of the developer blogs that have stimulated my interest recently.

I think it was through Tom Kyte that I found Seth's blog. This entry about deadlines typifies his style - concise thought experiments to make you think about either your work or home life, however you'd like to apply it.

Tom also mentioned if you're in contact with 11g, it's simply amazing some of the neat little features popping up. You may only use these once in a blue moon, but if you're aware of it, I bet sometimes you're proud how elegant it is.

Even if you're not yet in reach of 11g, it doesn't hurt to learn about it in places such as the PL/SQL Challenge (but don't cheat, I think yesterday's question on bulk collect semantics was either aimed at the speed readers or ...) And you can start using these features today with features such as condition compilation.

Speaking of Steven, here's his new presentation "Golden Rules for Developers". In the vain of 2nd rule of Fight Club... perhaps an addition I'd like to suggest is "being able to explain your interpretation of the reason why these are golden rules" - especially before you read his presentation, based on the list Eddie Awad summarised.

For the visual people, Jeff Kemp reminded me me of the Volkswagen VW that was photographed with "Feature" as it's number plate. And for a random afternoon quiz, local Gary Myers offered this titbit.

For something deeper, Dutchman Alex Nuijten offered this about a bug bear of mine - implicit data-type conversion, well, how it can impact check constraints. Steven talked about another preach I practice - not using select  * in my code.

It's awesome how much good content there is out there, this just scratches the vast Oracle community.

Now is a great time to start learning Apex!

Martin really took the words out of my mouth - although the activities that have been consuming my time haven't been nearly as fun - although I did fit a second honeymoon in there somewhere.

Like he says, I haven't forgotten about the blog - I have a growing list of topics sitting in my in-box ready to type out. I just need to fit it in between working on my Apex with Oracle Text presentation for next month's AUSOUG branch meeting from 16:30 on July 14th (what a great segue!) and of course doing my regular job consulting & training for my wonderful employer.

In the meantime, I have been able to slowly catch up with the multitude of Oracle posts flooding my RSS feed. Over the past few months there has been some great highlights, and it seems as the public release of Apex 4.0 is approaching, now is a great time to start learning Application Express.

First of all, earlier this month we have Oracle Apex product manager David Peake announcing http://apex.oracle.com has been upgraded to a pre-release version of Apex 4.0. This is a major step towards the final release and is very exciting news indeed.

Secondly, we have Dimitri Gielis pledging to blog every day about Apex 4.0 features until the public release. Good onya mate! He is using his Soccer World Cup tipping application as a case study for migrating from 3.2 to 4.0, which will make for interesting reading.

If you need a break from learning Application Express and just want to learn some random facts about PL/SQL, whilst at the same time giving yourself the opportunity to win a prize, why not visit Steven Feuerstein's PL/SQL Challenge (which happens to be written with Apex). Developers around the world are tackling his daily questions in a variety of ways, from researching the answer to ensure accuracy to relying on general understanding or guessing. Of course in the real world we would attempt to thoroughly study and understand the relevant feature before using it in production - but I like to use the questions as a way to test my tip-of-my-tongue knowledge. Sometimes I do well, sometimes I come across features I've had little exposure to. This is great because not only to I learn something new as Steven sometimes provides further commentary on the answers, I get to test how well I can apply my understanding on PL/SQL on features I'm not familiar with.
He also has a blog that solely serves this challenge, responding to various questions about the daily quiz. Good work Steven!

Now you've had that break, why not open up some Apex 4.0 Oracle By Examples? I look forward to this list growing and bookmarking this high on the list as a valuable resource for learning the new Apex features.

In other news, Oracle 11g Release 2 for Windows has been released. I have just finished installing the 64 bit version on my new laptop (another reason for blogging delays) and I look forward to further tinkering.

Now for all these links, these are only a few of the good Apex posts. Later I'll list out some of the good Oracle developer links - those who are proud of their SQL &  PL/SQL.