Re: ORA-06502 when moving from 8.0.5 to 8.1.7

nimesh_myanger_at_hotmail.com (NM) wrote in message news:<bdae89b.0112200222.10e157f9_at_posting.google.com>...
> I set up a test environment on both systems (8.0.5 and 8.1.7) like
> below:
>
> i) created table TEST_6502
>
> create table TEST_6502
> (
> BUY CHAR(3) null,
> SLL CHAR(3) null
> );
>
> ii) created function TEST_6502_FUNCT
>
> create or replace function TEST_6502_FUNCT(buycurr IN CHAR, sellcurr
> IN CHAR)
> RETURN CHAR IS
>
> BEGIN
> IF buycurr IS NOT NULL THEN
> RETURN buycurr;
> ELSE
> RETURN sellcurr;
> END IF;
> END TEST_6502_FUNCT;
>
>
> If I now insert the following:
>
> insert into TEST_6502 values ('','GBP');
> insert into TEST_6502 values ('USD','GBP');
>
> and run the function:
>
> SQL> select test_6502_funct(buy,sll) from test_6502;
>
> Oracle 8.1.7 will generate the ORA-06502 error. It seems that if the
> function encounters a NULL field in the first select, then a
> subsequent select will generate an error. This seems to suggest that
> in 8.1.7, there is some sort of caching in the variables of a
> function.
>
> The question now is, what should I do to avoid this? Should I
> initialise buycurr and sellcurr at the beginning of the function? If I
> do that, is that not going to hit performance?
>
> Best regards,
> NM

I remember that there was a bug where Oracle failed to cast nulls to being equal to all datatypes in pl/sql. You may well be hitting that bug, but I am not sure what the work around was. If you have support you might try using the advanced search against the bug database only to eliminate a ton of forum posts where people get this error for bad code. With luck you will find the right but report to see how to cast your result. You might also be able to devise your own work around using the nvl function.

• Mark D Powell --