Validate data type within SQL

For all those pushing data around, especially dirty data, this one is for you.

Today I was preparing to process data I loaded from a spreadsheet.
A simple filter was required - to ignore the header row, had it been included.

I'm lucky enough to be working on 19c, and I remembered that a reasonably new function should help me out with all many of data loading issues. With a quick scan of my favourite reference manual, I found VALIDATE_CONVERSION.

For example, this gives me 'ORA-01722 invalid number' because of the header row I failed to exclude.

select c.*
from my_data_load c
order by to_number(seq);

But without the to_number, the order returns incorrectly.

SEQ
-----
1
10
12
140
2
Order

Sure, we could say
where seq != 'Order'

But this tool will have more than one use

select c.*
from my_data_load c
where validate_conversion(seq as number) = 1
order by to_number(seq);

SEQ
-----
1
2
10
12
140

Recreate this result using

select * from (
select 'Order' seq from dual
union all select '1' from dual
union all select '2' from dual
union all select '10' from dual
union all select '12' from dual
union all select '140' from dual
)
where validate_conversion(seq as number) = 1
order by to_number(seq)

And see typical return values (0 or 1) for conversion attempts using

select
   validate_conversion('1' as number) num1
  ,validate_conversion('2' as number) num2
  ,validate_conversion('1b' as number)  num_not
  ,validate_conversion('01-01-2001' as date) date1
  ,validate_conversion('30-02-2000' as date, 'dd-mm-yyyy') date2
from dual;  

      NUM1       NUM2    NUM_NOT      DATE1      DATE2
---------- ---------- ---------- ---------- ----------
         1          1          0          0          0

It's one of a few tools I'm using to make data loading life easier, and processing data in sets using SQL, not looping & context switching within PL/SQL.

The kicker, turns out this has been available since 12.2.

It turns out the usage of validate_conversion in PL/SQL will give the compilation warning PLW-06009. And so does the alternative to check if this returns null:
to_date('z-z-2001' default null on conversion error, 'dd-mm-yyyy')

More examples available from
LiveSQL
Tim Hall
Oren Nakdimon
19c Documentation

Unpivoting Oracle APEX meta-data

There are APEX dictionary views for most of the data that represents the 'source' that is your APEX application meta-data.

Note: This post has beeen updated to reflect me not looking very hard, but I added a performance comparison... just because.

Today I found one place where I really wanted to run a query to find references to potential data - application substitution strings.

APEX Application Substitution Strings

And yes, I have a CSS rule to right-align those names, to make it easier to read.

I couldn't find any references in the APEX dictionary (correction, see below), so I looked in the Oracle data dictionary to find where it may live.

select * from all_tab_columns 
where column_name like 'SUB%03';

So not only are these stored in a place inaccessible to us mere-mortal developers, they are also stored in 20 sets of name/value columns - not rows.

This means if you have a dozen applications, with references in slightly different locations for each application, then string searches might be a pain.

Unpivot to the rescue!

select * from (
  select id app_id, alias, name -- key facts
      -- all the substitution stringz!
      ,substitution_string_01, substitution_value_01
      ,substitution_string_02, substitution_value_02
      ,substitution_string_03, substitution_value_03
      ,substitution_string_04, substitution_value_04
      ,substitution_string_05, substitution_value_05
      ,substitution_string_06, substitution_value_06
      ,substitution_string_07, substitution_value_07
      ,substitution_string_08, substitution_value_08
      ,substitution_string_09, substitution_value_09
      ,substitution_string_10, substitution_value_10
      ,substitution_string_11, substitution_value_11
      ,substitution_string_12, substitution_value_12
      ,substitution_string_13, substitution_value_13
      ,substitution_string_14, substitution_value_14
      ,substitution_string_15, substitution_value_15
      ,substitution_string_16, substitution_value_16
      ,substitution_string_17, substitution_value_17
      ,substitution_string_18, substitution_value_18
      ,substitution_string_19, substitution_value_19
      ,substitution_string_20, substitution_value_20
  from apex_190200.WWV_FLOWS -- direct from underlying view
)
unpivot (
(str, val) -- new columns
for rec in -- denoted by 
  ((substitution_string_01, substitution_value_01) as '01'
  ,(substitution_string_02, substitution_value_02) as '02'
  ,(substitution_string_03, substitution_value_03) as '03'
  ,(substitution_string_04, substitution_value_04) as '04'
  ,(substitution_string_05, substitution_value_05) as '05'
  ,(substitution_string_06, substitution_value_06) as '06'
  ,(substitution_string_07, substitution_value_07) as '07'
  ,(substitution_string_08, substitution_value_08) as '08'
  ,(substitution_string_09, substitution_value_09) as '09'
  ,(substitution_string_10, substitution_value_10) as '10'
  ,(substitution_string_11, substitution_value_11) as '11'
  ,(substitution_string_12, substitution_value_12) as '12'
  ,(substitution_string_13, substitution_value_13) as '13'
  ,(substitution_string_14, substitution_value_14) as '14'
  ,(substitution_string_15, substitution_value_15) as '15'
  ,(substitution_string_16, substitution_value_16) as '16'
  ,(substitution_string_17, substitution_value_17) as '17'
  ,(substitution_string_18, substitution_value_18) as '18'
  ,(substitution_string_19, substitution_value_19) as '19'
  ,(substitution_string_20, substitution_value_20) as '20'
  )
)
order by app_id, str 

