Feb 032014
  raise my_database_is_on_fire;
  when others then
    dbms_output.put('Just ignore it...');
    dbms_output.put('...it'll go out soon!');

This is a re-hash of my very first post. I wouldn’t normally repeat myself, but I’ve just been horribly burned by some code with “when others then null” blocks. This suppressed tablespace full errors, leading to data corruption and hasty data fixes.
If you must have “when others” exception blocks, please ensure you re-raise the error so it can be spotted and dealt with!

Sep 232013
select an_easier_way, 
from   data_stuck_in_utc_time
where  case 
  when utc_datetime between 
    --clocks forward and back
        trunc(daylight_savings_datetime, 'y'),
        2)) - 7, 'sunday') and
        trunc(daylight_savings_datetime, 'y'),
        9)) - 7, 'sunday') 
    utc_datetime+1/24 -- an hour
  end = daylight_savings_datetime;

There is
  extract(timezone_hour from 
A classic convoluted build your own solution to a problem Oracle's 
already solved for you; figuring out the daylight savings offset 
for a particular date. This is the calculation for the UK (daylight 
savings starts the last Sunday in March and ends the last Sunday in 
October), but it varies in some countries. Must easier to have a 
timestamp with time zone and just extract the hour offset from it!

If you’d like a complete script showing this, keep reading.
Continue reading »

Aug 122013
select unnecesary_conversions,
from   your_queries
where  sysdate = to_date(sysdate);

no rows selected

As Tom Kyte often says, implicit conversions = evil. In this case putting a to_date 
around a date first converts it to a string, then converts it back to a date. If your 
session NLS settings don't include the full time, these will be lost in the conversion, 
effectively stripping these out of the query.

For the full script, keep reading.
Continue reading »

Aug 092013
delete databases
where  not exists (
  select a_recovery_strategy
  from   backups
  where  your_job_depends = on_it_working
  or     you_may_find_the_job = not_existing

Of course, it's a better strategy to put backups and a recovery plan in place than going 
around deleting databases without these. If you're not going to do this you'll save some
pain in the long term just getting rid of them now though!
Jul 222013
create package compile as

  subtype type is varchar2(10);
  constant constant type := 'constant';

  function function return type;
  procedure function;

end compile;

create package body compile as

  function function return type as
    return constant;

  procedure function as
end compile;

select compile.function from dual;


exec compile.function;


You will get a bunch of compiler warnings about this, but 
Oracle will happily let you create and use this package. 
Please don't do this for real though (unless you want everyone 
who maintains this code in the future to curse your name :)

Inspired by this stack overflow question.

Jul 082013
select case 
         when to_char(the_datetime_is, 'mi:ss')
                = '00:00' --midnight
       end unnecessarily
from   your_etl_process
where  the_datetime_is between trunc(:this_date)
                       and trunc(:this_date)+1
and    you_run_this_for = 'consecutive dates';

Remember, between is inclusive so it returns rows equal to the lower and upper bounds (in this 
case dates with times at midnight). Repeated executions of the same query moving the boundaries
will end up processing items multiple times. The correct way to do this is use greater than or
equal to your lower bound and strictly less than the upper bound (not less than or equal to the 
lower bound plus something slightly less than the upper bound!).

For a script showing this principle, click here.

Jul 012013
alter session set nls_date_format = 
  'dd-mon-yyyy hh:mi am';

select sysdate "Clearly 9am on 1 July 2013" 
from   dual;

Clearly 9am on 1 July 2013
01-jul-2013 09:00 AM 

alter session set nls_date_format = 
  'dd-mm-yy hh:mi';

select sysdate "9 am or pm? 1 July or 7 Jan?" 
from   dual;

9 am or pm? 1 July or 7 Jan?
01-07-13 09:00           

alter session set nls_date_format = 
  '"''union select * from sql_injection"';

select sysdate "Make sure you're using binds!" 
from   dual;

Make sure you're using binds!
'union select * from sql_injection
You can put pretty much anything between the quotes in the last example, potentially 
leading to all sorts of weird and wonderful behaviour for people reading the data and 
relying on implicit conversions!

For an explanation of the SQL injection risk, read Tom Kyte’s write up.

Jun 242013
create table roll_your_own_time_types (
  where_everything_is_a varchar2(5) 

alter table roll_your_own_time_types 
add constraint so_we_are_sure_it_is_a_time 
check (regexp_like(

alter table roll_your_own_time_types add (
    interval day(0) to second

In addition to saving you from having to write validaiton code, using an interval implements
all the arthimetic operations for you as well, so you can easily find the difference between
two times. This kind of datatype crime is more common with dates, but I suspect that's
because it's rare times are stored with no date component.

As usual, there’s an oracle-base article detailing how to use interval datatypes if you want a more thorough explanation.

May 202013
select dm.name
from   data_models dm
join   data_model_values qc
on     dm.id = qc.model_id
join   data_model_values de
on     dm.id = de.model_id
join   data_model_values coa
on     dm.id = coa.model_id
where  qc.attr = 'Query complexity'
and    qc.val = 'Hideously mind-boggling'
and    de.attr = 'Datatype enforcement'
and    de.val = 'Non-existent'
and    coa.attr = 'Calculation of aggregates'
and    coa.val = 'Almost impossible';


select name
from   data_models
where  query_complexity = 
         'Simple and straightforward'
and    datatype_enforcement = 
         'Strong and complete'
and    calculation_of_aggregates = 
         'As easy as group by';


While it's possible to end up with some dodgy queries and datatypes in a "normal" relational model, 
it's much easier to fix the problems when compared to EAV. If you've got any other comparisons 
between EAV and normal relational models, please add them in the comments!

To be fair, there are (very) few cases where EAV is appropriate. This provides a nice discussion of when you should/shouldn’t use this.

Apr 292013
create sequence ora 
start  with -1002 minvalue -1002;

  for commits in (
    select loops 
    from   queries
    where  we = 'specify'
    for    update
  ) loop
      'Causes an ' || sqlerrm(ora.nextval));
  end loop;

Error report:
ORA-01002: fetch out of sequence
Issuing a commit after opening a cursor with the "for update" releases the row locks
and invalidates the cursor, meaning you can no longer fetch data out of it!

Click here for a complete script to see this in action.