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.

Demystifying Oracle Unpivot

A couple of years ago I posted a simple example using PIVOT, converting rows to columns with the classic example of figures by months.

Oracle 11g R1 also introduced the UNPIVOT function, allowing columns to be converted into rows.

Problem

I've created an example that lists cities by row, but two attractions as two columns, with pairing attributes describing the reason for the attraction.

create table aus_attractions(id  number, city varchar2(50)
  , attraction1 varchar2(50)
  , attraction2 varchar2(50)
  , reason1 varchar2(50)
  , reason2 varchar2(50)
  );
insert into aus_attractions values (1, 'Perth','weather','beaches','sunny','white sand');
insert into aus_attractions values (2, 'Sydney','bridge','blue mountains','climb','scenic');
insert into aus_attractions values (3, 'Melbourne','culture','aussie rules','activities','crowds');

select id, city
      ,attraction1,attraction2
      ,reason1, reason2
from aus_attractions;

Not optimal relational data design

I'd like to see each attraction by row - 6 rows instead of 3.

You could solve this with a UNION ALL, and/or a WITH - but one day "they" will ask for 5 options, might as well unpivot.

Solution

Then we can turn our original statement into an inline view, serving the unpivot function.

select id, city, attraction, reason, rec_nbr
from ( -- original query:
     (select id, city
         ,attraction1,attraction2
         ,reason1, reason2
   from aus_attractions
   )
 unpivot               -- the magic operator
 ((attraction, reason) -- names of replacement columns
  for rec_nbr in (     -- new column defining data source in literal alias below
   -- split each group of fields in here
   (attraction1, reason1) as 'REC1' 
  ,(attraction2, reason2) as 'REC2'
  )
 )
);

ID      CITY       ATTRACTION        REASON       REC_NBR
------  ---------- ----------------- ------------ -------
1       Perth      weather           sunny        REC1
1       Perth      beaches           white sand   REC2
2       Sydney     bridge            climb        REC1
2       Sydney     blue mountains    scenic       REC2
3       Melbourne  culture           activities   REC1
3       Melbourne  aussie rules      crowds       REC2

6 rows selected

Awesome.

Unpivoted data

Simple, once you've done it the first time...

Documentation

Oracle SQL Language Reference
Oracle Data Warehousing Guide - SQL for Analysis and Reporting

Other great examples of varying depth

OTN - Arup Nanda
Oracle-Base - Tim Hall
Oracle FAQ - Unpivot
Oracle-developer.net - Adrian Billington
AMIS - Lucas Jellema
SQL Snippets: Columns to Rows - UNPIVOT (11g)

If you're not already using the above sites for good reference material, you're missing out.

Also check out this example demonstrated at live.oracle.com

The trick with triggers

Creating triggers, prior to 11g, would default them to an enabled state.

From 11g, we have this in the documentation:

By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement.

So when I write my DDL scripts, given this behaviour, I know what I'd prefer:

SQL> create table test_table(a number);
table TEST_TABLE created.
create or replace trigger test_trigger_good before insert on test_table for each row DISABLE
begin
  null -- missing semicolon
end;
/
TRIGGER test_trigger_good compiled
Warning: execution completed with warning

SQL> insert into test_table (a) values (1);

1 rows inserted.

or

SQL> create or replace trigger test_trigger_bad before insert on test_table for each row 
begin
  null -- missing semicolon
end;
/
TRIGGER test_trigger_bad compiled
Warning: execution completed with warning

SQL> insert into test_table (a) values (1);

SQL Error: ORA-04098: trigger 'DEVMGR.TEST_TRIGGER_BAD' is invalid and failed re-validation
04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
           found to be invalid.  This also means that compilation/authorization
           failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
           disable the trigger, or drop the trigger.

Only run this when you know things are cool, ie - the triggers does not have compilation errors.

alter trigger test_trigger_good enable;

Otherwise you get the same problem, since you can enable an invalid trigger.

Tip of the hat to Connor McDonald for suggesting this many moons ago.

Fun with SQL analytical functions

I had an interesting SQL problem at work recently, and I came up with a solution that I'm not sure is completely ideal - so I thought I'd attempt to replicate it here.

Some of the complexity is lost while I obscure and simplify the problem (it involved a hierarchical query), but I think the key elements remain.

Consider a table of codes with an order sequence.

create table my_values (code varchar2(10), order_seq number(5));
insert into my_values values ('A', 10);
insert into my_values values ('A', 20);
insert into my_values values ('A', 30);
insert into my_values values ('B', 40);
insert into my_values values ('B', 50);
insert into my_values values ('C', 60);
insert into my_values values ('C', 70);
insert into my_values values ('C', 80);
insert into my_values values ('D', 90);

My requirement was to see the next and previous code. For instance, when listing B records, I wanted to see A and C in the same row - the solution screamed analytical functions so I started with my favourite:

select code, order_seq
      ,row_number() over (partition by code order by order_seq) rn
from my_values
order by order_seq;

CODE       ORDER_SEQ RN
---------- --------- --
A                 10  1 
A                 20  2 
A                 30  3 
B                 40  1 
B                 50  2 
C                 60  1 
C                 70  2 
C                 80  3 
D                 90  1

I struck the results down to just the first row for each code, incorporating lag/lead to get the info I needed.

select s.code
      ,lag(s.code) over (order by s.order_seq) my_lag
      ,lead(s.code) over (order by s.order_seq) my_lead
from 
(
  select code, order_seq
        ,row_number() over (partition by code 
                            order by order_seq) rn
  from my_values
  order by order_seq
) s
where s.rn = 1
order by order_seq;

CODE       MY_LAG     MY_LEAD  
---------- ---------- ----------
A                     B          
B          A          C          
C          B          D          
D          C                    

Finally, to combine my results I created an in-line view with a subquery factoring statement.

with sub as (
  select s.code
        ,lag(s.code) over (order by s.order_seq) my_lag
        ,lead(s.code) over (order by s.order_seq) my_lead
  from 
  (
    select code, order_seq
          ,row_number() over (partition by code 
                              order by order_seq) rn
    from my_values
    order by order_seq
  ) s
  where s.rn = 1)
select m.*, sub.my_lag, sub.my_lead
from my_values m, sub
where m.code = sub.code;

CODE       ORDER_SEQ MY_LAG     MY_LEAD  
---------- --------- ---------- ----------
A                 10            B          
A                 20            B          
A                 30            B          
B                 40 A          C          
B                 50 A          C          
C                 60 B          D          
C                 70 B          D          
C                 80 B          D          
D                 90 C                     

So my question is - any ideas for a simpler solution?

Oracle PIVOT

A common requirement for queries is to turn rows into columns, or the other way around.

In Excel, we can do this using TRANSPOSE, a bit of patience & know-how, and ctrl+shift+enter.

In Oracle, if we have aggregate data displaying months by row, the old way was to use a bunch of DECODEs (or similar)

SELECT t.name
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jan',1,0)) jan
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'feb',1,0)) feb
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'mar',1,0)) mar
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'apr',1,0)) apr
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'may',1,0)) may
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jun',1,0)) jun
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'jul',1,0)) jul
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'aug',1,0)) aug
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'sep',1,0)) sep
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'oct',1,0)) oct
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'nov',1,0)) nov
  ,SUM (DECODE (TO_CHAR (e.start_date,'mon'),'dec',1,0)) dec
FROM events e, bookings b, resources r, resource_types t
WHERE e.event_no = b.event_no
AND r.code = b.resource_code
AND r.type_code = t.code
GROUP BY t.name;

NAME                 JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
-------------------- --- --- --- --- --- --- --- --- --- --- --- ---
Catering               0   0   2   1   0   0   0   0   0   0   0   0 
Stationary             0   0   1   1   0   0   0   0   0   0   0   0 
Video equipment        0   0   1   1   1   0   0   0   1   0   0   0 
Audio equipment        0   0   0   0   0   0   0   0   0   1   0   0 
Computer equipment     0   0   1   0   0   0   0   0   0   0   0   0 
Locations              0   0   2   2   2   1   1   1   1   1   0   0 

6 rows selected 

Oracle 11g introduced pivot queries.

SELECT * FROM
 ( SELECT COUNT(*) c, t.name, TO_CHAR(start_date,'mon') mth
   FROM events e, bookings b, resources r, resource_types t
   WHERE e.event_no = b.event_no
   AND r.code = b.resource_code
   AND r.type_code = t.code
   GROUP BY t.name, to_char(start_date,'mon')
)
PIVOT
 (SUM(c) -- Add up all my counts
  FOR mth -- Transposing the months
    IN ('jan' as jan
             ,'feb','mar','apr','may','jun'
       ,'jul','aug','sep','oct','nov','dec')
);

NAME                 JAN 'feb' 'mar' 'apr' 'may' 'jun' 'jul' 'aug' 'sep' 'oct' 'nov' 'dec'
-------------------- --- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
Catering                           2     1                                                 
Stationary                         1     1                                                 
Video equipment                    1     1     1                       1                   
Audio equipment                                                              1             
Computer equipment                 1                                                       
Locations                          2     2     2     1     1     1     1     1             

6 rows selected 

Note line 12 where we can provide column aliases to the fresh output.

