Informative Error Messages

You have probably all experienced situations where you get an error message from Oracle and it turns out to be a total Red Herring. Occasionally though the error messages can be really helpful. I got an email from a co-worker today (thanks Stephan) that read like this:

I was working on an external table today and fat fingered something. This is the error report-

Error report:
SQL Error: ORA-30657: operation not supported on external organized table
30657.0000 – “operation not supported on external organized table”
*Cause: User attempted on operation on an external table which is
not supported.
*Action: Don’t do that!

Gotta love it. They don’t actually tell us what we did wrong, but they do tell us how to resolve the issue!

Apparently some of the Oracle developers have a sense of humor.

By the way, I’m sure you already know this but there is a nifty little Oracle provided tool called oerr that spits out this information:

SYS@SANDBOX1> !oerr ora 30657
30657,0000, "operation not supported on external organized table"
// *Cause:  User attempted on operation on an external table which is
//          not supported.
// *Action: Don't do that!


  1. Noons says:

    There is one particular kind of humor that Oracle developers have been showing since about 9i that I don’t really appreciate.

    The kind that writes this:

    Fri Feb 4 22:41:06 2011
    Cannot re-create tempfile /ublah/oracle/oradata/blah/temp03.dbf, the same name
    file exists
    Fri Feb 4 22:41:06 2011

    in the alert log file and does NOT add an Oracle error code to it of the form

    Basically it means searching for the general string “ORA-” to locate possible problems in this file is out of the question.

    Thanks for nothing, developers: I now have to read the blessed thing every day looking for any of these messages, line by line…

    Yeah, I understand grid/OEM/xml can do all that for me as well as the coffee.
    But exactly and precisely how?

  2. On an only-just-related note – Oracle documentation lacking clarity – I always like this quote from the 11gR1 Globalization Support Guide –

    “I think these secions that describe encoding are out of date. For example, do Java and Microsoft Windows still use UCS-2? Or do they use UTF-16? I think UNIX uses UTF-32. So I am confused about what is the most current information for these sections.”

  3. osborne says:


    Don’t hold back, let us know how you really feel! 😉


    Docs are full of interesting things – like the index entry back in some distant version that had:

    “Dirty Reads – Done Dirt Cheap”


  4. Michael Fontana says:

    Sometimes the simplest ones confound the newbies. After awhile, we learn the eccentric nuances of Oracle error messages, and come to understand what they mean, which is basically: “that won’t work”. Here’s an elegant example:

    SQL> select tablename from dba_tables;
    select tablename from dba_tables
    ERROR at line 1:
    ORA-00904: “TABLENAME”: invalid identifier

    SQL> !oerr ora 904
    00904, 00000, “%s: invalid identifier”
    // *Cause:
    // *Action:

    We all know what I did wrong, but imagine you’re just learning SQL or don’t use it every day. What action do you take?

    I rest my case….

  5. We just ran into another informative error message:

    => oerr ora 2298
    02298, 00000,”cannot validate (%s.%s) – parent keys not found”
    // *Cause: an alter table validating constraint failed because the table has
    // orphaned child records.
    // *Action: Obvious

  6. osborne says:

    Sounds like a teenager trying to borrow the family car while the folks are out of town.

    “parent keys not found”

Leave a Reply to Michael Fontana