Oracle is stupid

I’ve just spent two days debugging and fixing AR-JDBC issues with Oracle. And let me tell you, those days haven’t been fun. I am really unfond of Oracle at the moment. You probably saw my last post. Now, let me add a new point of insanity to the proceedings…

Say that you define a table like this:

create table companies (
firm_id integer default null references companies

Now, if you try to get the default value of the column firm_id in some manner (for example, you could use JDBC, DatabaseMetaData.getColumns, and getString(13) on the result). You could also use the OCI8 C interface. You would get the same result. Any guesses? What is the default value of the column firm_id? Some might say that it should be the String “null”. Nope. It’s the String “null “. Notice the space.

Now, if you instead defined your table like this:

create table companies (
firm_id integer default null

In this, what is the default value of the column firm_id? it’s “null”. Without a space. Yes, it varies. Yes, it actually varies based on the formatting of the SQL used to create the table. You could potentially use the Whitespace language to embed arbitrary programs in the null default value… Because if there is two blanks between the null word, and the next token, then that’s what you will get from the default value. Notice that we used two totally different interfaces to get this information so it’s obviously something that is saved in the database engine. Wow.

Is this insane? Is it crazy? Am I thoroughly disgusted by now?

Yes. And also, AR-JDBC finally runs all ActiveRecord tests with Oracle.

8 Comments, Comment or Ping

  1. Tim O'Brien

    This is criminal! Do you mean to tell me that people pay an arm and a leg for this software?

    September 26th, 2007

  2. Tony

    Ack! That reminds me of a (thankfully) long-ago and far away time when I was stuck programming with the Windows API in C.

    As a newbie, I wasted a lot of time before figuring out that the reason a while(blah){…} code segment was producing weird results was that some Windows functions prototyped as returning BOOL can actually return 0, 1, or -1. Nice design, guys!

    September 26th, 2007

  3. MDWeezer

    What’s Ola Bini’s database of choice?

    I’ve been using JRuby at work lately and the database I’m tied to is of course Oracle. Luckily Rails has nifty features to let me utilize legacy database tables and ActiveRecord-JDBC worked well for me right off the bat (I’m not doing anything ground breaking). But it looks like your book will end up on my desk soon…

    September 26th, 2007

  4. Wilson

    I like Oracle a lot, but the JDBC driver is just criminally bad. The C API is nice, though. =(

    September 26th, 2007

  5. Dr.Alces

    Welcome to the wonderful world of oracle. Afraid oracle alway have there own way of doing things. For >1Tb databases and using the database as application server with pl/sql its great. For implement JDBC driver like other vender they not so hot.

    The JDBC driver metadata calls dont work well. Most likely will have to do same as native ActiveRecord drive and go direct for column information with a oracle specifc meta data select (via ALL_TABLES,ALL_TAB_COLUMNS, ALL_CONSTRIANTS etc.) eg.

    select column_name as name, data_type as sql_type, data_default, nullable,
    decode(data_type, ‘NUMBER’, data_precision,
    ‘FLOAT’, data_precision,
    ‘VARCHAR2’, data_length,
    null) as limit,
    decode(data_type, ‘NUMBER’, data_scale, null) as scale
    from all_tab_columns
    where owner = ‘#{owner}’
    and table_name = ‘#{table_name}’
    order by column_id

    The Next with Default is can be a sql function call like sys_guid() so may need to filter out these.

    After that most likily will find the 4kb blob problems on insert and then fact oracle number() is fixed 22 place decimal and not expected float (BINARY_DOUBLE in oracle speak).

    September 26th, 2007

  6. quark

    Ever since I had to work with Oracle in a publishing environment at a former job, I’ve hated it. I think it’s utter piece of crap. Why anyone in their right mind would pay anything for it is beyond me. The issues you’ve discovered here are just scratching the enamel of the surface of an ice berg the size of Antares.

    Anyway; good progress, great job!

    September 27th, 2007

  7. Dennis Byrne

    September 29th, 2007

  8. Tonetheman

    you do realize that you are really complaining about the getString… why are you not trying to get the int value out of it? i would think that the string value is probably driver dependent. and based on your description it is. oh well just a thought.

    September 30th, 2007

Reply to “Oracle is stupid”