As with most esoteric SQL functions, there are quite a few good examples on the web that I'm not out to emulate - the prime purpose of this post was to help remind me what's going on.
That being said, Tim Hall and Arup Nanda have the most concise articles.

I particularly like Lucas Jellema's example linked by Arup using

select value
from
(
    (
        select
            'a' v1,
            'e' v2,
            'i' v3,
            'o' v4,
            'u' v5
        from dual
    )
    unpivot
    (
        value
        for value_type in
            (v1,v2,v3,v4,v5)
    )
)
/

I wonder if that might be an interesting catalyst in some future queries
Update August 2014 - yes it did! -> Unpivot

More fun with dates

Last week I had a post reminding us to consider the time component of our date data types.

Here I'm just listing some fun with dates, either from single row sql expressions or cool little queries.

select 
  sysdate today
 ,trunc(sysdate) midnight_today
 ,trunc(sysdate)+1/86400 one_second
 ,trunc(sysdate)+2/24 two_hours
 ,trunc(sysdate)+30/60/24 thirty_minutes
 ,trunc(sysdate) + interval '2:30' hour to minute two_hour_thirty
 ,trunc(sysdate,'mm') start_of_month
 ,trunc(sysdate,'yy') start_of_year
 ,add_months(trunc(sysdate,'yy'),6) financial_year
 ,extract(year from sysdate) year_as_number
 ,to_char(sysdate,'yyyy') year_as_varchar2
 ,(sysdate - add_months(sysdate,6)) year to month  interval_months
 ,(Sysdate-(sysdate+2+30/60/24)) day(9) to second interval_daysec
 ,to_date('2012','yyyy') start_of_month
 ,last_day(sysdate) end_of_month
 ,date '2012-02-28' + 1 leap_year
 ,date '2011-02-28' + 1 non_leap_year
 ,date '2012-02-28' + interval '1' day only_during_leap_year
 ,date '1582-10-4' + 1 gregorian_changeover
 ,to_date('01-01-4712bc','dd-mm-yyyybc') scaliger_start
 ,to_date(1,'j') easier_scaliger_start
 ,to_date('31-12-9999','dd-mm-yyyy') end_of_time
 ,to_date('01-01-98','dd-mm-yy') legacy_wrong_century
 ,to_date('01-01-98','dd-mm-rr') legacy_better_format
from dual;

-- Generate first day of each month
select add_months(trunc(sysdate,'yy'),rownum-1) months
from dual connect by level <= 12;

MONTHS
---------------------
01/01/2012 00:00:00
01/02/2012 00:00:00
01/03/2012 00:00:00
01/04/2012 00:00:00
01/05/2012 00:00:00
01/06/2012 00:00:00
01/07/2012 00:00:00
01/08/2012 00:00:00
01/09/2012 00:00:00
01/10/2012 00:00:00
01/11/2012 00:00:00
01/12/2012 00:00:00

12 rows selected

-- Generate list of this year's weekends
select dt from (
  select trunc(sysdate,'yy')+rownum-1 dt
  from dual connect by level <= 365)
where to_char(dt,'dy') in ('sat','sun');

DT                  
---------------------
01/01/2012 00:00:00   
07/01/2012 00:00:00   
08/01/2012 00:00:00   
14/01/2012 00:00:00   
...
23/12/2012 00:00:00   
29/12/2012 00:00:00   
30/12/2012 00:00:00   

105 rows selected 

select sysdate@! wtf_is_this from dual;

Further reading on SQL injection with dates

http://tkyte.blogspot.com.au/2012/02/all-about-security-sql-injection.html

Remember date contains time

This is one thing I regularly mention to trainees, and recently I fell for the problem myself!

Dates may contain a time component.

Simple example, some might expect this statement to return a result (as I write this being 1st March) - it does not.
select * from dual where sysdate <= date '2012-03-01';

For the pure reason that sysdate returns century, year, month, hour, minute, second. And so may some of the dates stored in your database.

SQL> select to_char(sysdate,'dd-Mon-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')
------------------------------------------------------------
01-Mar-2012 20:49:28

Here is a simple test case where ideally I would get two rows -"now" & "midnight today", and the build-up to this scenario:

create table my_dates(descr varchar2(50), dt date);
insert into my_dates values ('yesterday', sysdate-1);
insert into my_dates values ('now', sysdate);
insert into my_dates values ('midnight today', trunc(sysdate));
insert into my_dates values ('midnight tomorrow', trunc(sysdate)+1);
insert into my_dates values ('tomorrow', sysdate+1);

-- all dates
select * from my_dates;

