How would you retrieve specific error codes from an oracle database programmatically?

Question

Scenario:
I wish to define a constant for the DUP_VAL_ON_INDEX error code:

GCN_DUP_VAL_ON_INDEX        CONSTANT NUMBER := -1;

However, I would like to set the value programmatically:

GCN_DUP_VAL_ON_INDEX        CONSTANT NUMBER := DUP_VAL_ON_INDEX.ERRCODE;

Is there a way to do this w/out creating a custom function like this?:

CREATE OR REPLACE FUNCTION GET_DUP_VAL_ERR_CODE
RETURN INTEGER
IS
BEGIN
   raise dup_val_on_index;
EXCEPTION
   WHEN OTHERS THEN
      RETURN SQLCODE;
END;
/

what for? it's more common to do the very opposite things - define constant exceptions for error codes... — Kirill Leontev
– Kirill Leontev, Commented May 10, 2012 at 19:25
We are bulk collecting the erorrs during a forall loop, then doing logging for each exception. We use the error code to determine what to do next. — ScrappyDev
– ScrappyDev, Commented May 10, 2012 at 20:10
@scrappythenell, a forall will raise a single error for the entire data-set rather than a single record in that data-set. What happens if you want to do something else for only a single record? Is such a situation possible? — Ben
– Ben, Commented May 10, 2012 at 21:29

Kirill Leontev · Accepted Answer · 2012-05-10 21:15:59Z

1

Probably not.

Check out SYS.STANDARD package - it's the place where all the exceptions, SQL functions, and everything else "predefined" is actually defined.

Pretty much straightforward:

DUP_VAL_ON_INDEX exception;
    pragma EXCEPTION_INIT(DUP_VAL_ON_INDEX, '-0001');

answered May 10, 2012 at 21:15

Kirill Leontev

11k8 gold badges46 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How would you retrieve specific error codes from an oracle database programmatically?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related