This content has been marked as final. Show 12 replies
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.
In linux,solaris except window you can use oerr utility.
it gives u error description ,cause and action to resolve the error.
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.
Kuljeet Pal Singh
that was really wonderful one. Though I knew the SQLERRM function, i used it only in WHEN OTHERS THEN.
Thanks everyone who replied this topic, but I was really looking for a table/view inside Oracle.
I'm developing a Web Application for use in the intranet of the company that I work to catalogate some problems we have and the specific solution used to solve the problem.
That's why I want to use a real table.
Well you could use the trick Jim showed you to populate an actual table.
That's why I want to use a real table.
Jim J. said, in Re: What Tables or Views for ORA- errors? , that is possible to retrieve the error message and error code using the SQLERRM and SQLCODE functions.
This solves my problem parcially. Is there any Oracle function that retrieves the Cause a Action texts for a given error?
You could put a link in your web application to refer to the documentation
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.
Is there any Oracle function that retrieves the Cause a Action texts for a given error?
Look at this for a model
set echo off
rem Pl/sql script to create procedure to report Oracle error messages
rem in sqlplus
rem 1996 07 11 m d powell New script.
rem 1998 06 23 m d powell Modify to be in-stream procedure from stored
set feedback off
set serveroutput on
set verify off
accept err_code prompt "Enter Oracle Error Number, i.e, ORA-00100 = 100 ==> "
procedure ora_error (
v_err_no in number
v_rc number ;
v_msg varchar2(100) ;
if v_err_no > 0 then
v_rc := v_err_no * -1 ;
v_rc := v_err_no * -1 ;
v_rc := v_err_no ;
end if ;
v_msg := sqlerrm(v_rc) ;
end ora_error ;
set feedback on
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:
create type t_oracle_error is object (
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);
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
select * from table(oracleerrors);
SQL> select * from table(oracleerrors);
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 , , 
-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
Jim J. this is the solution that I thought, but instead of doing a PL/SQL block I will use an Shell Script to user the oerr function.
Thanks everyone! =D