Re: DUP_VAL_ON_INDEX exception handler not firing

Jonathan W. Ingram wrote:
>
> Hello everyone,
>
> I am having an odd problem with a stored procedure on Oracle 7.1.5.2.3
> on OpenVMS 6.2, running on a DEC Alpha 8400 with RAID 0.
>
> The code follows this logic:
>
> BEGIN
> INSERT
> INTO <table>
> (<column names>)
> VALUES (<values>);
>
> n_inserted := n_inserted + 1;
> n_processed := n_processed + 1;
>
> EXCEPTION
> WHEN DUP_VAL_ON_INDEX THEN
> n_updated := n_updated + 1;
> n_processed := n_processed + 1;
>
> WHEN OTHERS THEN
> v_err_text := SQLERRM;
> RAISE LOAD_ERROR;
> END;
>
> My user defined exception LOAD_ERROR is properly defined, as are all
> variables in the procedure. All variables are initialized properly
> (checked before the above block is run).
>
> The problem is this: A DUP_VAL_ON_INDEX exception is raised on the
> insert statement, but instead of falling through the DUP_VAL_ON_INDEX
> exception handler (or after doing so?) falls through the OTHERS
> exception handler and causes the user-defined LOAD_ERROR exception to be
> raised.
>
> The SQLCODE is -1 (primary or unique key violation). This error is
> supposed to be handled by the DUP_VAL_ON_INDEX exception handler
> (clearly stated on page 5-5 of the PL/SQL User's Guide and Reference, as
> well as having worked before for me numerous times).
>
> Testing has revealed that the DUP_VAL_ON_INDEX exception handler is
> *not* being fired at all. The exception is being raised straight to the
> OTHERS exception handler. The DUP_VAL_ON_INDEX exception handler *is*
> used elsewhere in the database and *does* work there.
>
> Logic dictates that there must be a problem with the PL/SQL block
> containing the exception handler, but no one here has been able to
> isolate any problems. I would like to include actual code above;
> however, there are certain security concerns that would have to be
> addressed before I could do this.
>
> If anyone has any generic ideas, I would appreciate hearing them. I can
> work around the problem by explicitly checking SQLCODE in the OTHERS
> handler, but this is a kluge that I don't want to use.
>
> Thanks in advance for your help,
> Jonathan Ingram
 This exception raises only when there is a UNIQUE key violation.

-- 
    AMARENDRA B NETTEM
    ORACLE CONSULTANT
    WHITTMAN-HART CORPORATION, CHICAGO
    (http://www.iit.edu/~nettama)