Oerr Missus – Carry On find that error

Oracle on linux has quite a useful little utility. By simply typing “oerr” then an oracle error number, you’ll get back the text of the message and (occasionally) some useful info about the error.
For those of you who aren’t lucky enough to have your database running on a proper OS, there is a way of getting something almost as good from the database itself.

This sort of thing comes in handy in circumstances such as when Forms spits an error at you like :-

"FRM-40735: PRE-QUERY trigger raised unhandled exception ORA-1403"

You want to find out what ORA-1403 is so you can get on with working out what the problem is.
Well, you can create this simple function on the database :-

CREATE OR REPLACE FUNCTION oerr ( p_errno IN PLS_INTEGER)
   RETURN VARCHAR2 AS
BEGIN
   IF p_errno IN (1, 100) THEN
      --
      -- All Oracle errors are negative numbers
      --...except these two
      --
      RETURN sqlerrm(p_errno);
   ELSE
      RETURN sqlerrm(p_errno * -1);
   END IF;
END;
/

Then, at the SQL prompt, simply issue the following query :-

SQL> SELECT oerr(1403) FROM dual;


ORA-1403 No Data Found

As for working out WHY forms is not behaving, I’m afraid you’re on your own there !

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s