Where would we be if we just believe?

As a science aficionado, there are certain phrases that ... catch the eye.

Recently on twitter there was an interesting thread that continued from Michelle Skamene's post on Top 15 Tuning Tips for APEX.  Michelle provided a wonderful follow-up post summarising the outcomes of the thread.

Point 9 suggests we avoid HTML in our queries, and use HTML expressions. This is undeniably good practice, but there was a question regarding how much performance is gained. Patrick Wolf summarises it well (sorting, XSS, context switching).

I've been curious about this for a while myself, and what better way to verify the truth than do some science ;p

I thought about the recent experimentation I did with interpreted code, and recent client work I've done with bind variables, so I thought I'd not only compare timings with a simple test harness I use, but see what v$sqlarea had to say.

When considering what SQL to compare, I decided to use the simple bit of SQL I used for my AskTOM Office Hours demo app. I saw how quickly the embedded HTML expanded to fit repeated business rules, and why not use something simple. If any difference was to be seen, let's see if it shows up with something basic, just like Juergen suggested.

The first query is just a simple query on scott.emp, with an extra expression to determine if the row is 'special'. This information is used declaratively within APEX, so these calculations are absent from this testing. The complexity has been transferred, but simplified. That's the reason it's best practice.

cursor c_1 is
select /*+ qb_name(c1) */ empno, ename, job, hiredate
  ,case
   when hiredate < date '2000-01-01'
     then 'special'
   end last_cent
from scott.emp

So, let's just concentrate on the differences in the SQL we can measure.

The second query has the HTML tags embedded, repeated on columns where I substituted the column into the class attribute in the previous example. The code required expands quickly, hence the argument to keep the SQL and presentation layer separate.

select /*+ qb_name(c2) */empno
  ,'<b>'||ename||'<b>' ename
  ,case when hiredate < date '2000-01-01'
    then '<span style="color:purple;font-weight:bold;">'
     ||'<span title="'||job||'">'||to_char(hiredate,'fmddth Mon YYYY')||'</span>'
   end ||'</span>'
   as hireDate
  ,case when hiredate < date '2000-01-01'
     then '<span style="color:purple;font-weight:bold;">'||job||'</span>'
   end job
from scott.emp

The third query introduces context switching to PL/SQL due to the inclusion of htf.bold and apex_escape.html. This protects the output we'd otherwise have escaped using the relevant property. Or you could mitigate usage by sanitising data on the way in.

cursor c_3 is
select /*+ qb_name(c3) */empno
  ,htf.bold(apex_escape.html(ename)) ename
  ,case when hiredate < date '2000-01-01'
    then '<span style="color:purple;font-weight:bold;">'
     ||'<span title="'||apex_escape.html(job)||'">'||to_char(hiredate,'fmddth Mon YYYY')||'</span>'
   end ||'</span>'
   as hireDate
  ,case when hiredate < date '2000-01-01'
     then '<span style="color:purple;font-weight:bold;">'||apex_escape.html(job)||'</span>'
   end job
from scott.emp;

If we just compare throughput, there is a clear loser. Context switching between SQL & PL/SQL is just too much, though I would like to think in recent versions of the database, those functions could be enhanced with such devices as the UDF pragma?

iterations:50000
     3.98 secs (.0000796 secs per iteration)
     5.16 secs (.0001032 secs per iteration)
    33.09 secs (.0006618 secs per iteration)

I just run these queries lots of times to measure throughput. Tom Kyte wrote a more enhanced test bed called runstats, if you want juicier details.

-- and start timing...
    l_start := dbms_utility.get_time;

    FOR i IN 1 ..c_iterations
    LOOP
     for x in c_1
     loop
        null;
     end loop;

    END LOOP;
    l_end := dbms_utility.get_time-l_start;
    dbms_output.put_line( TO_CHAR(l_end/100, '99990.00') 
                       || ' secs ('||(l_end/c_iterations/100) || ' secs per iteration)' );

What about v$sqlarea?

select substr(sql_text, 1, 22)sql, fetches, parse_calls, buffer_gets, sharable_mem
   ,persistent_mem, runtime_mem, user_io_wait_time
   ,plsql_exec_time, cpu_time, elapsed_time, physical_read_bytes 
from v$sqlarea  
where sql_text like 'SELECT%SCOTT.EMP%'
order by 1

v$sqlarea results

These statistics validate the timings, and I think also validates this as a performance tip that belongs in Michelle's list.
While performance difference with/without HTML may be marginal in this case, the supplementary benefits make it clear best practice.

Interpreted code in APEX

A few years ago I posted a comparison between plugin code left in the source attribute, vs code that has been transferred to a PL/SQL package.

In the interests of good science, and I wanted to chat about it at next week's Office Hours, I wanted to repeat this test.

I had a little difficulty working out how I got the metrics, I think APEX debugging has changed a little since I ran the test. Instead I considered looking at v$sqlarea to assess performance.

Turns out I quickly found the relevant queries using the following SQL, which a case statement to help me identify the difference between rows each time

select
  case
  when sql_text like 'begin declare  begin wwv_flow_plugin_api.%' then 'API'
  when sql_text like 'begin declare function x return varchar2 is begin return null; %' then 'call dynamic'
  when sql_text like 'begin declare FUNCTION enkitec_sparkline_render%' then 'parse dynamic'
  when module = 'SQL Developer' then ' me'
  end which
  ,executions
  ,loads
  ,parse_calls
  ,disk_reads
  ,buffer_gets
  ,user_io_wait_time
  ,plsql_exec_time
  ,rows_processed
  ,cpu_time
  ,elapsed_time
  ,physical_read_requests
  ,physical_read_bytes
  ,lockeD_total
  ,pinned_total
  ,sql_text
