Re: DUP_VAL_ON_INDEX exception handler not firing
Date: 1996/10/16
Message-ID: <5439kg$9nu_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <3263FF06.7705_at_attws.com>, "Jonathan W. Ingram" <jonathan.ingram_at_attws.com> writes:
|> 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.
It sounds like you are running into bug 177533. This bug is fixed in PL/SQL 2.2, and deals with incorrect handling of DUP_VAL_ON_INDEX. There are different symptoms, including getting NO_DATA_FOUND instead (which you may be seeing here - try WHEN DUP_VAL_ON_INDEX OR NO_DATA_FOUND in your exception handler). I would recommend calling Oracle support and confirming this with them.
|>
|> 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
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Wed Oct 16 1996 - 00:00:00 CEST