DESCR                                              DT                  
-------------------------------------------------- ---------------------
yesterday                                          29/02/2012 19:40:12   
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   
midnight tomorrow                                  02/03/2012 00:00:00   
tomorrow                                           02/03/2012 19:40:12   

-- equal to today
select * from my_dates
where dt = date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
midnight today                                     01/03/2012 00:00:00   

-- still equal to today
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
midnight today                                     01/03/2012 00:00:00   

-- better, but includes midnight tomorrow
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01'+1;

-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   
midnight tomorrow                                  02/03/2012 00:00:00   

-- up to one second before midnight
select * from my_dates
where dt between date '2012-03-01'
         and     date '2012-03-01'+1-1/86400;
DESCR                                              DT                  
-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   

-- excluding the ability to use an index on dt
select * from my_dates
where trunc(dt) = date '2012-03-01';

DESCR                                              DT                  
-------------------------------------------------- ---------------------
now                                                01/03/2012 19:40:12   
midnight today                                     01/03/2012 00:00:00   

Here are other date related posts:

• Date format tolerance
• Interval issues
• Data densification - filling dates

Scott

Wrong number or types of arguments to what?!

Have you ever received the following error?

SQL> select x.abc from dual x;
select x.abc from dual x
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'

If you have, the reason is the planets aligned in the Oracle world just to cause confusion.

• you're pre 11g
• you've mis-typed a column
• you're using a simple alias

Essentially, prior to 11g, there are two synonyms defined on the database who's name share some commonly used aliases.

SQL> select synonym_name, table_owner, table_name from all_synonyms where synonym_name in ('X','Y');

SYNONYM_NAME         TABLE_OWNER          TABLE_NAME
-------------------- -------------------- --------------------
X                    MDSYS                OGC_X
Y                    MDSYS                OGC_Y

So if you've mis-typed a column, Oracle tries to work out what your identifier is mapping to and if it finds some random match, it will report a seemingly random error.

These days the public synonyms are more appropriately called OGC_X and OGC_Y, but that doesn't mean you might not have any functions or synonyms defined in your own database that might also be used as table aliases. Heck, when I wrote this post I confused myself again because I had a dummy function in my database called XY.

So there are ways to circumvent things like this happening

1. Use a standard aliases for your tables - this seems pedantic, but it's worthwhile. They're not hard to conjure - first three letters (organisations org), first letter of each word (resource_types rt) - but keep it consistent. This will make life easier for developers that need to read SQL, and even Oracle likes you to use aliases for performance reasons.
   At one site, our code didn't pass muster if our table aliases didn't match the prescribed list.
2. Make your functions/synonyms descriptive - nobody wants to find a function called X or ABC and have to chase up what it does. Use some standard naming conventions.
3. Don't make mistakes in your SQL - but if when you do, learn to recognise the reported errors to help nut out the typo you've made - don't just ignore the error to sit & stare. While the error message doesn't always report the exact line/position of the actual problem, the hints it provides are typically consistent in some form - computers are dumb.
4. Upgrade your database - unrelated, just typically a good move ;-)

Scott

Updating key-preserved inline views

I learnt something today - there is a scenario where you need update privileges on a table you aren't updating.

Here is my example - I have a table TRAIN_APEX.RESOURCES that I would like to update based on TRAIN.RESOURCE_RATES. So the key factor here is my source table is in a different schema to my destination table - and I only have select privileges on resource_rates.

TRAIN_APEX> SELECT privilege, grantee, table_name FROM all_tab_privs where table_name = 'RESOURCE_RATES';

PRIVILEGE            GRANTEE              TABLE_NAME
-------------------- -------------------- --------------------------------------------------------
SELECT               TRAIN_APEX           RESOURCE_RATES

1 row selected.

This means when I attempt to run an update that uses an elegant key-preserved in-line view, I can't do it!

TRAIN_APEX> UPDATE
  2    (SELECT r.type_code
  3           ,a.standard_rate
  4           ,r.code
  5           ,r.daily_rate
  6     FROM train_apex.resources r
  7         ,train.resource_rates a
  8     WHERE a.type_code = r.type_code)
  9  SET daily_rate = standard_rate;
       ,train.resource_rates a
              *
ERROR at line 7:
ORA-01031: insufficient privileges

I've had a look through the documentation for views and update statements, and I can't see it as a pre-requisite.

Out of curiosity I defined the statement as an actual view to confirm the base table is key-preserved. I knew this already because I've successfully run this statement when the tables are in the same schema, or in scenarios where my account has UPDATE ANY TABLE.

TRAIN_APEX> CREATE VIEW resources_vw AS
  2  SELECT r.type_code
  3        ,a.standard_rate
  4        ,r.code
  5        ,r.daily_rate
  6  FROM train_apex.resources r
  7      ,train.resource_rates a
  8  WHERE a.type_code = r.type_code;

