numeric or value error: character string buffer too small?

Question

I have this procedure:

CREATE OR REPLACE PROCEDURE PROC_DESCUENTO (CLIENTE IN VARCHAR2)

IS V_PRODUCTO VARCHAR2(30);
V_CLIENTE  VARCHAR2(45);
V_DESCUENTO NUMBER;

BEGIN
 SELECT P.DESCRIPCION,
        Z.NOMBRE ' 'Z.APELLIDOM ' 'Z.APELLIDOP AS NOMBRE,
        (P.VUNITARIOD.CANTIDAD)0.2 AS DESCUENTO

 INTO 
    V_PRODUCTO,
    V_CLIENTE,
    V_DESCUENTO

     FROM DETALLE D JOIN PRODUCTO P
ON d.cod_detalle = p.codproducto
JOIN BOLETA B
ON(b.cod_boleta = d.cod_detalle)
JOIN CLIENTE C
ON(c.cod_cliente = b.cod_boleta)
JOIN PERSONA z
ON(c.rut = z.rut)
WHERE z.nombre = CLIENTE
ORDER BY d.cantidad desc;

DBMS_OUTPUT.PUT_LINE(V_PRODUCTOV_CLIENTEV_DESCUENTO);
END PROC_DESCUENTO ;

This compiles very well but when I'm going to execute this procedure using a string parameter (client's name called Barry in my BD)

SET SERVEROUTPUT ON;-- LLAMDO POR PARAMETROS PROCEDIMIENTO
EXECUTE PROC_DESCUENTO('Barry');

numeric or value error: character string buffer too small

I don't know why I get this since Barry has 4 digits only on his name.

Popeye Popeye · Accepted Answer · 2020-07-02T05:32:17

You need to increase the size of the variables as follows:

V_PRODUCTO PRODUCTO.DESCRIPCION%type;
V_CLIENTE  VARCHAR2(200); -- sum of size of 3 columns whose data is feed in it + 2
V_DESCUENTO NUMBER;

Also, following is not correct:

Z.NOMBRE ' 'Z.APELLIDOM ' 'Z.APELLIDOP AS NOMBRE,

It should be

Z.NOMBRE || ' ' || Z.APELLIDOM || ' ' ||Z.APELLIDOP AS NOMBRE,

Apart from it, ORDER BY d.cantidad desc is irrelevant here. Remove it from query.

Also, last statement in the procedure is incorrect. It should be -

DBMS_OUTPUT.PUT_LINE(V_PRODUCTO || ' ' || V_CLIENTE || ' ' || V_DESCUENTO);

numeric or value error: character string buffer too small?

1 Answers