This query transposes all the string/value columns into rows, each denonimated by the new "REC" column.

Note, this query can only be executed by those with the APEX Administrator Role, and if you want to have it within a view that could be executed by other schemas, then select access on wwv_flows is needed with grant option.

Columns unpivoted into rows

By placing the query in a view, I can now make queries like this to find any substitution strings in any applications that have date references.

select * from apx_app_sub_strings
where val like 'APP_DATE%';

Pretty neat, well at least as far as what I was trying to do.

This query may break in future versions, as it's based on an underlying, undocumented view.
I also think that once upon a time, there were fewer pairs.

Update: As the community quickly pointed out, I missed & forgot about a view already dedicated for such a task. I was too busy looking for a column number, when really I should have used APEX_APPLICATION_SUBSTITUTIONS.

I thought I'd see how 'they' solved the problem, and I was a little surprised to see a bunch of UNION statements.

SELECT ...
           f.substitution_string_18,
           f.substitution_value_18,
           f.substitution_string_19,
           f.substitution_value_19,
           f.substitution_string_20,
           f.substitution_value_20
      from wwv_flow_authorized auth,
           wwv_flows f
     where f.id = auth.application_id )
select workspace,
       workspace_display_name,
       application_id,
       application_name,
       substitution_string_01 as substitution_string,
       substitution_value_01  as substitution_value
  from substitution
where substitution_string_01 is not null
union all
select workspace,
       workspace_display_name,
       application_id,
       application_name,
       substitution_string_02 as substitution_string,
       substitution_value_02  as substitution_value
  from substitution
where substitution_string_02 is not null
union all...

After seeing this I couldn't help but run a brute for comparison, running both solutions x times.

iterations:5000
16.55 secs (.003310 secs per iteration) -- union all
 6.54 secs (.001308 secs per iteration) -- unpivot

I made sure the underlying join was the same, and I'm not all that surprised the unpivot did the job quicker.

Update 2: It appears 20.1 has gone with unpivot.

Friday Funny - Festival of serious yahoos

I don't care what you all think, this is my blog, and I continue to laugh at this SQL joke.

select column_value
from apex_string.split('YAHOO~SERIOUS~FESTIVAL', '~')
order by dbms_random.value

If you're a Simpsons fan, you may recall this clip

Yahoo Serious Festival - The Simpsons

If you're not aware, Yahoo Serious is an Australian actor from the movie of the same name. He made it onto Time magazine, then nothing.

But how nifty is that apex_string.split function? And it's cousin join?
Although recently I saw Connor use what appears to be a native option, one that doesn't require an APEX package. On the blog topic list...

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.

What version is my RAD stack?

A common follow up clarification on forums is regarding the version of the relevant tool.
Questions relating to Oracle APEX could be impacted by the APEX version, the database version, and perhaps the ORDS version - in addition to what browser is being used.

The information on the RAD stack can be resolved in one (concatenated) SQL query.

APEX was easy, there is a simple one row view, which ultimately translates to a function returning a literal.
Same with ORDS, I didn't even bother with a scalar subquery when I put it all together.

The Oracle database version had a few options, each with nuance.

1. v$instance is not available to everyone
2. dbms_db_version package variables are not accessible to SQL, unless you use the WITH clause, which is only available in 12c. And I'm not going to write a query based on all_source. It also doesn't provide the version granularity I expected.
3. v$version returns a few rows in the Express Edition. In Enterprise Edition, it is a different "banner_full" column that contains the dot release you really need - among other data
   Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
4. I hadn't heard of product_component_version, but it's a sys view that does all the string processing you need on v$version, but has the same XE/EE column differences. It also only returns one row when not in Express Edition.

So I ended up with the following, executed here in Express Edition.

col apex_version format a15
col ords_version format a15
col db_version format a15

select 
  (select version_no from apex_release) as apex_version
 ,ords.installed_version                as ords_version
 ,(select version /* column name 'version_full' in Standard|Enterprise Edition */
   from product_component_version
   where product like 'Oracle%')        as db_version
from dual;

APEX_VERSION    ORDS_VERSION    DB_VERSION    
--------------- --------------- ---------------
19.1.0.00.15    3.0.6.176.08.46 11.2.0.2.0 

