PLS-00049: bad bind variable 'X_VARIABLE'

Question

I'm getting the following error when I trying to get a record from a table, in this case, is (SERVICIOENDIAS)/SERVICETIMEINDAYS and I don't know why I'm getting that errr :

Trigger TRG_CONTROL_SERVICIO compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
20/11     PLS-00049: bad bind variable 'NEW.V_SERVICIODIAS'
Errors: check compiler log
 

I'm a dummy with Oracle and possibly I may do a lot of mistakes and bad practices, so I'll appreciate it if you can say me that.

  CREATE OR REPLACE TRIGGER TRG_CONTROL_SERVICIO
BEFORE
INSERT OR UPDATE
ON SERVICIO
FOR EACH ROW

DECLARE
V_SERVICIODIAS INT;

CURSOR C_SERVICIODIAS IS 
    SELECT TIEMPOSERVICIODIAS INTO V_SERVICIODIAS
    FROM SERVICIO;
BEGIN
FOR R_SERVICIODIAS IN C_SERVICIODIAS LOOP
    IF INSERTING THEN
        IF :NEW.FECHAREGISTRO > SYSDATE THEN
               LOG_AUDIT( USER, 
                          SYSDATE, 
                          'SERVICIO', 
                          'INSERT FECHA INCORRECTA', 
                          'SE EJECUTO EL TRG_CONTROL_SERVICIO'); 
               RAISE_APPLICATION_ERROR(-20000, 'FECHA INCORRECTA, INTENTE DE NUEVO');
       ELSE
          DBMS_OUTPUT.PUT_LINE('INSERTANDO UN SERVICIO');
       END IF;
       IF :NEW.V_SERVICIODIAS <= 60 THEN
        DBMS_OUTPUT.PUT_LINE('INSERTANDO UN SERVICIO');
       ELSE
          LOG_AUDIT( USER, 
                          SYSDATE, 
                          'SERVICIO', 
                          'NO SE PUEDE UN SERVICIO MAYOR A 60 DIAS', 
                          'SE EJECUTO EL TRG_CONTROL_SERVICIO'); 
          RAISE_APPLICATION_ERROR(-20000, 'NO SE PUEDE REGISTRAR EL SERVICIO, MUCHAS HORAS',TRUE);
       END IF;
    ELSIF UPDATING THEN
        IF :NEW.FECHAREGISTRO <> :OLD.FECHAREGISTRO AND :NEW.FECHAREGISTRO > SYSDATE THEN
                 LOG_AUDIT( USER, 
                          SYSDATE, 
                          'SERVICIO', 
                          'PROHIBIDO UPDATE FECHAREGISTRO',
                          'SE EJECUTO EL TRG_CONTROL_SERVICIO'); 
              RAISE_APPLICATION_ERROR(-20000, 'FECHA INCORRECTA, NO SE PUEDE MODIFICAR ESTA FECHA',TRUE);
    ELSE
        DBMS_OUTPUT.PUT_LINE('ACTUALIZANDO UN EMPLEADO');
    END IF;
END IF;
END LOOP;
END;

Answer 1

As has been pointed out, V_SERVICIODIAS is a local variable so it's not part of the :NEW pseudorecord. You'd use

IF V_SERVICIODIAS <= 60 THEN

Once you do that, the next issue is that nothing is actually populating that local variable so the if statement can never be true. That's because there is a syntax error in your cursor definition. You use an into clause if you have a select statement that returns a single row not when you're defining a cursor that you plan on looping over

CURSOR C_SERVICIODIAS IS 
    SELECT TIEMPOSERVICIODIAS 
    FROM SERVICIO;

Once you fix the cursor definition error, you'd get rid of the local variable entirely and just reference the column from the cursor.

IF R_SERVICIODIAS.TIEMPOSERVICIODIAS <= 60 THEN

Having done all that, however, you're going to encounter a mutating table runtime error because a row-level cursor on the SERVICIO isn't going to be able to query the SERVICIO table. And, unfortunately, at that point it's not obvious how to resolve the problem because it's not obvious why the cursor exists in the first place. Why would you want to loop over every row in the table every time you insert a new row into the table? That doesn't really make any sense.

One possibility is that you don't actually want to loop over every row in the table. Perhaps you want to remove the V_SERVICIODIAS local variable and the cursor definition and the loop and just reference the TIEMPOSERVICIODIAS from the current row.

IF :NEW.TIEMPOSERVICIODIAS <= 60 THEN