Invalid number when the input is the right one and the table definition is also the right one?

So I’m doing this script and at some point it worked. But I had to do it with bind variables so some of it changed. Once I did the changes and applied the bind variables isn’t working anymore and it throws these errors.

SET SERVEROUTPUT ON;
VAR B_NUMRUT_EMP NUMBER;
EXEC :B_NUMRUT_EMP := &RUT;
VAR B_BONI_EMP NUMBER;
EXEC :B_BONI_EMP := &BONI;
DECLARE
    V_NOMBRE_EMP EMPLEADO.NOMBRE_EMP%TYPE;
    V_APPATERNO_EMP EMPLEADO.APPATERNO_EMP%TYPE;
    V_APMATERNO_EMP EMPLEADO.APMATERNO_EMP%TYPE;
    V_DVRUT_EMP EMPLEADO.DVRUT_EMP%TYPE;
    V_SUELDO_EMP EMPLEADO.SUELDO_EMP%TYPE;
    V_BONIFICACION V_SUELDO_EMP%TYPE;
    V_ID_CATEGORIA_EMP EMPLEADO.ID_CATEGORIA_EMP%TYPE;
BEGIN
    SELECT
        E.NOMBRE_EMP,
        E.APPATERNO_EMP,
        E.APMATERNO_EMP,
        E.DVRUT_EMP,
        E.SUELDO_EMP,
        E.ID_CATEGORIA_EMP
        INTO
            V_NOMBRE_EMP,
            V_APPATERNO_EMP,
            V_APMATERNO_EMP,
            V_DVRUT_EMP,
            V_SUELDO_EMP,
            V_ID_CATEGORIA_EMP
    FROM
        EMPLEADO E INNER JOIN CATEGORIA_EMPLEADO CE
        ON (E.ID_CATEGORIA_EMP = CE.ID_CATEGORIA_EMP)
    WHERE
        E.NUMRUT_EMP = :B_NUMRUT_EMP 
        AND E.SUELDO_EMP BETWEEN 0 AND 5000000 
        AND CE.ID_CATEGORIA_EMP <> 'Ejecutivo de Arriendo';
       
    V_BONIFICACION := V_SUELDO_EMP * (:B_BONI_EMP/100);
    DBMS_OUTPUT.PUT_LINE('DATOS CALCULO BONIFICACION EXTRA DEL ' || :B_BONI_EMP|| '% DEL SUELDO');
    DBMS_OUTPUT.PUT_LINE('Nombre Empleado : ' || V_NOMBRE_EMP || ' ' || V_APPATERNO_EMP || ' ' || V_APMATERNO_EMP);
    DBMS_OUTPUT.PUT_LINE('RUN : ' || :B_NUMRUT_EMP || '-' || V_DVRUT_EMP);
    DBMS_OUTPUT.PUT_LINE('Sueldo : ' || V_SUELDO_EMP);
    DBMS_OUTPUT.PUT_LINE('Bonificación extra : ' || V_BONIFICACION);
END;

ORA-01722: número no válido ORA-06512: en línea 10 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number.

I don’t know why is it throwing these when I checked multiple times line by line if something is wrong and I can’t find anything at all.

Also, table definition here:

NUMRUT_EMP        NOT NULL NUMBER(10)   
DVRUT_EMP         NOT NULL VARCHAR2(1)  
APPATERNO_EMP     NOT NULL VARCHAR2(15) 
APMATERNO_EMP     NOT NULL VARCHAR2(15) 
NOMBRE_EMP        NOT NULL VARCHAR2(25) 
DIRECCION_EMP     NOT NULL VARCHAR2(60) 
ID_ESTCIVIL       NOT NULL NUMBER(1)    
FONOFIJO_EMP      NOT NULL VARCHAR2(15) 
CELULAR_EMP                VARCHAR2(15) 
FECNAC_EMP                 DATE         
FECING_EMP        NOT NULL DATE         
SUELDO_EMP        NOT NULL NUMBER(7)    
ID_COMUNA                  NUMBER(3)    
ID_CATEGORIA_EMP           NUMBER(1)    
NUMRUT_SUPERVISOR          NUMBER(10)

I tried to enter these 2 variables through substitution variables and linked them to bind variables (I was asked to so I need to do it this way)

>Solution :

You filter the query on:

AND CE.ID_CATEGORIA_EMP <> 'Ejecutivo de Arriendo';

But your table definition describes the column as:

ID_CATEGORIA_EMP           NUMBER(1)

This is what is giving the error as the string literal cannot be converted to a number.

Leave a Reply