But then I decided it would look better as rows, executed here on an Enterprise Edition database.

col tool format a10
col version_no format a20

select 'APEX' tool, version_no from apex_release
union all
select 'ORDS', ords.installed_version from dual
union all
select 'Database', version_full /* column name 'version' in XE */
from product_component_version
where product like 'Oracle%';

TOOL       VERSION_NO          
---------- --------------------
APEX       18.2.0.00.12        
ORDS       18.4.0.r3541002     
Database   18.3.0.0.0    

APEX and ORDS version is also available form within the App Builder, under Help -> About.

APEX Help -> About

I was curious as to how the WITH function would look, though I had trouble executing it.

with function ver return varchar2 is
  begin
    return dbms_db_version.version || '.' || dbms_db_version.release;
  end;
select ver from dual;

And it returns 18.0, when I expected the 'version_full' value of 18.3.

If you would like to know if a particular APEX patch bundle has been applied, we can use the following function call, with a privileged user.

select APEX_INSTANCE_ADMIN.GET_PARAMETER('APEX_19_2_0_PATCH_30392181') when from dual;

WHEN
-------------------
2019-12-16_13-50-57 

As Hilary suggests, this may change over time.

References

Forum question - How to check the database version
Marko - What is my current ORDS version
Martin - What is my current APEX Version
Hilary (forum) - Has patch x been applied?

Visualising SQL Analytics Rolling Count with OracleJET in APEX

Back in around 2005, before the time of smart phones, I had some data.

I can't remember what the data was, but I was told that for it to be valid, it should roughly form a bell curve.

Sure, I'm sure I could have aggregated it, exported it to Excel, and plotted it to a graph, but this was 2005. There was no SQL Developer where I could copy & paste the results directly into Excel. No panel in Excel with a button that plots that data in one click. Back in my day, thing like that were a little bit more work ;p

This was a time when I enjoyed SQL*Plus, and I ended up writing a query to nest STDDEV within RPAD so it would plot asterisks down the page, in a vertical bell. In was a work of art. I'm sure I've still got the test case labelled in bowels of Gmail.

Fast forward to today, where I have data I know starts as a daily bell curve, but I'm applying a rolling 24 hour total over that daily resonance. How does that affect the wave?

I use today's fancy tools to plot the said data, and it doesn't turn out quite how I pictured.

This is my query below plotting real data using OracleJET charts in Oracle APEX.

The underlying data still formed a bell, more detail in a future post, but smoothing this out to a rolling 24 hour total - what should that look like? Why is there a flat line during no activity, instead of a dip?
No doubt there is a math-geek corner of the internet that explores this pattern, but I still wasn't confident with the query.

Real data is awesome. The trouble is, it can be real noisy. If you're trying to reconcile your results, it's often worth building a simple test case. As a regular forum participant, I'm such an advocate for this. If the person asking the question took the time to write the DDL for the test case, I reckon half the questions wouldn't need asking. The other half will have an executable test case ready for any responders.
How's this - in a response to a question I asked (with a test case, of course), Andrew Sayer responded with a LiveSQL demonstration! AskTom questions that have a livesql.oracle.com example are generally prioritised.

So, what I have here is a real simple table, with some "logs" at specific times, and a little variance. It's quite similar to the setup you might see for SQL exercises in the Oracle Dev Gym.

-- create a simple table of dates
create table range_eg  (rd date);
-- ready for adjustment
truncate table range_eg;
-- insert sample hits at a regular interval, with more frequency around noon.
insert into range_eg values (timestamp '2019-03-26 09:30:00');
insert into range_eg values (timestamp '2019-03-26 10:30:00');
insert into range_eg values (timestamp '2019-03-26 10:45:00');
insert into range_eg values (timestamp '2019-03-26 11:30:00');
insert into range_eg values (timestamp '2019-03-26 11:45:00');
insert into range_eg values (timestamp '2019-03-26 12:30:00');
insert into range_eg values (timestamp '2019-03-26 12:45:00');
insert into range_eg values (timestamp '2019-03-26 13:30:00');
insert into range_eg values (timestamp '2019-03-26 13:45:00');
insert into range_eg values (timestamp '2019-03-26 14:30:00');
insert into range_eg values (timestamp '2019-03-26 15:30:00');
insert into range_eg values (timestamp '2019-03-26 16:30:00');

