does a table containing the the list of exceptions exist-CodePudding

I'm searching for a table containing the names of the exceptions and the description.

I would like for instance use the table like that.

  select name from TABLE description like '%loop%' or description like '%cycle%'

CodePudding user response：

Not how you would like.

Tables exist in the documentation:

But there is not a table within the database.

If you want to look up the ORA errors (rather than the many other types of errors such as LPX, that occurs during XML parsing) then you can use the PL/SQL SQLERRM(error_number) function described in this answer.

Or you can create your own table:

CREATE TABLE errors (
  errno  NUMBER(5,0),
  errmsg VARCHAR2(1000)
);

DECLARE
  v_msg VARCHAR2(1000);
BEGIN
  FOR i IN REVERSE -65535 .. 100 LOOP
    v_msg := SQLERRM(i);
    IF  v_msg NOT IN (
          ' ' || TO_CHAR(-i, 'fm99990') || ': non-ORACLE exception ',
          'ORA-' || TO_CHAR(-i, 'fm00000') || ': Message '|| TO_CHAR(-i, 'fm99990') ||' not found;  product=RDBMS; facility=ORA',
          'ORA-' || TO_CHAR(-i, 'fm00000') || ': '
        )
    THEN
      INSERT INTO errors (errno, errmsg) VALUES (i, v_msg);
    END IF;
  END LOOP;
END;
/

db<>fiddle here

CodePudding user response：

There's no such a table, but you can create one of your own.

SQL> create table oraerr
  2    (sqlcode varchar2(5),
  3     sqlerrm varchar2(500));

Table created.

SQL> declare
  2    l_msg varchar2(500);
  3  begin
  4    for i in 1 .. 99999 loop
  5      l_msg := sqlerrm(-i);
  6      if l_msg not like 'ORA-%Message % not found;  product=RDBMS; facility=ORA' then
  7         insert into oraerr(sqlcode, sqlerrm) values
  8           (lpad(i, 5, '0'), l_msg);
  9      end if;
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

What's the result? (displaying only several rows)

SQL> select * from oraerr
  2  where sqlcode <= '00030'
  3  order by sqlcode;

SQLCO SQLERRM
----- --------------------------------------------------------------------------------
00001 ORA-00001: unique constraint (.) violated
00017 ORA-00017: session requested to set trace event
00018 ORA-00018: maximum number of sessions exceeded
00019 ORA-00019: maximum number of session licenses exceeded
00020 ORA-00020: maximum number of processes () exceeded
00021 ORA-00021: session attached to some other process; cannot switch session
00022 ORA-00022: invalid session ID; access denied
00023 ORA-00023: session references process private memory; cannot detach session
00024 ORA-00024: logins from more than one process not allowed in single-process mode
00025 ORA-00025: failed to allocate
00026 ORA-00026: missing or invalid session ID
00027 ORA-00027: cannot kill current session
00028 ORA-00028: your session has been killed
00029 ORA-00029: session is not a user session
00030 ORA-00030: User session ID does not exist.

15 rows selected.

SQL>

Note that:

there are still many "empty" error codes/messages - remove them, if you want
range between -20000 and -20999 is reserved for user-defined exceptions; I didn't take the effort to skip them
error messages depend on Oracle database versions; a new version might have some codes removed, some other added, so - this is not an universal list of error messages, you should run it again if you upgrade

But, generally speaking, this might be what you wanted.