SQLfail

How do you move a table made of cheese?

Chris Saxon — Fri, 24 May 2013 08:00:18 +0000

alter table crumbly_cheese 
move tablespace caerphilly;

You can't beat a classic cheese pun in SQL form ;)

A Tale of Two Models

Chris Saxon — Mon, 20 May 2013 08:00:00 +0000

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';

NAME
----------------------
ENTITY ATTRIBUTE VALUE

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';

NAME
----------
RELATIONAL

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.

The things that make life worth living

Chris Saxon — Fri, 17 May 2013 08:00:10 +0000

insert into joyous_memories
  select making_vast_improvements
  from   that_horrible_query
  union  all
  select designing_data_models
  from   scalable_applications
  union  all
  select heart_warming_smile
  from   your_newborn_child;

While there can be a lot of effort put in before getting these outcomes, the reward is worth it! :)

Where did all the decimals go?

Chris Saxon — Mon, 13 May 2013 08:00:00 +0000

create table fractional_digits_disappear (
  without_warning_when_put_in_an integer
);

insert into fractional_digits_disappear (
  without_warning_when_put_in_an
) values (
  3.141592
);

select *
from    fractional_digits_disappear;

WITHOUT_WARNING_WHEN_PUT_IN_AN
------------------------------
                             3

The fractional seconds of timestamps are also silently lost when these are inserted into dates.
Not raising an error for this could be confusing, and potentially an "inexplicable" bug waiting to happen.

The Crying Game

Chris Saxon — Fri, 10 May 2013 08:00:06 +0000

select count(*)
from   babies
where  crying = any (
  select understanding
  from   parents
);

COUNT(*)
--------
       0

If anyone ever manages to create a baby-to-parent translation device, 
I'm sure they'll become the richest person in the world!

The NON-EXISTENT Edition

Chris Saxon — Mon, 06 May 2013 08:00:20 +0000

drop procedure is_not_really_gone_but;

select object_name || ' ' || object_type 
                 as "Continues on"
from   user_objects_ae
where  object_name = 
           'IS_NOT_REALLY_GONE_BUT';

Continues on
-----------------------------------
IS_NOT_REALLY_GONE_BUT NON-EXISTENT

create table is_not_really_gone_but ( 
  until_replaced            number,
  by_a_non_editioned_object varchar2(1) 
                            default 'Y',
  which_we_then_drop        varchar2(1)
);

drop table is_not_really_gone_but;

select object_name || ' ' || object_type 
from   user_objects_ae
where  object_name = 
           'IS_NOT_REALLY_GONE_BUT';

no rows selected
If you often create test procedures/fucntions (like me), you can find the _ae dictionary views 
quickly fill with these "NON-EXISTENT" objects. This can be confusing once you start working with 
editions. Hoepfully Oracle will introduce some form of purge option when dropping PL/SQL to provide 
a nicer way to get rid these

For a full script, use this. (NB – requires 11gR2 to see the effect)

Traffic Management

Chris Saxon — Fri, 03 May 2013 08:00:00 +0000

delete
from   roads
where  cars in ('my', 'way')
and    desire_to_get_home_quickly = 'huge';

1,492 rows deleted
Now I just need to find a way to execute this before leaving work to ensure an smooth flowing journey home!

Sequence out of fetch? More problems with committing in loops

Chris Saxon — Mon, 29 Apr 2013 08:00:36 +0000

create sequence ora 
start  with -1002 minvalue -1002;

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

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.

You don’t need to wait to be given this permission

Chris Saxon — Fri, 26 Apr 2013 08:00:00 +0000

grant read on 
directory concepts_guide 
to everyone;
It's always slightly disappointing seeing how many forum questions could be answered by a quick and easy look at the relevant section of the concepts guide and/or general manuals

But now you’ve got it, head here to read it!

A Private Place to Manage Working Data

Chris Saxon — Mon, 22 Apr 2013 08:00:07 +0000

create global temporary table  
      contains_data_that_is (
  only_visible_to_one_session 
    varchar2(1)  default 'Y',
  and_by 
    varchar2(50) default 'removed on commit',
  unless_you_state 
    integer
) on commit preserve rows;

I've always found the "global" naming of temporary tables slightly confusing, 
as this implies the data is visible across all sessions. 
I guess it could be to distinguish these from SQL server temporary tables, 
which can be created on the fly in a single session.
If you know better, please enlighten me in the comments!

For a demo of temporary tables in action, use this.

SQLfail

How do you move a table made of cheese?

select posts from sqlfail where content like :this_post;

A Tale of Two Models

select posts from sqlfail where content like :this_post;

The things that make life worth living

select posts from sqlfail where content like :this_post;

Where did all the decimals go?

select posts from sqlfail where content like :this_post;

The Crying Game

select posts from sqlfail where content like :this_post;

The NON-EXISTENT Edition

select posts from sqlfail where content like :this_post;

Traffic Management

select posts from sqlfail where content like :this_post;

Sequence out of fetch? More problems with committing in loops

select posts from sqlfail where content like :this_post;

You don’t need to wait to be given this permission

select posts from sqlfail where content like :this_post;

A Private Place to Manage Working Data

select posts from sqlfail where content like :this_post;

select posts
from sqlfail
where content like :this_post;

select posts
from sqlfail
where content like :this_post;

select posts
from sqlfail
where content like :this_post;

select posts
from sqlfail
where content like :this_post;

select posts
from sqlfail
where content like :this_post;

select posts
from sqlfail
where content like :this_post;

select posts
from sqlfail
where content like :this_post;

select posts
from sqlfail
where content like :this_post;

select posts
from sqlfail
where content like :this_post;

select posts
from sqlfail
where content like :this_post;