-- I took a few hits out the middle, so the bell would not be quite the same shape
insert into range_eg values (timestamp '2019-03-27 09:30:00');
insert into range_eg values (timestamp '2019-03-27 10:30:00');
insert into range_eg values (timestamp '2019-03-27 10:45:00');
insert into range_eg values (timestamp '2019-03-27 11:30:00');
--insert into range_eg values (timestamp '2019-03-27 11:45:00');
--insert into range_eg values (timestamp '2019-03-27 12:30:00');
--insert into range_eg values (timestamp '2019-03-27 12:45:00');
insert into range_eg values (timestamp '2019-03-27 13:30:00');
insert into range_eg values (timestamp '2019-03-27 13:45:00');
insert into range_eg values (timestamp '2019-03-27 14:30:00');
insert into range_eg values (timestamp '2019-03-27 15:30:00');
insert into range_eg values (timestamp '2019-03-27 16:30:00');

-- I added a small day at the end, to see how it may affect rolling total drift to zero
insert into range_eg values (timestamp '2019-03-28 11:30:00');
insert into range_eg values (timestamp '2019-03-28 12:30:00');
insert into range_eg values (timestamp '2019-03-28 13:30:00');

Then used an analytical function to cumulate the running count over a 1 day interval.
The second column, with accompanying union, was an attempt to see what happened if I filled out each hour with a record, to see if that influenced the overnight dip in my real data. It also let me see the cumulative count taper off as the data ended.

select * from (
    select rd 
     ,sum(sum(c)) over ( order by rd  
         range between NUMTODSINTERVAL(1,'day') preceding 
                   and current row)  last_24hr_running
     ,sum(count(*)) over ( order by rd  
         range between NUMTODSINTERVAL(1,'day') preceding 
                   and current row)  last_24hr_cnt
     from 
      (select 1 c,rd from range_eg 
      union all
      -- I even tried to pad out records to see 
      -- if the line would vary during the lulls
      select null c, trunc(sysdate,'hh24')+1-rownum/24
      from dual
      connect by level< 24*3
      )
    group by (rd) 
-- must ignore first day in inline query
-- as cumulative range will be skewed
) --where request_date > sysdate-2 
order by 2;

Data checks out, SQL checks out - does the graph check out?

Test case plotted

I think so, but given the context of the data, I don't think this line graph is appropriate, particularly around the period of no activity. More on that in the next post.

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

Remove duplicate from APEX collection

One of my favourite SQL analytic functions is row_number(), and I've used it in the past to identify, then remove duplicates.

In this case, I have an APEX collection that represents a session based view history of products/people/events, or whatever your users might be browsing.

I've created an option to consolidate that view history, and remove any record you might have opened more than once.

Collections are a little hard to play with outside of APEX, so I use the create session procedure in the OraOpenSource libraries to simulate an APEX session within SQL Developer.

exec oos_util_apex.create_session(120,'WESLEYS')

For my test case I simulate adding a few entries in my collection, varying a date column slightly.

begin
apex_collection.create_or_truncate_collection('TEST');
apex_collection.add_member('TEST', 'Scott',  p_d001 => sysdate - 5);
apex_collection.add_member('TEST', 'Dmitri', p_d001 => sysdate - 2);
apex_collection.add_member('TEST', 'Lino',   p_d001 => sysdate - 4);
apex_collection.add_member('TEST', 'Scott',  p_d001 => sysdate - 3);
apex_collection.add_member('TEST', 'Joel',   p_d001 => sysdate - 2);
apex_collection.add_member('TEST', 'Sabine', p_d001 => sysdate - 4);
apex_collection.add_member('TEST', 'Penny',  p_d001 => sysdate - 5);
apex_collection.add_member('TEST', 'Scott',  p_d001 => sysdate - 6);
apex_collection.add_member('TEST', 'Jackie', p_d001 => sysdate - 3);
apex_collection.add_member('TEST', 'Joel',   p_d001 => sysdate - 1);
end;
/

Here is a query that will use an analytical function to add a computed column that identifies the most recent entry for any name, and assign it a 1. Any subsequent entries for that name will get a 2, 3, 4 etc.

select seq_id, c001 name
  ,row_number() over 
    (partition by c001 -- look for duplicates in this set of columns
     order by d001 desc -- put records I want to keep first
     ) rn
     ,d001 dt
from apex_collections
where collection_name = 'TEST'
order by name;

In this case, seq_id 1, 5 & 6 represent the records I want to trim from the list.

Duplicate entries highlighted

Turn the query into an in-line view to filter out any records where the row_number() is not 1, as we are not allowed to have window functions in the where clause (ORA-30483).

select seq_id, rn from
 (select seq_id, row_number() over (partition by c001 order by d001 desc) rn
  from apex_collections
  where collection_name = 'TEST'
) where rn != 1;

    SEQ_ID         RN
---------- ----------
         6          2
         1          2
         5          3

Reverse the order by, and I'll keep the first entry instead.
Got more columns that signify uniqueness? Expand the partition by clause.

A standard aggregate query would be able to identify the names of those duplicates, plus how many you have, but no set of records with the unique identifiers to remove. Using min(seq_id) would be insufficient once you more than two entries.

