DUP_VAL_ON_INDEX exception handler not firing

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;

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 Received on Tue Oct 15 1996 - 00:00:00 CEST