View created.

Elapsed: 00:00:00.05
SQL>
SQL> @view_dml resources_vw

COLUMN_NAME          INSERTABL UPDATABLE DELETABLE
-------------------- --------- --------- ---------
TYPE_CODE            YES       YES       YES
STANDARD_RATE        NO        NO        NO
CODE                 YES       YES       YES
DAILY_RATE           YES       YES       YES

4 rows selected.

So instead, I have to run a "normal" update with a condition to check for presence of rows in my source table, which also means my update isn't as efficient.

UPDATE train_apex.resources r
SET daily_rate =
  (SELECT standard_rate
   FROM train.resource_rates a
   WHERE a.type_code = r.type_code)
WHERE EXISTS
  (SELECT NULL
   FROM train.resource_rates a
   WHERE a.type_code = r.type_code)

Do you think this is a bug or expected behaviour?

Scott

Local DB died, local developer doesn't panic

My database crashed on my dad's birthday.

There's no correlation to be made there, let alone causation.

“The invalid assumption that correlation implies cause is probably among the two or three most serious and common errors of human reasoning.” Steven J Gould.

And if you haven't read it, Tom Kyte has a great Oracle related article on the matter. His old AskTom link is dead, but here is one from Oracle China - I think the transmission is by carrier pigeon, or a 9000 baud modem, but give it a few minutes. Similar to opening a sensis website, really - except it's all text.

Not a DBA!!!

Now my DBA hat is really small. I can't open this like that jeff smith fellow.

Nope, the first thing I did was informed my boss that my laptop blue screened - first time on this sucker, not long after I noticed Glassfish gone and the Oracle DB simmering on the ashes.

After a quick layman's look following a hunch, I found my SYSTEM tablespace was lacking elbow room.

I thought I'd lost the original figures in another crash (opening the lid from hibernation), but I found them in my e-mail to Penny.

SQL> @free sys%

NAME       KBytes         Used         Free   Used      Largest

----------------- ------------ ------------ ------ ------------

SYSAUX    768,000      630,848      137,152   82.1       40,960
SYSTEM  1,249,280    1,244,032        5,248   99.6        5,120

With my uneducated eye, I deduced that SYSTEM Used 99.6% was called a "vital clue".

So in another script I had hidden away, some other little tip Penny gave me one day, was to make a bigger canvas, so to speak

alter tablespace system
add datafile 'C:\app\Scott\oracle\11.2.0\oradata\sw11g\SYSTEM02.dbf'
SIZE 1000M;

And once Penny got back to me with another suggestion:

select owner,segment_name,segment_type
      ,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM'
and    bytes/(1024*1024)> 1
order by size_m desc
/

OWNER SEGMENT_NAME    SEGMENT SIZE_M
----- --------------- ------- ------
SYS   AUD$            TABLE      360
SYS   IDL_UB1$        TABLE      288
SYS   SOURCE$         TABLE      120
SYS   IDL_UB2$        TABLE       40
SYS   C_TOID_VERSION# CLUSTER     23
SYS   C_OBJ#_INTCOL#  CLUSTER     22
SYS   ARGUMENT$       TABLE       16
SYS   I_SOURCE1       INDEX       15
SYS   C_OBJ#          CLUSTER     14  

She palmed me off to the relevant documentation where I could do some trimming. 11.2.0.1 for those watching at home.

Here's what I came up with

BEGIN
  DBMS_AUDIT_MGMT.SET_DEBUG_LEVEL(DBMS_AUDIT_MGMT.TRACE_LEVEL_ERROR); -- didn't work?
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 24*7 /* hours */);
END;
/

BEGIN
  DBMS_AUDIT_MGMT.create_purge_job(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    audit_trail_purge_interval => 24*30 /* hours */,
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    use_last_arch_timestamp    => FALSE);
END;
/

I was originally having problems with seeing the trace, as I didn't have enough temporary space (or something similar, I lost the actual message) in SYSAUX, so I gave that tablespace another 300M datafile.

Now my free space looks like I have enough room to swing a dinosaur, and I haven't had any velociraptors opening doors since.

SQL> @free sys%


NAME       KBytes         Used         Free   Used      Largest
----------------- ------------ ------------ ------ ------------
SYSAUX  1,075,200    1,015,680       59,520   94.5       43,008
SYSTEM  2,273,280      875,840    1,397,440   38.5    1,022,976
     ------------ ------------ ------------
sum     3,348,480    1,891,520    1,456,960

I saw Jeff Smith's article about his ORA-3113 issue via twitter. After locating my alert log, I couldn't track down anything wrong from where I first saw issues, but I'm not used to reading these logs.

Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
ERROR: Unable to normalize symbol name for the following short stack (at offset 199):
dbgexProcessError()+193<-dbgeExecuteForError()+65<-dbgePostErrorKGE()+1726<-dbkePostKGE_kgsf()+75<-kgeade()+560<-kgerev()+125<-kgerec5()+60<-sss_xcpt_EvalFilterEx()+1869<-sss_xcpt_EvalFilter()+174<-.1.4_5+59<-00000000775A85A8<-00000000775B9D0D<-00000000775A91AF<-00000000775E1278<-kgllkal()+151<-kglLockCursor()+188<-kxsGetLookupLock()+146<-kkscsCheckCursor()+326<-kkscsSearchChildList()+1067<-kksfbc()+12294<-kkspsc0()+2117<-kksParseCursor()+181<-opiosq0()+2538<-kpooprx()+357<-kpoal8()+940<-opiodr()+1662<-PGOSF523_kpoodrc()+32<-rpiswu2()+2757<-kpoodr()+717<-xupirtrc()+2739<-upirtrc()+124<-kpurcsc()+150<-kpuexec()+9766<-OCIStmtExecute()+70<-kewrose_oci_stmt_exec()+79<-kewrgwxf1_gwrsql_exft_1()+407<-kewrgwxf_gwrsql_exft()+620<-kewrews_execute_wr_sql()+72<-kewrftbs_flush_table_by_sql()+210<-kewrft_flush_table()+150<-kewrftec_flush_table_ehdlcx()+454<-kewrfat_flush_all_tables()+1021<-kewrfos_flush_onesnap()+167<-kewrfsc_flush_snapshot_c()+613<-kewrafs_auto_flush_slave()+548<-kebm_slave_main()+856<-ksvrdp()+2506<-opirip()+965<-opidrv()+909<-sou2o()+98
Sun Feb 26 19:16:53 2012
Errors in file c:\app\scott\diag\rdbms\sw11g\sw11g\trace\sw11g_smon_7520.trc  (incident=73309):
ORA-00600: internal error code, arguments: [25027], [2], [2965385640], [], [], [], [], [], [], [], [], []
Errors in file c:\app\scott\diag\rdbms\sw11g\sw11g\trace\sw11g_m002_7944.trc  (incident=73461):
ORA-07445: exception encountered: core dump [kgllkal()+151] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x92A2D07] [UNABLE_TO_READ] []
Incident details in: c:\app\scott\diag\rdbms\sw11g\sw11g\incident\incdir_73309\sw11g_smon_7520_i73309.trc
Incident details in: c:\app\scott\diag\rdbms\sw11g\sw11g\incident\incdir_73461\sw11g_m002_7944_i73461.trc
Non-fatal internal error happenned while SMON was doing cursor transient type cleanup.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Sun Feb 26 19:17:01 2012
Trace dumping is performing id=[cdmp_20120226191701]

Maybe it might help someone on day, or someone can add further detail.

References

• CNOUG's Archiver 
• Steven J Gould Wiki
• Auditing Enhancements 11gR2
• That Jeff Smith

Free Oracle 11.2 database just released

Kris Rice has just announced the release of Oracle Express Edition 11.2.

This is a free product you can download, install & learn. A thorough description of the exact licencing can be found here.

It's very easy to install, just like installing any other software on your computer. I created a document with some step-by-steps to help get people learning started - including logging in with SQL Developer to establish some users, instead of using SYS.

Go, install, play, learn.

Scott

Recursive Subquery Factoring

OK, this post is partially for my benefit because I'm sure in future I'll need to re-think how this works - and I'll want the basic syntax on hand.

From 11g Release 2, the SQL WITH clause has been extended to allow recursive queries. This new syntax complies with the ANSI standards, as opposed to Oracle's CONNECT BY, START WITH keywords.

It's officially called "recursive subquery factoring", but it's also known as tree-walking or a hierarchical query.

Unfortunately in this simple example, the ANSI syntax is somewhat more verbose. I wonder if this evens out as the complexity of the query increases, or if the readability of the code is "scalable"?

-- 10g method
SELECT o.org_id, o.name, o.parent_org_id, level
FROM organisations o
CONNECT BY PRIOR org_id = parent_org_id
START WITH org_id = 1000;

-- 11 method
WITH org_sage (org_id, name, parent_org_id, reportlevel) AS
  (SELECT org_id, name, parent_org_id, 1 reportlevel
   FROM   organisations
   WHERE  org_id = 1000
   UNION ALL
   SELECT o.org_id, o.name, o.parent_org_id, reportlevel+1
   FROM  org_sage p, organisations o
   WHERE p.org_id = o.parent_org_id
)
SELECT org_id, name, parent_org_id, reportlevel
FROM org_sage;