from v$sqlarea
where sql_text like '%sparkline%'
order by which

When APEX invoked the code using an API call, it looked like

begin declare begin wwv_flow_plugin_api.g_dynamic_action_render_result := apx_plug_sparkline.render (p_dynamic_action => wwv_flow_plugin_api.g_dynamic_action,p_plugin => wwv_flow_plugin_api.g_plugin );end; end;

When APEX needed to parse the entire function, it looked like

begin declare FUNCTION enkitec_sparkline_render ( p_dynamic_action IN APEX_PLUGIN.T_DYNAMIC_ACTION, p_plugin IN APEX_PLUGIN.T_PLUGIN ) RETURN APEX_PLUGIN.T_DYNAMIC_ACTION_RENDER_RESULT IS

To do this test, all I needed to do was paste the PL/SQL back into the source attribute; I didn’t bother changing what was invoked in the callback fields.

After 50 page refreshes each in dev – parsing the code was at least twice as slow, based on CPU time.

I suspect the 'call dynamic' was the builder validating the code.

Basic glimpse

In an environment with more activity, I was able to compare the standard API call with a few hundred that included parsing the code.

Click/tap to embiggen

If I take 141312 CPU time, divide by 301, then multiply by 17778, I get parsing around 1.8x the amount time as using the API.
The same goes for elapsed time, while the PLSQL Execution time remains the same.

Plus there’s disk reads, an obscene amount of buffer gets, considering the execution ratio.

I tried a second plugin (nested reports), and while the CPU ratio seemed the same, the physical reads were over twice as high.

Remembering this is just placing the code in the box – I haven’t even referenced it.

Too many words and numbers? How about a graph.

If this seems a fair reason to reduce the amount of interpreted code you have…

Twice the work, for nothing.

... then how does this make you feel?

What's a buffer, and why do we want to get it?

It seems by removing the code from the source attribute of the plugin, we measurably reduce the amount of work the database does. Imagine if we reduce our PL/SQL usage throughout the application?

So I conclude

1. Use bind variables
2. Put your code in packages

On the PL/SQL you don't write when using APEX.

Fancy joining in on a discussion with PL/SQL and Oracle APEX community members from around the world?

I'm honoured join Karen Cannell and Scott Spendolini, to be hosted by Steven Feuerstein in the next AskTom PL/SQL Office Hours on December 3, 2019.

It seems a few people haven't heard of these "office hours" sessions, but they're worth a go - more than just your average webinar. And they're all recorded for later viewing.

Topic

This is just the PL/SQL sessions! Every month they have one for Oracle APEX, SQL, Spatial, JavaScript, JSON, Database Security, and even more.

Product segments

It's probably one of the richest learning resources Oracle currently produces, and I really need to tap into more of it.

If the timezone gods allow it, I recommend live participation, as you really do get your questions answered. The produce managers handle the chatline, ensuring questions get followed through.

I think Steven's ensuring we have a good 15 minutes for questions, because they always come through. They keep the chat transcripts, too. The one for Oracle Forms Modernisation was all about the questions!

And it's free - you only need to sacrifice your email address, though it's probably the best email feed I have. Concise, infrequent, except my forum feed, of course.

On December 3, in my 15 minutes of fame, I plan to (spark debate?) about "the PL/SQL you don't need to write". I delivered this as an APEX application, instead of whipping out the ol' powerpoint.

Join in next week, and Karen will start with some decent habits, and the other Scott will no doubt enlighten us about security, or some such.

Free training - from Oracle... well, in this case, delegated to some passionate volunteers. ;p

Function based tables

You've probably seen this somewhere already, no doubt from Connor, though I couldn't find much beyond Tim's post on pipelined functions - I can't find the right keywords to find related content.

I like table functions, so this will help me remember we no longer need to specify the table() operator in 18c (12.2).

12c

select * 
from table(
  apex_string.split('A,B,C',',')
);

Result Sequence                                                                                                                      
---------------
A
B
C

12c> select * from apex_string.split('A,B,C',',');

ORA-00933: SQL command not properly ended

18c

select * from apex_string.split('A,B,C',',');

Result Sequence                                                                                                                      
---------------
A
B
C

This apex_string package is like the swiss army knife of string manipulation. I love it.
I think I'm surprised a few other posts haven't made it out of draft. This example felt a little like butchery, but it was an interesting play.

Here's a way to test it out with your own function, returning a supplied APEX collection type - a table/collection of strings.

create or replace function tf_test return apex_t_varchar2 is
  lt   apex_t_varchar2 := apex_t_varchar2(); -- ORA-06531: Reference to uninitialized collection
begin 
 for i in 1..12 loop
   lt.extend; -- ORA-06533: Subscript beyond count
   lt(lt.last) := add_months(trunc(sysdate,'yy'),i-1);
 end loop;

  return (lt);
end;
/ 

select column_value as dt 
from tf_test();

DT
---------
01/JAN/19
01/FEB/19
01/MAR/19
01/APR/19
01/MAY/19
01/JUN/19
01/JUL/19
01/AUG/19
01/SEP/19
01/OCT/19
01/NOV/19
01/DEC/19

12 rows selected

If you don't include the section with comments, you get the relevant error.

We need the trailing brackets even in the absence of actual parameters. We didn't in the old format, but I couldn't find the updated syntax diagram.

select * from tf_test;
-- ORA-04044: procedure, function, package, or type is not allowed here

