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.