Failing In So Many Ways

Icon

Liang Nuren – Failing In So Many Ways

Database Asserts and Oracle Fun

I should begin by explaining that my particular talents tend to lend towards processing extremely large quantities of data.  In my career I’ve grown data warehouses from infantile to quite large – and utilized everything from Postgres to NoSQL and distributed processing frameworks like Hadoop. The bulk of my work experience is with Perl and Python – and I now (intentionally) work at a Java/Oracle/Hadoop shop.  I really felt the need to branch out and understand what it is that Oracle brings to the table that Postgres doesn’t.

The code base at work is a bit old and sometimes interesting.  Most of it’s in PL/SQL, Java, and the ever-present XML.  Despite our best efforts to exterminate PL/SQL, it continues to propagate on occasion – and testing it with the normal Hibernate methods gets tedious really fast.  In an effort to improve the ease of writing tests around this PL/SQL code I have been writing some specialized direct SQL asserts.

Part of being a convenient and easy to use assert is being able to take a variety of types – from whatever Hibernate object to a Date or even a string representation.  While I was working on making sure that ordering was preserved, I ran across this particular behavior in Oracle:

SQL> create table foo (some_date_column date);

Table created.

SQL> insert into foo (some_date_column) values (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(some_date_column, ‘YYYY-MM-DD HH24:MI:SS’) as some_date_column from foo order by to_char(some_date_column, ‘YYYY-MM-DD HH24:MI:SS’);
select to_char(some_date_column, ‘YYYY-MM-DD HH24:MI:SS’) as some_date_column from foo order by to_char(some_date_column, ‘YYYY-MM-DD HH24:MI:SS’)
*
ERROR at line 1:
ORA-01722: invalid number

Exciting eh?  Some experimentation with the query revealed that the problem has something to do with aliasing the column as the original column name and then ordering by the formatted column.  Removing the order by or changing the table alias resulted in the query working as expected (though with the change in behavior).

Either way, it wasn’t very useful for my hopefully generic SQL assert and I felt compelled to find a work around.  Fortunately, I was able to come up with one without burning too much time.  Simply wrap the select columns  in a sub select and pull the order by into the outer select – as an added bonus you only have to do the formatting once this way.

It looks like this:

SQL> select some_date_column from ( select to_char(some_date_column, ‘YYYY-MM-DD HH24:MI:SS’) as some_date_column from foo ) x order by some_date_column;

SOME_DATE_COLUMN
——————-
2011-10-28 19:03:02

Fun times with Oracle, eh?

Advertisements

Filed under: Databases, Software Development, , , ,

One Response

  1. Charles says:

    wow dood that is SO UNINTERESTING

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: