Query Oracle for ORA-code error details

Question

My Question: How do you query an 11g Oracle database to get a description of an error code?

Background: Someone told me they had seen code once which would query oracle for details on a specific ORA-code error. I've been searching on Google for a little while now and can't seem to find anything like that. Does anyone know if this is possible? Is there a v$view for it or something?

Why: I want to write a procedure which will return the description of an error code I give to it. So when it's written I could call it like this:

select ora_code_desc('ORA-00000')
from dual;

And it would output:

Normal, successful completion.
Cause: An operation has completed normally, having met no exceptions.
Action: No action required.

Or something like that :) Thanks for the help!

Answer 1

It's not accessible from SQL but within PL/SQL, you can use the SQLERRM function.

For example

SQL> ed
Wrote file afiedt.buf

  1  begin
  2    dbms_output.put_line( sqlerrm(0) );
  3    dbms_output.put_line( sqlerrm(-1041) );
  4* end;
SQL> /
ORA-0000: normal, successful completion
ORA-01041: internal error. hostdef extension doesn't exist

PL/SQL procedure successfully completed.

You could, of course, build an ora_code_desc function that took in a string, removed the first three characters, passed the resulting number to SQLERRM, and returned the result

SQL> ed
Wrote file afiedt.buf

  1  create or replace function ora_code_desc( p_code in varchar2 )
  2    return varchar2
  3  is
  4    l_str varchar2(1000);
  5  begin
  6    l_str := sqlerrm( substr(p_code, 4 ) );
  7    return l_str;
  8* end;
SQL> /

Function created.

SQL> select ora_code_desc( 'ORA-00000' ) from dual;

ORA_CODE_DESC('ORA-00000')
--------------------------------------------------------------------------------
ORA-0000: normal, successful completion

Oracle also ships a utility on Unix platforms oerr that provides more detail-- particularly the cause and action you're looking for. If you really want that data too, you could write a Java stored procedure that called out to an operating system shell, executed an oerr command, and returned the result. That would give you more data but would, obviously, be much more complex.

Answer 2

You're looking for the SQLERRM function.

Answer 3

From the command line

oerr ora <error number>

output:

oerr ora 1501
01501, 00000, "CREATE DATABASE failed"
// *Cause:  An error occurred during create database
// *Action: See accompanying errors.

Answer 4

In the interest of completeness, I would like to point out that the Oracle documentation does contain a complete list of all errors, not just the ORA ones. These also contain an explanation and suggested action like the oerr utility.

I know developers prefer writing queries rather than RTFM. But if you feel like being retro you can find it here.