select count(*), c001 name, min(seq_id)
from apex_collections
where collection_name = 'TEST'
group by c001
having count(*) > 1

  C NAME        MN
--- ---------- ---
  2 Joel         6
  3 Scott        1

We can fold the analytical query into a PL/SQL loop and remove the duplicates from my collection.

begin
  << remove_duplicates >>
  for r_rec in (
    select seq_id, rn from
     (select seq_id, row_number() over (partition by c001 order by d001 desc) rn
      from apex_collections
      where collection_name = 'TEST'
    ) where rn != 1
  ) loop
    APEX_COLLECTION.DELETE_MEMBER
      (p_collection_name =>  'TEST'
      ,p_seq             => r_rec.seq_id);
  end loop remove_duplicates;
end anon;
/

Where the resulting collection is minus the older duplicate.

SEQ_ID     NAME        RN DT        
---------- ---------- --- ----------
         2 Dmitri       1 2018-09-16
         9 Jackie       1 2018-09-15
        10 Joel         1 2018-09-17
         3 Lino         1 2018-09-14
         8 Penny        1 2018-09-13
         7 Sabine       1 2018-09-14
         4 Scott        1 2018-09-15

Of course, this is not limited to APEX collections, but it gave me a chance to play with more toys.

ANSI dates make life easier

This post is one of a series on what I learned while not at Kscope18.

Dimitri mentioned that he learned about the ANSI date format that allows you to return a date with the expression.

Learned something new in @cczarski session to create a date: just use date #Kscope18 pic.twitter.com/mDNeV7QeYH

— Dimitri Gielis (@dgielis) June 11, 2018

Which means this
date '2018-06-10'

Is the same as
to_char('10-Jun-2018','DD-MON-YYYY')

And you'll never want to type the latter again.

I learned this little chestnut as a trainer of SQL, but what I didn't pick up, or have since forgotten, is this

timestamp '2018-06-10 14:33:41'

Thanks again, Connor, for adding to this thread.

ANSI dates

Here's some more info on ANSI dates. It's not lazy, Tanel, it's efficient ;p

Learning is a lifetime pursuit.

Filtering outliers from Oracle APEX activity logs

Last year I described a simple test case that described how to remove outliers from a fictional dataset using the STDDEV() analytical function .
http://www.grassroots-oracle.com/2017/06/removing-outliers-using-stddev.html

I want to follow this up with a practical case using one of my favourite data sets - the apex_workspace_activity_logs that record who opened what page, in what context, and how long it took to generate.

I've been keeping an eye on the performance of a particular page, after making a few performance adjustments to some conditions. Unfortunately, we had an unrelated anomaly that pushed average pages times quite high for a short period. Needless to say, this set of outliers transformed my beautiful performance indicating lines to a boxy bell curve.

Oracle APEX page performance data with extreme outlier

A great feature with Oracle JET is the ability to hide certain series, on click within the legend.
In this case I just wanted to ignore the MAX line for this post, which in this chart forms the secondary y-axis.

OracleJET Region Attributes - rescale

Our clients really enjoy this particular feature (so do I), so thanks to the JET team for building such a device, and the APEX team for integrating it.

This graph shows results where I modified the query to filter the outliers, on demand.

Performance graph with outlier removed

Looks like the adjustments to the conditions worked! The trend is downwards.

I tried a few variations to control the switch, but this seemed to perform the most predictably, although I'm not happy with the hardcoded number.

select [aggregate stuff]
from (
 select [all columns]
,case when :P23_IGNORE_OUTLIERS = 'Y' then
  -- only bother calculating when filtering them out
  stddev(elapsed_time) over (order by null)
else
  9999999
end as the_stddev
from [activity logs]
where [time/page is desired]
)
-- only when elapsed time less than 2 standard deviations gets 95% of your data
where (elapsed_time < 2*the_stddev )

I have a generic example of this on livesql.oracle.com I pay attention to these aggregates for our performance reports

• Median - what most users are experiencing
• Average - a typical user experience, influenced by extremes
• Moving average - general trend of visits, spread over a few days. An attempt to normalise local events
• Max - what's the worst some people are experiencing?

Here are some other activity log queries you may find interesting.

Happy graphing!

Data driven APEX icons

We have an application written with a heavily customised Theme 25 built for 10" tablets, and we feel the Universal Theme justifies the move, in part because of the surrounding ecosystem. Check out this forum discussion on the topic.

We've been looking through the packaged applications for applied ideas, and using the Universal Theme sample application as a component reference.

I came across a requirement where we had a list of items that indicated completion level out of 100.
Then I thought about a group of pie icons I saw in Dick Dral's Font APEX icon reference, and knew what I wanted to do.

All I needed to do was round a bunch of numbers to the nearest 5.

To solve such as problem, I usually start by giving myself a bunch of numbers to play with
select rownum rn from dual connect by level <= 100

Then defining that as a WITH statement, so I can refer to the derived column as often as I like.

with nbrs as (
  select rownum rn from dual connect by level <= 100
)
select rn
 , floor(rn/100 * 20) / 20*100 rnd
 ,'fa fa-pie-chart-'||floor(rn/100 * 20) / 20*100 icon
from nbrs
where mod(rn,5)=0

And I used a divide/multiply by 20 math trick to do the rounding.

We can then feed the result as the relevant icon into whatever region template we need.

I like pie.

Simple, yet effective.

Friday Fun SQL Lesson - union all

Our office kitchen is unavailable this Friday, so the call was put out for pub lunch.

After a couple of replies I decided to enter my reply, in full nerd mode.

select * from people_coming_to_lunch;

People
--------
Kate
Scott
Karolina

3 rows selected.

And of course one of the other SQL geeks (name redacted) replied to extend the data set.

select * from people_coming_to_lunch
union 
select 'Shanequa'
from dual;

And I couldn't help myself. I had to play the performance card and suggest that UNION ALL would be the appropriate usage, and should be the default you type out. Always. Until you decide otherwise.

That's because sorts are expensive. And a UNION will need sorting to check for duplicates.

That sort of all the rows isn't necessary

And it will sort the data set even if there is a unique key on the data.

create table people_coming_to_lunch (people varchar2(30));
insert into people_coming_to_lunch values ('Scott');
insert into people_coming_to_lunch values ('Kate');
insert into people_coming_to_lunch values ('Karolina');

create unique index lunch_people on people_coming_to_lunch(people);

select * from people_coming_to_lunch
union all
select 'Shanequa' from dual

By using UNION ALL instead of UNION, you're telling the database not to even bother sorting the set to eliminate any potential duplicates, since your advanced human brain knows there will be no duplicates.

With only a few rows, the timing of sheer throughput is barely noticable.

iterations:1000
     0.30 secs (.0003 secs per iteration)  -- UNION
     0.25 secs (.00025 secs per iteration) -- UNION ALL
 
iterations:10000
     1.72 secs (.000172 secs per iteration)
     1.09 secs (.000109 secs per iteration)
 
iterations:50000
    10.94 secs (.0002188 secs per iteration)
     8.48 secs (.0001696 secs per iteration)

So I turned it up a notch and added about 5000 rows to the table.

insert into people_coming_to_lunch
select table_name from all_tables;
 
5000 rows inserted

Here's the explain plan without the sort.

That's one less chunk of 5000 rows to process

Now the differences in performance stand out.

iterations:1000
     6.79 secs (.00679 secs per iteration) -- UNION
     2.85 secs (.00285 secs per iteration) -- UNION ALL
 
iterations:5000
    42.91 secs (.008582 secs per iteration)
    19.89 secs (.003978 secs per iteration)
 
iterations:5000
    31.70 secs (.00634 secs per iteration)
    22.83 secs (.004566 secs per iteration)
 
iterations:5000
    30.75 secs (.00615 secs per iteration)
    16.76 secs (.003352 secs per iteration)

Upto twice as long for the same statement?
No thanks, not when I could just type 4 extra characters to get an easy performance win.

Turns out this topic formed my first technical post. Back in 2009, after almost 10 years of using SQL, that was the first thing I blogged about. How about that.

Removing Outliers using stddev()

A colleague asked me about removing outliers from reports and I knew just the function to give a go.

Some time ago I had to prove that some data we had conformed to a normal distribution. Remember those from high school?

A normal distrubition (bell curve)

That request was about 2007 and I ended up using stddev() and lpad() to produce a vertical shaped bell curve in SQL*Plus. That was pretty cool.

As for removing outliers, we can use this tool to exclude a small percentage of whatever data you have, so that it doesn't skew averages.

If you want to look at the code in context, I've created a test case on livesql.oracle.com

But as an inline reference, first I created a simple table, and populated it with a small set of random data between known range.
(I would like to add this is exactly what Tim Hall recently described here in regard to learning and asking questions.)

Importantly, I also inserted an outlier that was an order of magnitude higher than the rest.
My aim was to ignore this outlier in my SQL that calculated average.

create table outlier_demo  (a number, val number);

insert into outlier_demo  
select rownum -- we don't care  
  , round(dbms_random.value(20,50),1) -- some random numbers between 20 and 50  
from dual   
-- let's create 49 rows  
connect by level < 50

insert into outlier_demo values (0,500)

select * from outlier_demo;

A       VAL
---     ----
1       27.9
2       42.5
3       49.7
4       22.3
5       42.3
6       20.2
7       33.9
8       46.8
...
48      45.7
49      21.8
0       500

