CODEWITHSUNDEEP
sqloracle-databaseplsql
Andrew Martin
Andrew Martin

Reputation: 5741

What is this Oracle code doing?

I found this article on the Oracle site: Managing Exceptional Behavior, Part 1.

In it, someone had defined an error package, with procedures to raise, handle, report and go, report and stop and log. A breakdown of this package can be found here: errpkg.

The part I'm interested in is here:

IF l_errcode BETWEEN -20999 AND -20000 THEN
    raise_application_error (l_errcode, l_errmsg);
/* Use positive error numbers -- lots to choose from! */
ELSIF l_errcode > 0 AND l_errcode NOT IN (1, 100) THEN
    raise_application_error (-20000, l_errcode || '-' || l_errmsg);
/* Can't EXCEPTION_INIT -1403 */
ELSIF l_errcode IN (100, -1403) THEN
    RAISE NO_DATA_FOUND;
/* Re-raise any other exception. */
ELSIF l_errcode != 0 THEN
    EXECUTE IMMEDIATE
        'DECLARE myexc EXCEPTION; ' ||
        '   PRAGMA EXCEPTION_INIT (myexc, ' || TO_CHAR (err_in) || ');' ||
        'BEGIN  RAISE myexc; END;';
END IF;

That snippet has been pasted in exactly as it appears in the article. I believe that the first if statement is covering all user errors, as -209999 to -20000 is reserved for user created exceptions.

In the first elsif statement (errcode > 0 and not in (1,00)), the code, according to the article, is doing the following:

I also handle positive numbers for application-specific error numbers. By handling positive error message numbers, I am not constrained to error numbers between -20,999 and -20,000, some of which Oracle also uses (although I stay away from 1 and 100, the only two positive error numbers that Oracle does use).

So to me that sounds like he is happy with declaring errors in his code like 150, 160, 170 etc, provided they don't occur between 1 and 100.

However, what are the next two parts of the procedure doing? What is the errcode in (100, -1403) for? I can see it's to do with error code 01403 (no data found), but I don't understand its position in the brackets (100, -1403). And what is the final elsif that does an execute immediate for? I'm struggling to see what its purpose is.

Upvotes: 1

Views: 253

Answers (2)

Julian Ladisch
Julian Ladisch

Reputation: 1437

l_errcode IN (100, -1403) is an abbreviated form of l_errcode = 100 OR l_errcode = -1403. It can be in any order, l_errcode IN (-1403, 100) has exactly the same results. Both 100 and -1403 are errorcodes for no data found.

The final elsif with the execute immediate raises an exception with a user defined error message. Such associate a user defined exceptions with the error code EXCEPTION_INIT is needed: https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/exceptioninit_pragma.htm

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191275

Oracle can use both -1403 and +100 for 'no data found', as mentioned in the exception_int documentation. It's also mentioned in the precompiler manuals; +100 is used in ANSI mode. So the IN (100, -1403) is just covering both possible values for the error raised when no data is found.

The final part seems to be declaring and immediately raising a named exception. You can only use raise application error with the -20000 range of error codes, so this is allowing the built-in exceptions to be raised without having to interpret or declare pragmas for all possible errors. no_data_found is treated explicitly because it has two error codes, and so a caller gets a consistent error whichever one actually occurred, presumably.

I'm struggling to see why you'd use the error package for this though - the article seem to be advocating catching with when others and then using this mechanism to raise a new exception with the same code; which would lose the stack trace. I don't quite get why that would be better than just letting the original exception bubble up naturally.

Upvotes: 4

Related Questions