Another unfortunate outcome is a quick test of throughput - 10000 iterations on my laptop gave the following respective timings.

.81 secs
.000081 secs per iteration
3.56 secs
.000356 secs per iteration

So it might be best to compare the two in your scenario/data, and consider the value of using the ANSI format in your case.

Further documentation can on hierarchical queries can found here, and in the SQL Language Reference, under the SELECT statement, looking at subquery factoring.

Remember, the key to understanding recursion is understanding recursion :-)

Conditional Compilation in 11g

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

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

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

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

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

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

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

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

PRECEDES follows FOLLOWS

Thanks for a tip-off from volleyball coach / Oracle guru Connor McDonald (and later by colleague Chris Muir), it seems 11gR2 was released while I was 30000ft in the sky.

I wouldn't be practising what I preach if I didn't point you to one of the best books in the online Oracle Documentation - the New Features Guide.

If you want to keep up with Oracle Technology, and learn a thing or two, every time a new version of Oracle is release, I highly recommend a peruse through this book.

Keep a lookout in the blog community because plenty of articles pop-up around these times showing off the shiny new features. One feature I'll mention today is an extension to triggers.

In a recent presentation I included some thoughts on compound triggers and a quick note on the FOLLOWS clause, allowing you to indicate that a trigger fire after a specified trigger.

This can be useful if you need to extend proprietary Oracle software.

create or replace trigger package_trigger
after update of salary
on employees
for each row
begin
dbms_output.put_line('package_trigger');
end old_way;
/

create or replace trigger custom_stuff
after update of salary
on employees
for each row
follows package_trigger
begin
dbms_output.put_line('custom_stuff');
end old_way;
/

I don't know whether it was an afterthought or this was just one of the last features installed in 11gR1 at the pleading request of a client, but it was a little odd that a PRECEDES type functionality wasn't included.

However now in 11gR2, this is now available.

There are caveats however, and in this case PRECEDES may only be applied to a reverse cross edition trigger - this is also a whole new ball game and I can't yet confidently tell you more about editions, except that Connor's been excited about the prospect of these for quite some time & it's impact throughout the database seems widespread.

Other features for the handy developer to keep an eye out for include:

• Enhancements to the Forms->Apex conversion process
• Analytical function improvements
• Recursive WITH clause - ANSI compliant hierarchical queries
• Flashback support for DDL
• Improvements to Oracle Scheduler

Oracle keeps on Oracling.

Presentations

Many moons ago when I first started as an Oracle Developer, I worked on a project with Penny. I wasn't under her employ then, but we were playing with materialized views in an 8.1.7 database. Fairly cutting edge at the time she suggested I do a presentation on it for the conference. Green as I was straight out of university I graciously declined, but ever since I searched for a topic I'd be confident in presenting. One day on a blog I stumbled on someone's response to a simple query that suggested using the Model Clause and for some reason this peaked (not piqued) my interest. I've never looked back since and ideas just keep coming.

So to those contemplating it, just go for it. It's like riding a bike. And you learn so much about Oracle when you're essentially forced to tinker & play.

