Pages

Tuesday 19 November 2019

APEX Low code expressions

The following where clause expression would normally return false, therefore the query would deliver no rows.
select * from dual where null != 'X'

no rows returned
A null can never equal a value, and hence cannot be determined to be disimilar to another value. False is the expected condition.

Turns out when using it as what appears to be the  declarative low code alternative, it returns true – and renders.

So if you want to display something based on a field that may have a value you don’t want, eg:


This will display the component if P10_NULL is anything but "yuck", including a null value.

In my business case, there was an occasional value of "D" that I need to hide things for.

Instead of using (slower) PL/SQL expression
nvl(:P42_CAT,'x') != 'D'

or SQL expression (anything but = D)
lnnvl(:P42_CAT='D')

As Kim suggests, our brain is seeing something SQL-ish, when really it's a low code translation that probably nails most scenarios - and maybe it would be better re-worded slightly as "not equals"

Our low code alternative is not only faster, but simpler. It turns it into just another if statement, as opposed to a snippet of PL/SQL that's interpreted on the fly, which is why dozens of global page conditions using PL/SQL expressions will slow your application.

Tom Kyte's mantra was to aim to reduce to just a single SQL statement (or no SQL at all).
Perhaps APEX developers should advocate for declarative attributes where possible, only then maybe try an expression or query.

2 comments:

  1. " It turns it into just another if statement, as opposed to a snippet of PL/SQL that's interpreted on the fly "

    What do you mean, does snippet of PL/SQL are interpreted on the fly ? and the if statements does not get interpreted on the fly ? what is the difference. can you explain please.

    Thanks and regards.

    ReplyDelete
  2. By 'on the fly', I mean dynamically executed code, vs code that has already been compiled.
    https://oracle-base.com/articles/8i/native-dynamic-sql

    Here is another overview of the potential performance difference.
    https://asktom.oracle.com/pls/apex/asktom.search?tag=dynamic-sql-vs-static-sql

    ReplyDelete