Re: DUP_VAL_ON_INDEX exception handler not firing

AMARENDRA B NETTEM wrote:
>
> 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)

Hi,

   Here is what I would do.

   Do not have any executable statements between the actual SQL and    the exception handler.

   Add "DBMS_OUTPUT.PUT_LINE('Sqlcode : ' || SQLCODE);"    for the DUP_VAL_ON_INDEX and for the OTHERS clause. This    will tell you exactly what and where the error is occuring.    (If you cannot use DBMS_OUTPUT package, insert into an     errors table).

Good Luck,
Radhakrishnan Received on Thu Oct 17 1996 - 00:00:00 CEST