The average is higher than expected because of that outlier. I've included a column that calculates the average without the outlier, albiet hardcoded. Notice how close it is to the median? When considering usage statistics from APEX, I've often paid more attention to the median (most rows) than the average (affected by outliers).

select count(*) c   
   -- what's our average?   
  ,round(avg(val)) the_avg   
   -- what would the average be without the known outlier?   
  ,round(avg(case when val < 500 then val end)) no_outlier   
  ,round(stddev(val)) std   
   -- what's the median  
  ,round(median(val)) the_median  
from outlier_demo 

C    THE_AVG NO_OUTLIER    STD   THE_MEDIAN
---  ------- ----------  -----   ----------
50        46         37     66           36

We can take the same function and get the result for every row, that's one way we can use it to filter outlier data.

select a, val  
   -- what is 2 standard deviations away  
   -- provide result for each row  
   ,round(2*stddev(val) over (order by null)) local_std  
from outlier_demo

Re-written as a from clause, we can now filter any rows that have a value higher than x standard deviations from the norm.

select count(*) c   
  ,round(avg(val)) the_avg   
  ,round(avg(case when val < 500 then val end)) no_outlier   
  ,round(stddev(val)) new_stddev  
  ,round(avg(local_std)) filter  
from    
  (select a, val   
   -- what is 2 standard deviations away   
   -- provide result for each row   
   ,round(2*stddev(val) over (order by null)) local_std   
   from outlier_demo   
  )   
-- only average those values within two standard deviations   
where val < local_std;

C   THE_AVG NO_OUTLIER  NEW_STDDEV  FILTER
--  ------- ----------  ----------  ------
49       37        37           9      132

This could be re-written in a form that may be more recognisable to some.

select count(*) c
  ,avg(val) the_avg
  ,avg(case when val < 500 then val end) no_outlier
  ,stddev(val) std
from outlier_demo
where val < (select 2*stddev(val) from outlier_demo);

There are probably a number of variations on how to apply this solution, and possibly a few alternative methods.
It will probably depend on the SQL in your particular report.

This is not to say you should be ignoring outliers completely, but stddev() can help identify them where necessary. And it scales.

Thank Kate for this post.

ps - If you want to dive deeper, Jonathan Gennick (my patient book editor) has two in depth posts on the topic, with some diagrams to help understand the mathematical concepts.

Related post:
Filtering outliers from APEX workspace activity logs

Truncating LISTAGG prior to 12.2

I've been following the LISTAGG problem for a while because I find the function useful.

The problem being this error:
ORA-01489: result of string concatenation is too long

It relates to when the result set is over 4000 characters, eg:

with data as (select rpad('x',400, 'x') str from dual connect by level <=10)
select listagg(str,', ') within group (order by null)
from data

Ten rows of 400 characters equals 4000, add the concatenation operator and you get the error.
If the second parameter was null, this would actually be within the 4000 limit.

There are improvements in 12.2 to handle this overflow.

with data as (select rpad('x',400, 'x') str from dual connect by level <=11)
select listagg(str, ',' on overflow truncate '...' with count) within group (order by null)
from data

The output would end
...*********,...(8985)

But if you're still in 12.1 or earlier, you need other options

One solution is a custom LISTAGG function to return a CLOB.

You could make a decision on the incoming length, which probably isn't always practical, or you can concatenate only the first x rows using a CASE statement

with data as (select rpad('x',400, 'x') str from dual connect by level <=10)
select listagg(case when rownum < 5 then str end
              ,', ') within group (order by null)
            ||'First 5 of '||count(*)||' shown.' as str
from data;

Which ends the output with
...********First 5 of 10 shown.

I suspect this will do for a lot of scenarios. Ordering is up to you.

I don't remember encountering this idea in the context of LISTAGG, but it seems nice and simple.

LiveSQL example:
https://livesql.oracle.com/apex/livesql/file/content_EHQAVVYH6J2XMN5VHTFJYT3Y8.html

Answer with SQL: How many weekdays a year?

I'm a big fan of generating data with dual, using a perk of the connect by syntax.

I think Tom Kyte was the originator of this technique. It's not necessarily the fastest method, but it's super convenient - no table required.

Today I wanted to know how many weekdays a year, so I defined 365 rows on the fly using sysdate to turn these into each day of the year. Then I ran a simple select over this to aggregate my result

with years as (
   select to_char(trunc(sysdate,'yy')+rownum-1,'dy') dy
   from dual
   connect by level <= 365
)
select count(*)
      ,sum(case when dy in ('sat','sun') then 1 end) weekends
from years

You can see this demonstrated at livesql.oracle.com
https://livesql.oracle.com/apex/livesql/file/content_D6ZWKMK4O2IVMWGOAXIDEP61M.html
Never heard of it? I recommend you have a play. I've only dabbled myself, but if you don't have an environment to experiment with, this is free!

