12 Replies Latest reply on Mar 7, 2006 3:08 PM by 492151

    Internal Table with Oracle's Error Codes


      Is there any internal table where is stored every oracle error code with it's Cause, Action and all the other similar stuff?

        • 1. Re: Internal Table with Oracle's Error Codes

          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.


          • 2. Re: Internal Table with Oracle's Error Codes
            kuljeet singh -

            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
            • 4. Re: Internal Table with Oracle's Error Codes
              Hi Jim,
              that was really wonderful one. Though I knew the SQLERRM function, i used it only in WHEN OTHERS THEN.

              • 5. Re: Internal Table with Oracle's Error Codes
                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.
                • 6. Re: Internal Table with Oracle's Error Codes
                  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
                  • 7. Re: Internal Table with Oracle's Error Codes

                    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?
                    • 8. Re: Internal Table with Oracle's Error Codes
                      Jens Petersen
                      You could put a link in your web application to refer to the documentation
                      • 9. Re: Internal Table with Oracle's Error Codes
                        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
                        • 10. Re: Internal Table with Oracle's Error Codes
                          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) ;
                          dbms_output.put_line(v_msg) ;
                          end ora_error ;
                          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 --
                          • 11. Re: Internal Table with Oracle's Error Codes
                            For a table of all "ORA-" error codes and messages, you could do something like:
                            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);
                              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;
                            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
                            • 12. Re: Internal Table with Oracle's Error Codes
                              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