Ora 20999 описание ошибки

It would appear that the glorious error ORA-20999 appears quite frequently in application code, as evidenced by the history of searches on the Internet for this error number. Unfortunately for the person searching endlessly for this error it’s not a standard Oracle offering. Instead, it’s a user-defined exception/error number to catch and report any of a plethora of unnamed Oracle exceptions, which makes finding a definitive answer for what this error represents practically impossible, as it means what the application programmer intended, which can, and does, vary between application programmers and applications. Let’s look at the valid range of user-definable error numbers/exceptions and try to clear the clouded air a bit.

Oracle offers a range of error numbers which are not assigned any standard Oracle error text and are not associated with any fixed Oracle exceptions; this range starts at 20000 and ends at 20999. Looking at a basic PL/SQL block to define and use some of these available error numbers it can be seen that these can either be quite useful or quite frustrating:

SQL> --
SQL> -- User defined errors are numbered
SQL> -- from 20000 to 20999 inclusive
SQL> --
SQL> --
SQL> -- Any time you see an error number
SQL> -- in that range it's an exception/error
SQL> -- defined by the user
SQL> --
SQL>
SQL> declare
  2        ex20000 exception;
  3        ex20459 exception;
  4        ex20773 exception;
  5        ex20999 exception; -- a very popular error number
  6
  7        pragma exception_init(ex20000, -20000);
  8        pragma exception_init(ex20459, -20459);
  9        pragma exception_init(ex20773, -20773);
 10        pragma exception_init(ex20999, -20999);
 11
 12  begin
 13        begin
 14         begin
 15          begin
 16
 17           --
 18           -- Raising our first defined exception
 19           --
 20           raise ex20000;
 21
 22          exception
 23          when ex20000 then
 24
 25           --
 26           -- Return the first error code
 27           -- and where we generated it
 28           --
 29           dbms_output.put(dbms_utility.format_error_stack);
 30           dbms_output.put_line('   First error');
 31           dbms_output.put_line(dbms_utility.format_error_backtrace);
 32
 33          end;
 34
 35          --
 36          -- Raise the second defined error
 37          --
 38          raise ex20459;
 39
 40         exception
 41         when ex20459 then
 42
 43          --
 44          -- Return the error code
 45          -- and where we generated it
 46          --
 47          dbms_output.put(dbms_utility.format_error_stack);
 48          dbms_output.put_line('   Second error');
 49          dbms_output.put_line(dbms_utility.format_error_backtrace);
 50
 51         end;
 52
 53         --
 54         -- Raise third defined error
 55         --
 56         raise ex20773;
 57
 58        exception
 59        when ex20773 then
 60
 61         --
 62         -- Return the error code
 63         -- and where we generated it
 64         --
 65         dbms_output.put(dbms_utility.format_error_stack);
 66         dbms_output.put_line('   Third error');
 67         dbms_output.put_line(dbms_utility.format_error_backtrace);
 68
 69        end;
 70
 71        --
 72        -- Raise last defined error
 73        --
 74        raise ex20999;
 75
 76  exception
 77  when ex20999 then
 78
 79        --
 80        -- Return the error code
 81        -- and where we generated it
 82        --
 83        dbms_output.put(dbms_utility.format_error_stack);
 84        dbms_output.put_line('   Fourth error');
 85        dbms_output.put_line(dbms_utility.format_error_backtrace);
 86
 87  end;
 88  /
ORA-20000:
   First error
ORA-06512: at line 20

ORA-20459:
   Second error
ORA-06512: at line 38

ORA-20773:
   Third error
ORA-06512: at line 56

ORA-20999:
   Fourth error
ORA-06512: at line 74


PL/SQL procedure successfully completed.

SQL>

Not much useful information was presented here, so it’s uncertain what error or errors could have occurred to generate this progression of error messages. [The ORA-06512 error is an informative message as Oracle ‘unwinds’ the error stack and reports what it believes to be as the source of the actual error.] Such user-defined error numbers can be assigned to known Oracle errors, however:

SQL> --
SQL> -- Define error messages
SQL> -- which could be more descriptive
SQL> -- and exceptions which are
SQL> -- easier to handle
SQL> --
SQL>
SQL> declare
  2          ex20206 exception;
  3
  4          pragma exception_init(ex20206, -2060); -- select for update error
  5
  6  begin
  7
  8          raise ex20206;
  9
 10  exception
 11  when ex20206 then
 12          raise_application_error(-20206, 'Attempt to lock distributed tables', true);
 13
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-20206: Attempt to lock distributed tables
ORA-06512: at line 12
ORA-02060: select for update specified a join of distributed tables

