Getting an Error “ORA-06502: PL/SQL: numeric or value error: character string buffer too small”

Question

This is my query

  begin
              select ceq_specimens.numero as NUMERO,
                       analyseEffectuee.DESCRIPTION as analyseEffectuee
              into out_rec.NUMERO_SPECIMEN3, out_rec.SPEC3_ANALYSE_EFFECTUE          
              from CEQ_FORMULAIRES_ANALYSES
              inner join ceq_liste_choix analyseEffectuee on analyseEffectuee.ID_LISTE_CHOIX=CEQ_FORMULAIRES_ANALYSES.ID_ANALYSE_EFFECTUE  
              inner join ceq_specimens on ceq_specimens.ID_SPECIMEN=CEQ_FORMULAIRES_ANALYSES.ID_SPECIMEN and ceq_specimens.ID_SPECIMEN=vintIdSpecimen3
              where CEQ_FORMULAIRES_ANALYSES.ID_FORMULAIRE=out_rec.ID_FORMULAIRE;
          EXCEPTION
            WHEN NO_DATA_FOUND THEN
            out_rec.NUMERO_SPECIMEN3 := ' ';
            out_rec.SPEC3_ANALYSE_EFFECTUE := ' ';  
          END;              

...

IF analyseEffectuee.DESCRIPTION as analyseEffectuee = Spécimen impossible à analyser: Préciser en commentaire(s)

I get error ''string buffer too small”

IF analyseEffectuee.DESCRIPTION as analyseEffectuee= Non

No problem in this case

Thanks for helping me!

what are the type and size of analyseEffectuee.DESCRIPTION and out_rec.SPEC3_ANALYSE_EFFECTUE ? If I had to guess I would say you have a size mismatch: the recipient is smaller than the content. — Vincent Malgrat

APC APC · Accepted Answer · 2012-07-26T15:20:43

I get Error ''string buffer too small” "

What that means is your variable out_rec.SPEC3_ANALYSE_EFFECTUE is not big enough to hold teh value Spécimen impossible à analyser: Préciser en commentaire(s).

The best way to define PL/SQL variables is to use the %TYPE keyword. This creates a variable which matches the column's definition.

Your code uses something called OUTREC. You haven't given us the definition of this, which makes it harder to correct your specfic problem but perhaps you should be doing something like this. Declare a PL/SQL record type which matches you're desired output, then declare a variable of that type:

type my_rec_t is record (
    NUMERO_SPECIMEN1 ceq_specimens.numer%type,
   SPEC1_ANALYSE_EFFECTUE    analyseEffectuee.DESCRIPTION%type,        
    NUMERO_SPECIMEN2 ceq_specimens.numer%type,
   SPEC2_ANALYSE_EFFECTUE    analyseEffectuee.DESCRIPTION%type,        
    NUMERO_SPECIMEN3 ceq_specimens.numer%type,
   SPEC3_ANALYSE_EFFECTUE    analyseEffectuee.DESCRIPTION%type        
); 
out_rec my_rec_t;

ORA-02303: cannot drop or replace a type with type or table dependents

So this means you're using SQL Types, with inheritance. Sorting this out is a bit of a problem. There is some syntax which we can use with the ALTER TYPE command which allows us to handle dependent types. Find out more.

Getting an Error “ORA-06502: PL/SQL: numeric or value error: character string buffer too small”

1 Answers