We can subtract more code, just not the brackets ;p

APEX Survey Results: Editing Tools

Another preference question in my 2015 survey.

Q5: What editing tools do you use for PL/SQL and JavaScript

That Jeff Smith fellow should be pretty happy with the top result, but almost half of the "Other" responses said PL/SQL Developer. I used this many years ago while SQL Developer was still being born. Since SQL Developer was free and portable, it was an easy selection, though I only use if for queries, not PL/SQL development.

A number of editors were suggested in Other, including two takers for Notepad. All I can say is wow, and I hope for the sake of others that tab characters are not being saved in your files.

I've been happily using Textpad almost since I started coding. I'm interested in making the jump to a non OS specific editor like Sublime, or perhaps Atom, but I'm not there yet. Though after watching the Kscope16 deep dive / montage (as a livestream), I want to try soon!

Edit: January 2017

I spotted these results in the 2016 StackOverflow survey, a somewhat more diverse survey than mine.

And I've started to use Atom, but I might have to give Sublime a go for a while to compare.

Synchronous Dynamic Actions in APEX 5.1

If you've ever used a PL/SQL dynamic action with the default 'wait for result', you would have seen the following warning if you have the browser console open.

Text for bots: Synchronous XMLHttpRequest on the main thread is deprecated because of its detrimental effects to the end user's experience.

Consider this scenario of dynamic actions on change of P42_ITEM:

Synchronous vs Asynchronous server calls

First JavaScript takes value of P42_ITEM, concatenates a letter and places result in another field. Let's say I entered "X":
$s('P42_NEW2', $v('P42_ITEM')+'a');
The value in P42_NEW2 would be "Xa"

The PL/SQL code concatenates another letter, passing current value of P42_NEW in and out of session state via page items to submit/return:
:P42_NEW2 := :P42_NEW2||'b';
The value will now be "Xab"

The second JavaScript concatenates another letter:
$s('P42_NEW2', $v('P42_NEW2')+'c');

On page load the value of P42_NEW2 would be "ac", since both JavaScript actions default to 'Fire on page load'.

If 'Wait for Result' on the PL/SQL action is 'yes' (default), then the communication will be synchronous and the JavaScript will only execute once the PL/SQL is finished. The final value would be "Xabc"

If the PL/SQL action 'Wait for Result' is set to 'No', then the subsequent JavaScript will execute immediately, not, um, waiting for a result. The value of P42_NEW2 would briefly be "Xa", then just "c" until the PL/SQL returns with "Xab".

Asynchronous DA result

Think about that. This impacts how you structure dynamic actions, pose questions to the user, and steer application workflow. Regression testing will be required when updating compatibility of your existing applications to 5.1.

In a tweet by John Snyders, who has posted some amazingly detailed posts on the inner workings of APEX, he suggested we can soon forget there was any other way.

A little thing: #orclapex #ea51 dynamic actions now do async ajax! Soon we can forget it was ever any other way. https://t.co/oUavxVs3OE

— John Snyders (@J_Snyders) June 2, 2016

I understood this to infer that synchronous actions would disappear. That screenshot is from 5.1 EA1, so it seems they're hanging around for now. It could be considered a backwards compatability thing, though it's still the default option. I'd say many applications would not work as expected should this behaviour disappear.

Synchronous activity on a website is usually non-preferrable for good UX, I understand that's inciting the change in the nature of the inherent browser behaviour (ie: the warning above). As far as I understand it, the way the APEX team has mitigated this warning is by modifying the JavaScript call to the PL/SQL callback as follows:

