Greatest, Least and NULLs

Uncategorized
| Jan 10, 2013
The functions greatest and least are amongst the special ones which can take lots of arguments. They are a bit similar to min and max for columns rather than rows. But they have one difference to the aggregate functions that meant I avoided them for a long time.

Unlike aggregate functions, which ignore null values,  greatest and least will return a null if any of the supplied columns (or expressions) are null. If that isn't what you want (and it often isn't) it means putting NVLs around everything and with substitution values that don't break the logic.

For example, 


create table (more...)

Inskipp the uncatchable exception

Uncategorized
| Jan 4, 2013
The world is full of exceptions. And exception handlers.

And sometimes, just sometimes, it actually makes sense to have a WHEN OTHERS. For example, if a program fails, under any circumstances, maybe you want it to log the exception or send an email to a support account.

But then, just sometimes, you want a program to FAIL, and to fail in such a way that even the most resilient of exception handlers won't catch it. Maybe you're working on a function that gets called way down the bottom of a deep call stack, and right there at the (more...)

COLLECT and DISTINCT

Uncategorized
| Dec 10, 2012


In regular SQL, you can use the DISTINCT qualifier with the COLLECT function.
Here it removes the duplicates for PETER and DAVID.

SQL> select cast(collect(first_name) as  sys.dbms_debug_vc2coll)
  2  from HR.employees
  3  where job_id = 'SA_REP'
  4  and manager_id in (147,145);

CAST(COLLECT(FIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Peter', 'David', 'Peter', 'Christopher', 'Nanette', 'Oliver'
, 'Clara', 'Danielle', 'Mattea', 'David', 'Sundar', 'Amit')


SQL>
SQL> select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
  2  from HR.employees
  3  where job_id = 'SA_REP'
  4  and manager_id in (147,145);

CAST(COLLECT(DISTINCTFIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Amit', 'Christopher', 'Clara', 'Danielle', 'David', 'Mattea'
(more...)

SQL Developer, COLLECT and CAST

Uncategorized
| Dec 9, 2012
This one is a quick hint for SQL Developer and the COLLECT function.

In case you're not familiar with it, COLLECT is an aggregate function that was introduced in 10gR2 and simply gathers all the items up into a VARRAY/TABLE style collection type. If you use it in PL/SQL programs, you can handle the collection programmatically.

But sometimes it can be handy to see the elements in a quick ad-hoc query.

In SQL*Plus, you'll get an output that is ugly, but usable:


SQL> l
  1  select country_id, collect(city)
  2  from hr.locations
  3* group by country_id
SQL> (more...)

Toastmasters – Testing public speaking away from ‘production’

Uncategorized
| Nov 30, 2012
Every Wednesday lunch (pretty much) there's a Toastmasters meeting at the company where I'm working. After having been there a year, I finally got around to turning up a couple of weeks ago. Hey, they also have live music on Wednesday lunch. It's a busy place.

Yury, from Pythian and the Sydney Oracle Meetup, has mentioned Toastmasters. Given my audience is mostly developers, let me sum it up my way: Toastmasters is a test environment for public speaking.

As with a test environment, you're expected to have a few "does not meet requirements" incidents. That's why it is (more...)

Windows 8 running Oracle 11g XE

Uncategorized
| Nov 10, 2012
It's been several months since my last blog post, mostly because free time is a scare resource.

But after a few false starts, I've removed the last vestige of "Windows XP" from my home, and upgraded my netbook to Windows 8. Windows 7 was never an option for this machine and Windows 8 has an afforable XP upgrade option and a simpler set of variants. The "Pro" version which has replaced my "XP Home" adds such goodies as acting as a Remote Desktop server, which was always skipped for 'home' users. I feel all grown up :)

The Jumps

Being just a little bit unique

Uncategorized
| Jul 17, 2012
A few weeks back, I saw a mention of an old Tanel Poder post about the uniqueness of ROWIDs.

This is an interesting subject as a lot of people expect ROWIDs to be unique, but don't think about how unique that is. While you can't be a little bit pregnant, you can be a little bit unique. Or at least unique only within a specific subgroup.

The only complaint I have about the piece is that it is very DBAish. Lots of moving files around the OS and all those other things that DBAs like to do.

I'm a developer, (more...)