What should I know about SQL?

Chris Saxon from the Developer Advocates group asked the following question on Twitter.

What three things do you think every developer must know about #SQL ?

Reply and share yours!

— Chris Saxon (@chrisrsaxon) July 5, 2016

My immediate thought regarded features I'd be disappointed to live without. Looking at some other responses I realise that the answer matches Tom Kyte's creed: "it depends".

Here is a collection of responses that I think sums(sql) really well, syntax pun = intended.

What feature should I know, to be productive & efficient?
(me)
1. analytics
2. scalar subqueries
2. with clause

What fundamental principles should I understand?
(Stew Ashton)
1. DDL: data design is key to good SQL
2. ACID
3. Bind variables

What frequent mechanics/behaviour should I be consistently aware of?
(me)
1. null behaviour
2. date arithmetic
3. data type comparison issues, for lack of better desc

All the while remembering architecture is so important.
(Dani Schnider)
1. SQL is a set-based language
2. Clean data model is important
3. Constraints are not just for fun

and so is performance
(Draft A Center? Or Kyle Hailey posting from wrong account?)
1, Bitmap Indexes
2. Reading Explain Plans
3. SQL Tracing

Why there are often exceptions to rules:
(That Jeff Smith)
4. getting results back doesn't mean your code is right.
5. learning never stops

For the creative out there.
(David Fitzjarrell)
6. There is usually more than one way to write a query.
7. Sometimes you need to think 'different'

And there's always the "comedian".
(Connor McDonald)
select, from, where :-)

That's quite the collection!

COALESCE vs NVL poll

Saddened but not surprised to see COALESCE lagging behind NVL.

Weekend #SQL poll: when mapping binds/columns that could contain nulls to non-nulls do you use:

— SQL Daily (@sqldaily) July 1, 2016

Why? Because I think coalesce is a good idea and the modern equivalent of NVL.

I follow @SQLDaily for useful tips. Oracle SQL evangelist Chris Saxon runs the feed.

Simple Unpivot

I came across the need for an UNPIVOT today that require fairly basic syntax, so this is me noting it for later. A single column unpivot, not multiple.

I had a discrete set of values in local variables that I wanted to use within a merge, so I selected them from dual. Here is a literal representations

SELECT 'SCOTT' login_id 
  ,'X' alpha, 'Y' beta
  ,10 catgy1
  ,20 catgy2
  ,30 catgy3
  ,40 catgy4
  ,50 catgy5
  ,60 catgy6
FROM dual
/

LOGIN A B     CATGY1     CATGY2     CATGY3     CATGY4     CATGY5     CATGY6
----- - - ---------- ---------- ---------- ---------- ---------- ----------
SCOTT X Y         10         20         30         40         50         60

 1 row selected

Trouble is, I needed the categories described as rows, not columns. So I wrapped the original query within an unpivot, commenting how the syntax represents the translation.

select login_id, alpha, beta, catgy, quota -- new columns
from ( -- existing query
  select 'SCOTT' login_id 
      ,'X' alpha, 'Y' beta      
      ,10 catgy1
      ,20 catgy2
      ,30 catgy3
      ,40 catgy4
      ,50 catgy5
      ,60 catgy6
  from dual
) -- end existing query
unpivot
(
quota -- new column: value
  for catgy in -- new column translating previously separate columns to discrete data
    ( -- and the column -> data translation listed here
     catgy1 as 'CATGY1' 
    ,catgy2 as 'CATGY2'
    ,catgy3 as 'CATGY3'
    ,catgy4 as 'CATGY4'
    ,catgy5 as 'CATGY5' 
    ,catgy6 as 'CATGY6'
    )
)
/

LOGIN A B CATGY       QUOTA
----- - - ------ ----------
SCOTT X Y CATGY1         10
SCOTT X Y CATGY2         20
SCOTT X Y CATGY3         30
SCOTT X Y CATGY4         40
SCOTT X Y CATGY5         50
SCOTT X Y CATGY6         60

 6 rows selected 

Relatively easy! Hope it helps one day.

You can see the results from these statements at livesql.oracle.com

Talking SQL Analytics with Connor

In about 22 hours I'll be talking with Oracle Developer Advocate (or the southern/eastern hemisphere AskTom adjunct) Connor McDonald about one of my favourite part of Oracle, being SQL Analytics.

If you haven't seen any of his YouTube videos recently, this will be a good chance to catch up and see how easy and practical SQL analytics are, and how you end up with simpler SQL.

I like to think of them like Excel calculations you can put in your result set.

Webinar registration details available here. It starts Tue, Mar 1, 2016 6:00 AM - 7:00 AM CST, or 6am for us Perth people.

Yes, my line has been tested and as long as you only hear my voice it should be cool. Connor apparently lives in a neighborhood more favourable to technology.

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.