apex.server.process
  ("CB_AJAX" // name of AJAX callback
  ,{x01       : $v('P42_ITEM')}
  ,{async: false // deprecated synchronous option
   ,success: function(pData) {
     // deprecated wait for result
     $s('P42_NEW2', pData + 'c');
   }
).done(function(pData) {
  // 5.1 wait for result
  // ...
});

Well, this is the jQuery method for deferred callbacks. In future this will be done natively as promises. I've previously detailed some sample code on these variations.

So the end result to the developer appears to be very little, though I'm sure there were plenty of tweaks in the engine to make this happen. Though it does protect applications from when browsers do decide to no longer support the async parameter.

Edit: The end result for the user, as per John's comment, is the browser will not be locked up whilst waiting for the result. This behaviour probably will result in some behaviour differences in some applications, time will tell. Be on the lookout for it.

Actions are no longer synchronous, but you can still have JavaScript execute only after the PL/SQL finished.

Improving PL/SQL performance in APEX

One of the simplest tuning techniques to encapsulate PL/SQL used in APEX within packages, minimising the size of anonymous blocks. This applies to any PL/SQL within the page, including computations, processes, plugins, dynamic actions, validations, shortcuts and dynamic PL/SQL regions.

This change can make a big impact in the execution time of PL/SQL as it's processed at compile time instead of being parsed and compiled at runtime as dynamic PL/SQL.

Plug-ins can be wonderful black boxes and consumers may not care how it works or looks under the hood. I have a few other views on the use plug-ins that I'll share on day, but this post looks at a way you can always improve it's performance by shifting the supplied code into a PL/SQL package.

Example of freshly imported plug-in in APEX 5.0

You (re)move the code that's supplied and defined as a procedure in your own package, perhaps apx_plugins.

Then modify the "Render function name" to prefix the call, ie: apx_plugins.render_save_before_exit

I gathered performance data on two plugins using compiled vs dynamic code. The following graph shows the relative difference in rendering time between the two using 11g, where the red dots display how many data points I used.

APEX Plug-in Performance - Dynamic vs Compiled PL/SQL

This particular information was garnered from debug log execution time.

select message, avg(execution_time)
from apex_debug_messages
where message like '%sparkline%'
group by message;

Update: I have used Oracle's instrumentation to get better figures
http://www.grassroots-oracle.com/2019/11/on-interpreted-code-in-oracle-apex.html

The recommendation to move this code can be found in (At least) the help text for plugin PL/SQL and documentation.

Moving code to packages will also overcome other limitations such as how much code fits in the attribute, though I think this boundary should only be reached in extreme circumstances. The Excel2Collection plug-in is an example. The author had to compress the PL/SQL code to make it and be packaged as a plug-in.

Encapsulating PL/SQL also provides more opportunity for re-use, reducing chance of defects and
allowing more granular version control.

I would like to think Oracle Forms developers have been doing this for years, in part to help minimise network traffic.

If I'm not mistaken, the same concepts can be applied to complex SQL, moving them into database views.

Daniel Hochleitner has also blogged on this topic.

Exploring dynamic pivot options

In looking for information on pivoting variable number of columns, I stumbled across a question I once managed to AskTom, many moons ago.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7644594042547

Adrian Billington has an interesting lead into an XML solution with pivot, but would need more digging to finalise conversation of XML data for APEX to use.
http://www.oracle-developer.net/display.php?id=506

Then I found Tom's answer using easy to understand dynamic SQL (properly asserted, no less)
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4471013000346257238

Then I found this gem, courtesy of Anton Scheffer, via Lucas Jellema
https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/

The link to Anton's blog post no longer works, but the relevant sql zip is still available in the resources.

It accepts queries like this, which solves my current problem:

select * from table( pivot(  'select deptno,  job, count(*) c from scott.emp group by deptno, job' ) )

   DEPTNO    ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
---------- ---------- ---------- ---------- ---------- ----------
        30                     1          1                     4
        20          2          2          1                      
        10                     1          1          1           

What an awesome solution.

For anyone receiving this error:
ORA-29913: error in executing ODCITABLEDESCRIBE callout
check you have direct SELECT privilege on table, similar to resolving PLS-201 error.

Now to work it into an APEX region, which seems like will be a little wild when applying PPR on report with generic number of columns.

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

Update 2019: Connor now his this code in his Github.

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 after the fact.

A little tip if you do find it - don't attempt to copy the code from the translated version of the page. It's amazing how many syntax issues were introduced by the translator, and logical issues that I wouldn't have noticed had I not performed a diff after things went wrong.

For instance, the "aggregate" keyword here vanished:
CREATE or replace FUNCTION listagg_clob (input varchar2) RETURN clob
PARALLEL_ENABLE AGGREGATE USING listagg_clob_t;
/

Thank's Carsten for a useful solution using custom aggregate functions, an element of PL/SQL I'm yet to master.

It's certainly faster than the XML solution, here is the throughput difference from 100 iterations of each.
-- listagg_clob
3.34 secs 
.0334 secs per iteration
-- xmlagg
18.75 secs 
.1875 secs per iteration

The simplest solution, however, would be to set up an RESTful service using ORDS.

Another workaround involves a CASE statement.

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, I missed the Database Design championship. I'll have to try again next time - hopefully with more competition from the wider community, <hint> since it's a worthy cause to participate. </hint>

Steven recently announced the rankings for the 2014 SQL championship. I managed 9th from 40 competitors, which is pretty cool. With a little less haste I should have got a few more correct but that's the game you play ;p

To perhaps pique your interest, the question topics were

1. String aggregation techniques
2. Temporal Validity (12c date queries)
3. Date datatype behaviours
4. DML in nested tables
5. MODEL queries.

Yup, it got hard quick, but they don't call it an annual championship for nothing, right?

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 DELETE, MERGE, INSERT, 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.

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

Past discussions are available to view - last time Steven asked for use cases for DBMS_RANDOM. You could even test out your PL/SQL while you're there and take a few quizzes.

Case sensitivities and making JavaScript more like PL/SQL

Yesterday the Anti-Kyte (a.k.a. Mike) published a great post on coding styles. I started to post a comment but ended up rambling to I thought I'd put my thoughts here.

I can't be one to comment on tangential intros (just look at any of my presentations), but he almost lost me when I thought the entire post was going to be soccer jokes I didn't get but switched in time to three controversial topics:

1. Uppercase keywords
2. Camel case
3. ANSI joins

To add my 5 cents - I still tend to uppercase keywords - partially out of habit and sometimes standards are a client site. I still like the concept of the brain finding lower case easier to interpret than upper case - now with extra help thanks to syntax highlighters. I don't mind all lower case, though I feel a little naughty when doing so. I know Connor does it all the time, and it looks raw & simple - which is not a bad thing.

He scared me a little when he started to provide PL/SQL examples using camel case - I can't stand camel case.
My identifier naming conventions in JavaScript tend to match up with my PL/SQL preferences, though sometimes I find it to be a hybrid in occasions where the name might harmonise with other jQuery functions.

function p42_show_info (t) {
  theRow = $(t).closest('tr').addClass('highlight');
  l_order = theRow.children('td[headers=ORDER_NO]').text();
...

I should probably stick with one way or the other since bugs thanks to tolerated case sensitivity are the worst kind of bugs - parasites, if you will.

I have no problems using ANSI syntax when used explicitly (no natural joins), but I tend to only use them for outer joins and when readability would benefit. There are scenarios where ANSI outer joins are the best solution to a query.

Scott

12 new 12c features for developers

I haven't installed 12c yet because
a) I haven't had the time
b) my Linux skills aren't the best

