Re: Weird ORA-06502 error

On Fri, 13 Nov 1998 12:51:21 +0200, "Antti Rauhala" <antti.rauhala_at_tietosavo.fi> wrote:

>Hello everybody,
>I get an ORA-06502 (numeric or value error) in a PL/SQL procedure. The funny
>thing is that the statement which causes the error has already been executed
>several times, and the statement is a static boolean value assignment.
>
>This procedure is used for data warehouse loading, and it should process a
>large number of rows. Now the execution ends after 20000-50000 rows. The
>number of rows processed before the error is never exactly the same, even
>though the source tables have not changed! And, as I said earlier, the
>problem statement already has been executed many times. I don't understand
>how a static assignment like bReject := FALSE; can change so that once it
>executes ok and after that it causes an error.
>
>I'm all confused with this, please help if you can. Here's a piece of my
>code:
>
>...
>bReject BOOLEAN;
>...
>IF (bReject = FALSE) THEN
> BEGIN
> INSERT INTO DWF1_ENNUSTE(
> dwf1_kayttopaikka, dwf1_aika,
> dwf1_sopimus, dwf1_komponentti,
> dwf1_mk, dwf1_maara)
> VALUES(
> iKPSur, iAikaSur,
> iSopSur, iKompSur,
> dMkSumma, dMaaraSumma)
> ;
> EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
> bReject := TRUE;
> END;
>END IF;
>
>

It can't. You must be getting the ORA-06502 on the insert. Try this:

begin
  insert .....
exception
  when DUP_VAL_ON_INDEX then
    ...
  when VALUE_ERROR then
    write out the insert values.
end;

My guess is that there is some bogus data that you are trying to insert.

chris.

>
>All help would be appreciated!
>Regards,
>Antti Rauhala
>Tietosavo Oy
>antti.rauhala_at_tietosavo.fi
>
Received on Fri Nov 13 1998 - 00:00:00 CST