Re: 8.1.7 Bug ?

Damien Salvador wrote:
>
> 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

Both compile fine on my db... (8.1.7.1)

mcdonac_at_db1> create or replace function d_r2(jrela in char)   2 return varchar2 is
  3 xml_ret varchar2(200);
  4 begin
  5 if jrela is not null then
  6 xml_ret := 'NOT NULL' ;
  7 else
  8 xml_ret := 'NULL' ;
  9 end if;
 10 return xml_ret;
 11 end d_r2;
 12 /

Function created.

mcdonac_at_db1>

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."