What I have done though is read through some of the documentation in the New Features manual.

The APEX features are just regurgitating what's in 4.2, since that's what's shipped out of the box with 12c - but I always like to look through what's improved with SQL and PL/SQL.

No doubt many of you may have seen or read presentations on what's coming up as early as last year, but for those who haven't - here are some I've spotted that made the cut for 12c and I look foward to tinkering with

1. PL/SQL function in the WITH clause. I think this may sometimes come in handy, and other times it will be abused and lead to some real ugly code. Performance will be interesting.
2. Increased size limit for VARCHAR2 etc - no doubt people have been asking for this since at least 8i...
3. dbms_utility.expand_sql_text - Recursively replace any view references with relevant tables. This would have been brilliant in a project I often visit where there are views upon views upon views.
4. utl_call_stack - returns structured information about an exception stack. Tom Kyte has already blogged about this one, I'd suggest he's been pushing for something like this for a while.
5. Sequences can be used as column DEFAULT. Yes. So very yes. Though again I will be interested in performance, since 11g's :new.id := my_seq.nextval was the same as selecting from dual.
6. Default value on explicit null insertion - for those times where you really must have a default value in the column!
   
7. Identity columns - full examples are scant, but looks interesting.
8. row_limiting_clause - for top-N reporting, apparently an ANSI standard. This definitely needs exploring.
9. Row pattern matching. Um, wow. Big data influences?
10. Cross apply; Lateral clauses. From what I think is going on here, this could be mighty handy.
11. SYS_CONTEXT sys_session_roles - role interrogation for current session - cool, and will be very handy. A little late for many Forms applications, though.
12. TRUNCATE TABLE CASCADE - where is that sledgehammer?

I listened to an Oracle Magazine podcast recently talking about MySQL - I wonder if the collaboration going on here has influenced 12c features, because it sounds like the quality of MySQL is enhanced with Oracle IP.

Of course, if you're in Australia or would like to visit, the Insync13 national conference series already has a bunch of 12c presentations that span JDeveloper, Optimizer, Coherence, Weblogic and general features.

Syntax differences between JavaScript & PL/SQL

I'm sure anyone who's worked with multiple programming languages - particularly at the same time - can attest to the occasional slip-up.

It's just unfortunate that they are just so hard to debug. Take these two statements

console.log('id:' || this.attr('id'));
console.log('id:' +  this.attr('id'));

Both are valid JavaScript statements & both work - in that the execute without error, but one returns the ID attribute value as debug output.

Thanks to Tom in the forums for helping me see what I missed
https://forums.oracle.com/forums/message.jspa?messageID=10654246#10654246
For those with a strong, habitual PL/SQL background such as myself, I think making this mistake at one point was pretty much inevitable.

Basically the first one accepts to expressions within an OR statement, while the second concatenates the two strings as desired.

It makes me wonder how many times strange things like this happen with human language. I'm sure there are some good analogies out there.

I certainly don't envy those people constructing multi-lingual applications - not that much demand here down under :-)

Scott

Categorizr for APEX - extended

Software evolves.

And I think it's going on right now. In April, Christian Rokitta blogged about Categorizr.

He adapted some PHP code from Brett Jankord and suited it to PL/SQL.

When I was building the mobile application for my AUSOUG presentation, I found it useful for statistics gathering... but I wanted more.

I added a few more functions, and included an object type to allow queries shown further below.
These functions broke/butchered the agent string further into

• OS 
• OS Version
• Browser
• Browser version

I think I've done well to cover the major data sets.

CREATE TYPE categorizr_agent_details IS OBJECT (
     agent                 VARCHAR2 (2000)
    ,device                VARCHAR2 (2000)
    ,os                    VARCHAR2 (2000)
    ,os_version            VARCHAR2 (2000)
    ,browser               VARCHAR2 (2000)
    ,browser_version       VARCHAR2 (2000)
   );
/

CREATE OR REPLACE PACKAGE categorizr
AS
   /******************************************************************************
      NAME:       categorizr
      PURPOSE:    detect web user agent device type

      Based on:
      Categorizr Version 1.1
      http://www.brettjankord.com/2012/01/16/categorizr-a-modern-device-detection-script/
      Written by Brett Jankord - Copyright © 2011

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      0.1        30- 3-2012  crokitta         Created this package.
      0.2        24-10-2012  swesley          Included browser/os gets
                                              With inspiration from http://barakhshan.wetpaint.com/page/detecting+os+and+browser+pl-sql
                                              and help from my own dataset. Doubtful all scenarios considered
   ******************************************************************************/
   g_tablets_as_desktops   BOOLEAN := FALSE; --If TRUE, tablets will be categorized as desktops
   g_smarttv_as_desktops   BOOLEAN := FALSE; --If TRUE, smartTVs will be categorized as desktops
   g_user_agent            VARCHAR2 (2000); -- User Agent String used for detection

   g_agent_details  categorizr_agent_details;

   FUNCTION get_agent_details(p_user_agent  VARCHAR2)
      RETURN categorizr_agent_details;

   FUNCTION get_category
      RETURN VARCHAR2;

   FUNCTION get_browser
      RETURN VARCHAR2;

   FUNCTION get_os
      RETURN VARCHAR2;

   FUNCTION isdesktop
      RETURN BOOLEAN;

   FUNCTION istablet
      RETURN BOOLEAN;

   FUNCTION istv
      RETURN BOOLEAN;

   FUNCTION ismobile
      RETURN BOOLEAN;

   /*
    The package is initialized automatically when called, trying to fetch the value of
    the HTTP_USER_AGENT, which naturally only succeeds when called through a web gateway.
    Additionally the package just offers a mean to test a user agent strings manually by
    passing the string with a procedure call
   */

   PROCEDURE set_user_agent (http_user_agent_string VARCHAR2 DEFAULT NULL);