SQL> declare
  2
  3          nolock exception;
  4          pragma exception_init(nolock, -69);
  5
  6  begin
  7          execute immediate 'alter table emp add myothercol number';
  8  exception
  9          when nolock then
 10                  raise_application_error(-20909, 'Thet ain''t allowed!!', true);
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-20909: Thet ain't allowed!!
ORA-06512: at line 10
ORA-00069: cannot acquire lock -- table locks disabled for EMP

SQL>

These examples are much clearer in what generated the exceptions and in the nature of the offending operations. [The ORA-00069 error mystically appears after someone has done this to a table:

SQL> alter table emp disable table lock;

Table altered.

SQL>

SQL> alter table emp enable table lock;

Table altered.

SQL>

and then find out why someone else thought it necessary to disable locking on the affected table.]

Oracle does enforce the available error number range, as illustrated below, so existing, defined Oracle errors won’t be ‘stepped on’ inadvertently:

SQL> --
SQL> -- Attempt to raise
SQL> -- an exception using an error number
SQL> -- outside of the acceptable range
SQL> --
SQL>
SQL> begin
  2        raise_application_error(-1400, 'Something strange occurred ...');
  3
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -1400 is out of
range
ORA-06512: at line 2


SQL>

[An ORA-01400 error is generated when attempting to insert a NULL value into a column declared as NOT NULL.]

Apparently application programmers read their error messages and understand perfectly what has transpired, and that’s great for them:

“‘ORA-20618: Excessive flarpage’?!?!? What does THAT mean?!?!?”

“Oh, that means ‘don’t press the F8 key more than once on alternate Tuesdays’.”

“I never would have guessed …”

It isn’t good for the user community in general, however, as they are the ones seeing these ‘artificial’ error messages generated by the application code and, in many cases, have no idea what problems to report to Customer Service when they arise:

SQL>
SQL> --
SQL> -- This could possibly be a bit clearer ...
SQL> --
SQL>
SQL> declare
  2          ex20773 exception;
  3
  4          pragma exception_init(ex20773, -1002);  -- fetch out of sequence error
  5
  6  begin
  7
  8          raise ex20773;
  9
 10  exception
 11  when ex20773 then
 12          raise_application_error(-20773, 'Yew cain''t dew that!!!');
 13
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-20773: Yew cain't dew that!!!
ORA-06512: at line 12


SQL>

In cases where the users have no access to the developers (and the development team hasn’t obscured the package or procedure code with the wrap utility) it may be necessary to look at that code and see exactly what did generate the error. Of course this may ‘backfire’ as the actual error condition may be buried so deep in the code as to be nearly impossible to search for and the error message was generated by the ubiquitous catch-all ‘when others then …’ exception handler:

SQL>
SQL> --
SQL> -- This couldn't possibly be less informative
SQL> --
SQL>
SQL> declare
  2          ex20773 exception;
  3
  4          pragma exception_init(ex20773, -1002);  -- fetch out of sequence error
  5
  6  begin
  7
  8          raise ex20773;
  9
 10  exception
 11  when others then
 12          raise_application_error(-20773, 'Yew cain''t dew that!!!');
 13
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-20773: Yew cain't dew that!!!
ORA-06512: at line 12


SQL>

And, gee whiz, sometimes the developers decide to pass in the SQLCODE and SQLERRM values to RAISE_APPLICATION_ERROR, with disastrous results:

SQL>
SQL> --
SQL> -- Let's try this and see if it flies
SQL> --
SQL> -- we'll declare an exception then pass in the
SQL> -- generated SQLCODE to the
SQL> -- raise_application_error handler
SQL> --
SQL>
SQL> declare
  2          ex21000  exception;
  3
  4          pragma exception_init(ex21000, -19);
  5
  6  begin
  7          raise ex21000;
  8
  9  exception
 10          when ex21000 then
 11                  raise_application_error(SQLCODE, SQLERRM);
 12
 13  end;
 14  /
declare
*
ERROR at line 1:
ORA-21000: error number argument to raise_application_error of -19 is out of
range
ORA-06512: at line 11


SQL>

As mentioned earlier RAISE_APPLICATION_ERROR polices the error code values passed to it, and will unceremoniously complain when that value is out of range. [For those who are curious an ORA-00019 (which would generate the SQLCODE of -19) is a ‘maximum number of session licenses exceeded’ error.]

Possibly a ‘user-centered’ mindset on the part of the application programmers might better serve the end users, and maybe some testing should be done by people outside of the development community to verify that the error messages generated are truly useful to all parties involved.

I’ve blogged here about coding confusing text as error messages, so I won’t mention that topic again in this post. But maybe, just maybe, application programmers should read both posts and change their errant ways so the end users have something meaningful and useful as an error message and, as a result, their calls to the Help Desk aren’t exercises in futility.

Hope springs eternal.

FORECAST Job fails with error message «ORA-20999: General Failure «schedule start from date is null»»