8.1.7 Bug ?

From: Damien Salvador <damien.salvador_at_via.ecp.fr>
Date: 2 Nov 2001 16:23:25 GMT
Message-ID: <slrn9u5i3t.o70.damien.salvador@zen.via.ecp.fr>

Hello,
we're moving from a 8.1.5 to a 8.1.7 on AIX. We're using the base through a lot of stored functions which are returning formated strings.
On 8.1.5 everything was running fine. On 8.1.7 we're encountering a recurrent ORA-6502 error

There is a workaround when storing the parameter in a temporary variable, but that's not completely satisfactory.

Is this a know problem or not ? (we're 'waiting' for an answer from Metalink, but they did not know at once) :

• First function works fine !

create or replace function d_r (jrela in char) return varchar2
is
xml_ret varchar2(200);
dummy char(2) ;
begin
dummy := jrela ;
if dummy is not null then
xml_ret := 'NOT NULL' ;
else
xml_ret := 'NULL' ;
end if;
return xml_ret;
end d_r;
/

• Function, as written for 8.1.5 (does not work on 8.1.7)

create or replace function d_r2(jrela in char) return varchar2
is
xml_ret varchar2(200);
begin
if jrela is not null then
xml_ret := 'NOT NULL' ;
else
xml_ret := 'NULL' ;
end if;
return xml_ret;
end d_r2;
/

Here is our table :

SQL> desc l_relation_ind

Nom                           NULL ?   Type
-------------------------------------------------------
ID_INDIVIDU                   NOT NULL NUMBER(10)
IND_ID_INDIVIDU               NOT NULL NUMBER(10)
CODTYPREL                     NOT NULL VARCHAR2(5)
DATE_RELATION                          DATE
JOUR_RELATION                          CHAR(2)
MOIS_RELATION                          CHAR(2)
ANNEE_RELATION                         CHAR(4)
OBS                                    VARCHAR2(300)

SQL> select * from l_mc_ind where id_individu=1696021 ;

ID_INDIVIDU ID_COM CODTY DATE_REL JO MO ANNE OBS ----------- ------ ----- -------- -- -- ----

1696021     145599 IC91              11 1993     
1696021     145600 IC91                          
1696021      61916 IC91                 1998     
1696021      61917 IC91  19/09/01 19 09 2001     

SQL> select d_r(jour_relation) from l_mc_ind where id_individu=1696021 ;

D_R(JOUR_RELATION)

---

NULL
NULL
NULL
NOT NULL SQL> select d_r2(jour_relation) from l_mc_ind where id_individu=1696021 ; ERROR:
ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: tampon de chaîne de caractères trop petit ORA-06512: à "DBA_NSI.D_R2", ligne 6
ORA-06512: à ligne 1

(that is, "numeric or value error : string buffer too small")

--

Damien
Unix Guru View of Sex :
# unzip ; strip ; touch ; finger ; mount ; fsck ; more ; yes ; umount ; sleep Received on Fri Nov 02 2001 - 10:23:25 CST