Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

How to get Oracle Error Message from Database or OS

How to get Oracle Error Message from Database or OS

If you are on UNIX platform then from unix machine using unix command you can easily get error description, cause and action easily. You don’t need to go to internet and then search error about it immediately. Suppose you got the error ORA-04043 and now you want to know the cause of the error and action of the error. From Unix machine (Linux, Solaris etc) you can easily get it by using oerr command. In order to use it For example, if you want to get cause and action of ORA-7300, then “ora” is the facility and “7300” is the error. So you should type “oerr ora 7300“.

If you get LCD-111, type “oerr lcd 111“, and so on.

Below is an example.

SQL> desc t;
ERROR:
ORA-04043: object t does not exist


SQL> !oerr ora 04043

04043, 00000, “object %s does not exist”
// *Cause: An object name was specified that was not recognized by the system.
// There are several possible causes:
// – An invalid name for a table, view, sequence, procedure, function,
// package, or package body was entered. Since the system could not
// recognize the invalid name, it responded with the message that the
// named object does not exist.
// – An attempt was made to rename an index or a cluster, or some
// other object that cannot be renamed.
// *Action: Check the spelling of the named object and rerun the code. (Valid
// names of tables, views, functions, etc. can be listed by querying
// the data dictionary.)

Similarly you can check other error message too.

Like,
$ oerr ora 600
00600, 00000, “internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]”
// *Cause: This is the generic internal error number for Oracle program
// exceptions. This indicates that a process has encountered an
// exceptional condition.
// *Action: Report as a bug – the first argument is the internal error number

However if you are not on UNIX platform you still can get your desired error message description.
In that case you have to use the function SQLERRM.
The error number passed to SQLERRM should be negative. Passing a zero to SQLERRM always returns the ORA-0000: normal, successful completion message. Passing a positive number to SQLERRM always returns the User-Defined Exception message unless you pass +100, in which case SQLERRM returns the ORA-01403: no data found message.

An Example:
——————

SET SERVEROUT ON
prompt Please enter error numbers as negatives. E.g. -1
prompt
exec dbms_output.put_line(‘==> ‘||sqlerrm( &errno ) );
/

SQL> Enter value for errno: -7445
==> ORA-07445: exception encountered: core dump [] [] [] [] [] []

January 4, 2008 - Posted by | oracle

1 Comment »

  1. Thanks a bundle! for revising me

    Comment by texas | May 15, 2011 | Reply


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: