Re: 8.1.7 Bug ?

Sybrand Bakker wrote:
>
> "Damien Salvador" <damien.salvador_at_via.ecp.fr> wrote in message
> news:slrn9u5i3t.o70.damien.salvador_at_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
>
> Is this the *complete* function?
> If so, why can't you simply use the built-in decode?
>
> Regards
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
> to reply remove '-verwijderdit' from my e-mail address

Probably because the decode will be a bucketload slower (if they are using this function not as a replacement for decode in sql)

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

http://www.oracledba.co.uk

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