END categorizr;

If you're interested in the package body, click as instructed

CREATE OR REPLACE PACKAGE BODY categorizr AS
   /******************************************************************************
      NAME:       categorizr
      PURPOSE:    detect web user agent device type

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        30-3-2012   crokitta         Created this package.
      0.2        24-10-2012  swesley          Included browser/os gets
   ******************************************************************************/


   FUNCTION preg_match (pattern    VARCHAR2,
                        subject    VARCHAR2,
                        switch     VARCHAR2 DEFAULT NULL)
      RETURN BOOLEAN
   IS
      l_pattern   VARCHAR2 (32767) := pattern;
      l_subject   VARCHAR2 (32767) := subject;
   BEGIN
      IF LOWER (switch) = 'i'
      THEN
         l_pattern := LOWER (l_pattern);
         l_subject := LOWER (l_subject);
      END IF;

      IF REGEXP_INSTR (l_subject, l_pattern) = 0
      THEN
         RETURN FALSE;
      ELSE
         RETURN TRUE;
      END IF;
   END;

   PROCEDURE set_category
   IS
   BEGIN
      CASE
         -- Check if user agent is a smart TV - http://goo.gl/FocDk
         WHEN preg_match ('GoogleTV|SmartTV|Internet.TV|NetCast|NETTV|AppleTV|boxee|Kylo|Roku|DLNADOC|CE\-HTML', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'tv';
         -- Check if user agent is a TV Based Gaming Console
         WHEN preg_match ('Xbox|PLAYSTATION.3|Wii', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'tv';
         -- Check if user agent is a Tablet
         WHEN (preg_match ('iP(a|ro)d', g_user_agent, 'i')
               OR preg_match ('tablet|tsb_cloud_companion', g_user_agent, 'i'))
              AND (NOT preg_match ('RX-34', g_user_agent, 'i')
                   OR preg_match ('FOLIO', g_user_agent, 'i'))
         THEN
            g_agent_details.device := 'tablet';
         -- Check if user agent is an Android Tablet
         WHEN preg_match ('Linux', g_user_agent, 'i')
              AND preg_match ('Android', g_user_agent, 'i')
              AND (NOT preg_match ('Fennec|mobi|HTC.Magic|HTCX06HT|Nexus.One|SC-02B|fone.945', g_user_agent, 'i')
               --or preg_match ('GT-P1000', g_user_agent, 'i')
               )
         THEN
            g_agent_details.device := 'tablet';
         -- Check if user agent is a Kindle or Kindle Fire
         WHEN preg_match ('Kindle', g_user_agent, 'i')
              OR preg_match ('Mac.OS', g_user_agent, 'i')
                AND preg_match ('Silk', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'tablet';
         -- Check if user agent is a pre Android 3.0 Tablet
         WHEN preg_match (
                 'GT-P10|SC-01C|SHW-M180S|SGH-T849|SCH-I800|SHW-M180L|SPH-P100|SGH-I987|zt180|HTC(.Flyer|\_Flyer)|Sprint.ATP51|ViewPad7|pandigital(sprnova|nova)|Ideos.S7|Dell.Streak.7|Advent.Vega|A101IT|A70BHT|MID7015|Next2|nook',
                 g_user_agent,'i')
              OR preg_match ('MB511', g_user_agent, 'i')
                AND preg_match ('RUTEM', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'tablet';
         -- Check if user agent is unique Mobile User Agent
         WHEN preg_match ('BOLT|Fennec|Iris|Maemo|Minimo|Mobi|mowser|NetFront|Novarra|Prism|RX-34|Skyfire|Tear|XV6875|XV6975|Google.Wireless.Transcoder', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'mobile';
         -- Check if user agent is an odd Opera User Agent - http:--goo.gl/nK90K
         WHEN preg_match ('Opera', g_user_agent, 'i')
              AND preg_match ('Windows.NT.5', g_user_agent, 'i')
              AND preg_match ('HTC|Xda|Mini|Vario|SAMSUNG\-GT\-i8000|SAMSUNG\-SGH\-i9', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'mobile';
         -- Check if user agent is Windows Desktop
         WHEN preg_match ('Windows.(NT|XP|ME|9)', g_user_agent, 'i')
              AND NOT preg_match ('Phone', g_user_agent, 'i')
              OR preg_match ('Win(9|.9|NT)', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'desktop';
         -- Check if agent is Mac Desktop
         WHEN preg_match ('Macintosh|PowerPC', g_user_agent, 'i')
              AND NOT preg_match ('Silk', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'desktop';
         -- Check if user agent is a Linux Desktop
         WHEN preg_match ('Linux', g_user_agent, 'i')
              AND preg_match ('X11', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'desktop';
         -- Check if user agent is a Solaris, SunOS, BSD Desktop
         WHEN preg_match ('Solaris|SunOS|BSD', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'desktop';
         -- Check if user agent is a Desktop BOT/Crawler/Spider
         WHEN preg_match ('Bot|Crawler|Spider|Yahoo|ia_archiver|Covario-IDS|findlinks|DataparkSearch|larbin|Mediapartners-Google|NG-Search|Snappy|Teoma|Jeeves|TinEye', g_user_agent, 'i')
              AND NOT preg_match ('Mobile', g_user_agent, 'i')
         THEN
            g_agent_details.device := 'desktop';
         -- Otherwise assume it is a Mobile Device
         ELSE
            g_agent_details.device := 'mobile';
      END CASE;

      -- Categorize Tablets as desktops
      IF g_tablets_as_desktops
      AND g_agent_details.device = 'tablet'
      THEN
         g_agent_details.device := 'desktop';
      END IF;

      -- Categorize TVs as desktops
      IF g_smarttv_as_desktops
      AND g_agent_details.device = 'tv'
      THEN
         g_agent_details.device := 'desktop';
      END IF;
   END;

   PROCEDURE set_browser
   IS
   BEGIN
      IF preg_match ('Opera', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Opera';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Opera/')+6);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('MSIE', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Internet Explorer';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'MSIE ')+5);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('Chrome', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Chrome';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Chrome/')+7);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('Firefox', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Firefox';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Firefox/')+8);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('Safari', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Safari';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Safari/')+7);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSE
        g_agent_details.browser := 'Other';
        g_agent_details.browser_version := NULL;
      END IF;
   END set_browser;

   PROCEDURE set_os
   IS
   BEGIN
      IF preg_match ('iPad|iPod|iPhone', g_user_agent, 'i') THEN
        g_agent_details.os := 'iOS';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, ' OS ')+4);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(g_agent_details.os_version, ' ')-1);

      ELSIF preg_match ('Windows', g_user_agent, 'i') THEN
        g_agent_details.os := 'Windows';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Windows ')+8);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('Mac OS', g_user_agent, 'i') THEN
        g_agent_details.os := 'Macintosh';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Mac OS X ')+9);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('RIM Tablet', g_user_agent, 'i') THEN
        g_agent_details.os := 'RIM';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'RIM Tablet OS ')+13);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('Android', g_user_agent, 'i') THEN
        g_agent_details.os := 'Android';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Android ')+8);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('Linux', g_user_agent, 'i') THEN
        g_agent_details.os := 'Linux';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Linux ')+6);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSE
        g_agent_details.os := 'Other';
        g_agent_details.os_version := NULL;
      END IF;
   END set_os;

   FUNCTION get_agent_details(p_user_agent  VARCHAR2)
      RETURN categorizr_agent_details IS
   BEGIN
     -- override package initialisation
     set_user_agent(p_user_agent);
     RETURN g_agent_details;
   END get_agent_details;

   PROCEDURE set_user_agent (http_user_agent_string VARCHAR2 DEFAULT NULL)
   IS
   BEGIN
     g_agent_details := NEW categorizr_agent_details(null,null,null,null,null,null);
      g_user_agent := http_user_agent_string;

      IF g_user_agent IS NULL
      THEN
         BEGIN
            g_user_agent := OWA_UTIL.get_cgi_env ('HTTP_USER_AGENT');
         EXCEPTION
            WHEN OTHERS
            THEN
               g_user_agent := NULL;
         END;
      END IF;

      set_category;
      set_os;
      set_browser;
      g_agent_details.agent := g_user_agent;
   /*EXCEPTION
      WHEN OTHERS
      THEN
         g_user_agent := null;*/
   END;

   FUNCTION get_category
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_agent_details.device;
   END;

   FUNCTION get_browser
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_agent_details.browser;
   END;

   FUNCTION get_os
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_agent_details.os;
   END;

   -- Returns true if desktop user agent is detected
   FUNCTION isdesktop
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'desktop'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;

   -- Returns true if tablet user agent is detected
   FUNCTION istablet
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'tablet'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;

   -- Returns true if SmartTV user agent is detected
   FUNCTION istv
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'tv'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;

   -- Returns true if mobile user agent is detected
   FUNCTION ismobile
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'mobile'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;
BEGIN
   set_user_agent;
   null;
END categorizr;

I've created an interactive report on visits data from that application: http://apex.oracle.com/pls/apex/f?p=SW2012:CATEGORIZR

This query used to generate that report

select 
   cnt
  ,categorizr.get_agent_details(v.agent).browser browser
  ,categorizr.get_agent_details(v.agent).browser_version b_version
  ,categorizr.get_agent_details(v.agent).os os
  ,categorizr.get_agent_details(v.agent).os_version os_version
  ,categorizr.get_agent_details(v.agent).device device
  ,categorizr.get_agent_details(v.agent).agent the_agent
from 
  (select agent, count(*) cnt from am_visits group by agent) v

If you were to utilise this package for production code, I would first check it for defects - then question your need to set the user agent in the package body.

I might also suggest that as this code matures, it could be added to the Alexandra PL/SQL Library, administered by Morten, expanded/updated by the masses, encouraging other utilities to come visiting.

Let me know if you find it handy or have any feedback. Thank you Christian & Brett for the boost.

Scott

Advert: Sage Training for Apex & PL/SQL

Hello to all from Perth, Australia (and beyond if you like)

Sage Computing Services have just scheduled two courses for November:

Oracle Apex 4 Workshop => 9-11th November
PL/SQL Workshop => 21-23 November

Both will be held in the Perth CBD, please e-mail us if you're interested in further details.

Cheers!

Scott

ps - we don't often put on PL/SQL training here in Perth - get in while you can!

A simple way to learn more about PL/SQL

Attention PL/SQL developers - Steven Feuerstein's PL/SQL Challenge website has just been 2.0'd

If you haven't given it a go, now is the time for a fresh crack. It's not just a daily quiz, but a place to learn and keep up to date with Oracle technology - not necessarily with new features, but techniques and capabilities you may not have been aware or, nor had the exposure to.

That and everyone has a chance to win something.

Kudos to Steven and his team of merry men.

Scott.

Anonymous PL/SQL blocks

Some of you may be familiar with the ability to administer Apex applications via SQL Developer, for instance I could modify the alias of my application thusly:

It opens a popup which allows me to write my new alias.
As with the other facilities of SQL Developer, you can opt to view the relevant SQL for this change.

All Oracle is really doing here is calling the relevant API, but what I also noticed the first time I used it was the autonomous transaction pragma it applied to the anonymous block.

declare
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  wwv_flow_api.set_security_group_id(p_security_group_id=>100001);
  wwv_flow_api.set_application_alias (p_flow_id=>101,p_alias=>'TIM_0101_b');
  commit;
end;
/

In the past I've quite happily applied this pragma for a procedure defined within a package, but this demonstrates we can also define independent transactions within these anonymous PL/SQL blocks, in addition to certain triggers.

Something I added to the memory bank when I first stumbled on it.

A final note - you can also kinda "name" your anonymous block to assist your documentation process, ie - there is no reason why you can't finish with:
END my_anonymous_block;

ScottWe

I quizzed an Oracle community

Once was a time when I read a few questions every morning at AskTom. It only took a few minutes but the amount of hints, tips, tricks and general best practice guidelines I learned became so valuable as my experience grew.

I still find it a valuable resource, but these days as a seasoned developer, I found myself a solution with a different style of learning - keeps my mind active and I learn the odd thing occasionally, other times it helps affirm my current understanding of Oracle behaviour.

Steven Feuerstein and his friends have created a (business) daily PL/SQL Challenge. You may have heard about it and wondered if you should give it a go - for most probably a variety of reasons.
Well, you don't even have to compete for the frequent prizes - you can have a private profile; or you may see people providing a link to their public profiles on their blog; or as a resume item.

The main thing is the occasional "test your knowledge" - typically a little snippet of code to have you thinking for a couple of minutes.

Steven has provided players an opportunity to submit their own quiz questions, so if you're curious of the sort of questions that get asked, here's mine from 28th February 2011 - it's a lesson on Short Circuit Evaluation:

---

I create and populate a table as follows:

CREATE TABLE plch_parts
(
   partnum    INTEGER
 , partname   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO plch_parts
        VALUES (1, 'Mouse');

   INSERT INTO plch_parts
        VALUES (100, 'Keyboard');
   COMMIT;
END;
/

Then I define the following function:

CREATE OR REPLACE FUNCTION part_exists (pn_partnum  IN  plch_parts.partnum%TYPE)
RETURN BOOLEAN IS
  ln_partname  plch_parts.partname%TYPE;
BEGIN
  SELECT partname
  INTO   ln_partname
  FROM   plch_parts
  WHERE  partnum = pn_partnum;

  DBMS_OUTPUT.PUT_LINE(ln_partname);

  RETURN TRUE;
EXCEPTION WHEN NO_DATA_FOUND THEN
  RETURN FALSE;
END part_exists;
/

Which of the choices, when used in place of the /*IF CONDITION*/ comment in the following block,
will result the subsequent output being displayed on the screen after the block is executed?

BEGIN
  /*IF CONDITION*/
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

Mouse
Keyboard
TRUE

---

A	IF part_exists(1) OR part_exists(100) THEN
B	IF NVL(part_exists(1) ,part_exists(100)) THEN
C	IF COALESCE(part_exists(1) ,part_exists(100)) THEN
D	IF part_exists(1) AND part_exists(100) THEN
E	IF CASE WHEN part_exists(1) OR part_exists(100) THEN TRUE END THEN

---

The rules are well written, the idea is well presented (unlike my hand written html table representation here), and the user feedback is fantastic. Steven also has an associated blog for constructive discussions on the occasional contentious question, or question of general interest or quirkiness.

My question was well received by some friends, and Steven now provides users with simple, key survey results from each question - and I was happy with mine.

The answer is B & D - "NVL" & "AND"
I provided the following verification code to assist explanation of the answer:

BEGIN
  -- As soon as the first expression returns true, PL/SQL no longer needs to evaluate the second. This is perfect for situations where you can put more efficient calculations first.
  IF part_exists(1)
  OR part_exists(100) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- Ideally this would behave as per COALESCE, but unfortunately NVL does not support short circuit evaluation.
  IF NVL(part_exists(1)
        ,part_exists(100)) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- COALESCE is ANSI-defined shorthand for similarly written CASE functions, thereby supporting short circuit evaluation. COALESCE is generally preferred to NVL.
  IF COALESCE(part_exists(1)
             ,part_exists(100)) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- Since this uses the AND operator, both expressions need to be evaluated to determine if the entire boolean equation is true. Oracle would use short-circuit evaluation if the situation permits, for instance, if the first expression returned false the second would not be evaluated.
  IF part_exists(1)
  AND part_exists(100) THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

BEGIN
  -- The Oracle database will use short circuit evaluation for CASE expressions.
  IF CASE WHEN part_exists(1)
  OR part_exists(100) THEN TRUE END THEN
    DBMS_OUTPUT.put_line('TRUE');
  END IF;
END anon;
/

There are currently over 1000 regular players, but only about two dozen regulars are registered as Australian - I've seen more than that at our AUSOUG branch meetings! Come on Aussies - "represent!"

It's a great learning & affirming tool - I'll be on the lookout for inspiration for submitting more questions. :-)

ScottWe

ps - no, you can't cut & paste the code while a question is active