Internal Table with Oracle's Error Codes

Hi,

There is a document from oracle that contains this. The name of hte pDF file document is

Oracle 9i Database Error Messages,
Release 2 (9.2)
Part No. A96525-01

You can download this from OTN or if you have the documentation CD then also you will see this in there. Note hte Part No. of the document to find it.

Thanks,
Regards

Abhivyakti

Hi,

In linux,solaris except window you can use oerr utility.
it gives u error description ,cause and action to resolve the error.

Like

OraBase1>>oerr ora 12560
12560, 00000, "TNS:protocol adapter error"
// *Cause: A generic protocol adapter error occurred.
// *Action: Check addresses used for proper protocol specification. Before
// reporting this error, look at the error stack and check for lower level
// transport errors.For further details, turn on tracing and reexecute the
// operation. Turn off tracing when the operation is complete.

Thanks
Kuljeet Pal Singh

Also, see: Re: What Tables or Views for ORA- errors?

Hi Jim,
that was really wonderful one. Though I knew the SQLERRM function, i used it only in WHEN OTHERS THEN.

thanks,
Abhivyakti

That's why I want to use a real table.

Well you could use the trick Jim showed you to populate an actual table.

Cheers, APC

You could put a link in your web application to refer to the documentation
http://www.oracle.com/pls/db92/db92.error_search?remark=homepage&prefill=ORA-

Is there any Oracle function that retrieves the Cause a Action texts for a given error?

In my experience the Oracle explanations are often opaque, sometimes misleading. You would proabbly be better writing your own text for your site's specifc problems.

Cheers, APC

Look at this for a model

set echo off
rem
rem Pl/sql script to create procedure to report Oracle error messages
rem in sqlplus
rem
rem 1996 07 11 m d powell New script.
rem 1998 06 23 m d powell Modify to be in-stream procedure from stored
rem
set feedback off
set serveroutput on
set verify off
accept err_code prompt "Enter Oracle Error Number, i.e, ORA-00100 = 100 ==> "
declare
procedure ora_error (
v_err_no in number
)
is
v_rc number ;
v_msg varchar2(100) ;
--
begin
if v_err_no > 0 then
v_rc := v_err_no * -1 ;
v_rc := v_err_no * -1 ;
else
v_rc := v_err_no ;
end if ;
v_msg := sqlerrm(v_rc) ;
dbms_output.put_line(v_msg) ;
end ora_error ;
--
begin
ora_error(&err_code);
end;
/
set feedback on
undefine err_code

The message is avaiable via the SQLERRM function. Oracle does not provide the information in native SQL.

HTH -- Mark D Powell --

For a table of all "ORA-" error codes and messages, you could do something like:

<BR>

create type t_oracle_error is object (

error_num varchar2(9),

error_desc varchar2(1024)

);



create type t_oracle_error_tab is table of t_oracle_error;



create or replace function OracleErrors return t_oracle_error_tab pipelined is

  l_error t_oracle_error := t_oracle_error(null,null);

begin

  for errNo in reverse -32799..0 loop

    l_error.error_num := errNo;

    l_error.error_desc := sqlerrm(errNo);

    if l_error.error_desc not like '%Message % not found;%' and l_error.error_desc != 'ORA'||to_char(errNo,'FM09999')||': ' then

      pipe row(l_error);

    end if;

  end loop;

  return;

end OracleErrors;



select * from table(oracleerrors);



SQL> select * from table(oracleerrors);



ERROR_NUM ERROR_DESC

--------- --------------------------------------------------------------------------------

0         ORA-0000: normal, successful completion

-1        ORA-00001: unique constraint (.) violated

-17       ORA-00017: session requested to set trace event

-18       ORA-00018: maximum number of sessions exceeded

-19       ORA-00019: maximum number of session licenses exceeded

-20       ORA-00020: maximum number of processes () exceeded

-21       ORA-00021: session attached to some other process; cannot switch session

-22       ORA-00022: invalid session ID; access denied

-23       ORA-00023: session references process private memory; cannot detach session

-24       ORA-00024: logins from more than one process not allowed in single-process mode

-25       ORA-00025: failed to allocate

-26       ORA-00026: missing or invalid session ID

-27       ORA-00027: cannot kill current session

-28       ORA-00028: your session has been killed

-29       ORA-00029: session is not a user session

-30       ORA-00030: User session ID does not exist.

-31       ORA-00031: session marked for kill

-32       ORA-00032: invalid session migration password

-33       ORA-00033: current session has empty migration password

-34       ORA-00034: cannot  in current PL/SQL session

-35       ORA-00035: LICENSE_MAX_USERS cannot be less than current number of users

-36       ORA-00036: maximum number of recursive SQL levels () exceeded

-37       ORA-00037: cannot switch to a session belonging to a different server group

-38       ORA-00038: Cannot create session: server group belongs to another user

-50       ORA-00050: operating system error occurred while obtaining an enqueue

-51       ORA-00051: timeout occurred while waiting for a resource

-52       ORA-00052: maximum number of enqueue resources () exceeded

-53       ORA-00053: maximum number of enqueues exceeded

-54       ORA-00054: resource busy and acquire with NOWAIT specified

-55       ORA-00055: maximum number of DML locks exceeded

-56       ORA-00056: DDL lock on object '.' is already held in an incompatible mode

-57       ORA-00057: maximum number of temporary table locks exceeded

-58       ORA-00058: DB_BLOCK_SIZE must be  to mount this database (not )

-59       ORA-00059: maximum number of DB_FILES exceeded

-60       ORA-00060: deadlock detected while waiting for resource

-61       ORA-00061: another instance has a different DML_LOCKS setting

-62       ORA-00062: DML full-table lock cannot be acquired; DML_LOCKS is 0

-63       ORA-00063: maximum number of LOG_FILES exceeded

-64       ORA-00064: object is too large to allocate on this O/S (,)

-65       ORA-00065: initialization of FIXED_DATE failed

-66       ORA-00066: LOG_FILES is  but needs to be  to be compatible

-67       ORA-00067: invalid value  for parameter ; must be at least

-68       ORA-00068: invalid value  for parameter , must be between  and

-69       ORA-00069: cannot acquire lock -- table locks disabled for

-70       ORA-00070: command  is not valid

-71       ORA-00071: process number must be between 1 and

-72       ORA-00072: process "" is not active

-73       ORA-00073: command  takes between  and  argument(s)

-74       ORA-00074: no process has been specified

-75       ORA-00075: process "" not found in this instance

-76       ORA-00076: dump  not found

-77       ORA-00077: dump  is not valid

-78       ORA-00078: cannot dump variables by name

-79       ORA-00079: variable  not found

-80       ORA-00080: invalid global area specified by level

-81       ORA-00081: address range [, ) is not readable

-82       ORA-00082: memory size of  is not in valid set of [1], [2], [4]

-83       ORA-00083: warning: possibly corrupt SGA mapped

-84       ORA-00084: global area must be PGA, SGA, or UGA

-85       ORA-00085: current call does not exist

-86       ORA-00086: user call does not exist

-87       ORA-00087: command cannot be executed on remote instance

-88       ORA-00088: command cannot be executed by shared server

-89       ORA-00089: invalid instance number in ORADEBUG command

-90       ORA-00090: failed to allocate memory for cluster database ORADEBUG command

-91       ORA-00091: LARGE_POOL_SIZE must be at least

-92       ORA-00092: LARGE_POOL_SIZE must be greater than LARGE_POOL_MIN_ALLOC

-93       ORA-00093:  must be between  and

-94       ORA-00094:  requires an integer value

-96       ORA-00096: invalid value  for parameter , must be from among

-97       ORA-00097: use of Oracle SQL feature not in SQL92  Level

-99       ORA-00099: timed out while waiting for resource, potential PDML deadlock

-100      ORA-00100: no data found

-101      ORA-00101: invalid specification for system parameter DISPATCHERS

-102      ORA-00102: network protocol  cannot be used by dispatchers

-103      ORA-00103: invalid network protocol; reserved for use by dispatchers

-104      ORA-00104: deadlock detected; all public servers blocked waiting for resources

-105      ORA-00105: dispatching mechanism not configured for network protocol

-106      ORA-00106: cannot startup/shutdown database when connected to a dispatcher

-107      ORA-00107: failed to connect to ORACLE listener process

-108      ORA-00108: failed to set up dispatcher to accept connection asynchronously

-111      ORA-00111: not all servers started because number of servers is limited to

-112      ORA-00112: only created up to  (maximum specified) dispatchers

-113      ORA-00113: protocol name  is too long

-114      ORA-00114: missing value for system parameter SERVICE_NAMES

-115      ORA-00115: connection refused; dispatcher connection table is full

-116      ORA-00116: SERVICE_NAMES name is too long

-117      ORA-00117: value out of range for system parameter SERVICE_NAMES

-118      ORA-00118: value out of range for system parameter DISPATCHERS

-119      ORA-00119: invalid specification for system parameter

-120      ORA-00120: dispatching mechanism not enabled or installed

-121      ORA-00121: SHARED_SERVERS specified without DISPATCHERS

-122      ORA-00122: cannot initialize network configuration

-123      ORA-00123: idle public server terminating

-124      ORA-00124: DISPATCHERS specified without MAX_SHARED_SERVERS

-125      ORA-00125: connection refused; invalid presentation

-126      ORA-00126: connection refused; invalid duplicity

-127      ORA-00127: dispatcher  does not exist

-128      ORA-00128: this command requires a dispatcher name

...