DUP_VAL_ON_INDEX exception handler not firing

[Jonathan W. Ingram]

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

[Scott Urman]

In article <3263FF...@attws.com>, "Jonathan W. Ingram" <jonatha...@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.

------------------------------------------------------------------------
Scott Urman Oracle Corporation sur...@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"
------------------------------------------------------------------------

[AMARENDRA B NETTEM]

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.
>

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)

[ra...@consensoft.com]

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