Re: ORA-06502 when moving from 8.0.5 to 8.1.7

From: NM <nimesh_myanger_at_hotmail.com>
Date: 20 Dec 2001 02:22:23 -0800
Message-ID: <bdae89b.0112200222.10e157f9@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 Received on Thu Dec 20 2001 - 04:22:23 CST