Below is a list of presentations I've done so far.
(And yes, it turns out I'm a fan of alliteration)

Update December 2017
There is a larger, more up-to-date list on this dedicated page:
http://www.grassroots-oracle.com/p/presentations.html

***************

Oracle Apex 4.1 Security  ( prezi | bookmark )

There have always been many options for securing Oracle Apex applications, and many of them don't require much effort - just a little understanding.

This presentation will cover all things Apex Security. Scott covers discussions and examples of many of Apex's security features, including changes to Authentication & Authorisation in 4.1 towards using plug-ins.

AUSOUG WA Conference 2011 - Awarded Best Paper

Oracle Apex Performance  ( pdf | prezi | bookmark )

Over the years there have been countless technical and social presentations doting on 5, 10, 12 ways to improve this, that and the other.

I will go through various performance tweaks (not tweets) for Application Express without limiting myself to a golden number.

These improvements will vary from simple PL/SQL refactoring; to monitoring for bottlenecks in your application; to cutting down maintenance time - which relates to the performance of you as an Oracle developer with only 24 hours in a day.

We may even visit a little Apex instrumentation on the way.

AUSOUG WA Conference 2010
AUSOUG SA Conference 2011
Virtathon 2011
InSync Sydney Conference 2011

Apex with Oracle Text ( pdf | inline | bookmark )

Oracle Text is a facility within the database that provides more advanced indexing & search techniques - including the ability to index documents stored in your database; on your server; or even the web!

Now you can incorporate this functionality into your web application using Application Express.

This presentation will demonstrate how easy it is to combine the two, and give you a platform for further expansion and exploration within a very powerful product.

AUSOUG WA Branch Meeting 2010

Trials & Tribulations of an Oracle Forms -> Apex Conversion ( pdf | bookmark )

Abstract: One of the hot topics these days questions how long you should keep hold of your long standing, hard working Forms application. Oracle support timeframes for Forms has been quite fluid over recent years and ultimately you may need to make a move..

Depending on the size and complexity of your application, a number of options present themselves.
Application Express is one such option. The question remains, however, is there some black box tool we can use to plug our Forms in and have it pump out Apex pages? And how much assistance does this tool need before and after the conversion process?

This presentation will cover some of the considerations you may need to make when contemplating this event. We'll show what happens when you convert a form with various types of components and reveal the gaps, if any, you'll find at the other end.

Let's not forget the other components of our Forms application. What about PL/SQL libraries, reports, menus? Is it worth tackling some of these items manually? We shall see...

AUSOUG WA and Vic National Conference 2009 - Oracle ACE ODTUG Stream

Oracle Documentation ( inline | bookmark )

Abstract: If you're a database developer, regardless of whether you have 6 months or 6 years experience, you need a good reference manual.
By good, I mean one that you can locate what you need within seconds. I know you know what I mean...

I'd like to show you how easy the free Oracle supplied documentation really is to use. And if for some reason it still doesn't cater to your needs, I'll show you some other methods and destinations that might save you a few headaches.

In this short presentation, I'll show you how to find most day to day documentation requirements in 2 clicks, maybe 3 if you're unlucky - without connecting to the net.
You might also hear some other new words such as Ubiquity & Bookmarklets.

AUSOUG WA Branch Meeting 2009

11g New Features ( inline | ppt | bookmark )

Abstract: There are a wealth of new features available in the 11g database release. This presentation touches on SQL & PL/SQL features I found of interest, and concentrates particularly on virtual columns.
Relevant scripts are available here.

ACTOUG May 2009

Creative Conditional Compilation ( inline | ppt | bookmark )
Abstract: Oracle released a feature in 10g Release 2 they thought worthy of facilitating in previous versions via patch sets - so I thought it was worthy enough for a closer look.

Conditional compilation isn't a foreign concept in the programming world, and for the developer aficionado it's a wonderful paradigm to explore.

Conditional compilation was designed with the main intention of being able to create database version specific code. With the recent advent of 11g, developers can actually start adding 11g features to their 10g code today!

However it provides the savvy PL/SQL developer to enhance their code in more ways than just gearing up for the next release… Dust of your software engineering hats and discover how to utilise conditional compilation to explore concepts such as latent self tracing code; latent assertions; and enhanced prototyping for your unit tests.

This seminar will illustrate several examples of conditional compilation that will open your mind; ultimately benefit your users; and can be implemented as far back as 9.2!

AUSOUG WA and QLD National Conference 2008
AUSOUG VIC Branch Meeting 2009
AUSOUG Qld Branch Meeting 2010


Be a Bulk Binding Baron ( inline | pdf | bookmark )

Abstract: Developers - If you are not using Bulk Binds you are not writing PL/SQL efficiently!

Bulk binding has been around for a long time, yet there are sites out there that don't utilise this feature to its full extent, if at all. Every release of Oracle improves on this functionality so obviously it's a topic worthy of consistent awareness.

In PL/SQL and SQL, there are a few nifty features related to bulk binding you may not have seen - it's not all about BULK COLLECT. Whether you're on 8i, 11g or anything in between, you'll benefit from the concepts described in this seminar and become a Bulk Binding Baron!

AUSOUG WA Branch Meeting 2008
AUSOUG SA Conference 2011

The Model Clause ( inline | bookmark )

Abstract: The session will breakdown the Model clause into its fundamental components and provides some basic real-world examples to demonstrate its greater potential.

Though most developers have heard of the SQL Model clause in 10g, many may baulk at the idea of using it - daunted by seemingly foreign syntax that might well have come out of a FORTRAN program.

Look a little closer and you'll find it's just like building a spreadsheet. Concise, easy to read syntax that provides the functionality for demanding calculations that would normally require elaborate joins, unions, analytics or PL/SQL. In addition to the development and maintenance burden, we are also faced with the all too familiar problem of business customers duplicating data to an Excel spreadsheet that is shared and erroneously modified around the workplace.

This session uses the Model clause as a high performance tool that can simplify approaches to every day problems. It demonstrates that Model is an extension to SQL that forms multi-dimensional arrays with inter-row & inter-array calculations that automatically resolves formula dependencies.

AUSOUG WA and VIC National Conference 2007