Creating a procedure for task to work it workshifts

I am creating a procedure for task for a workshift and i stumble upon some errors can you help me?

CREATE OR REPLACE PROCEDURE check_task_turnos
AS
BEGIN
   FOR i
      IN (SELECT calendar_start,
                 calendar_end,
                 estado,
                 CASE
                    WHEN     TO_NUMBER (TO_CHAR (calendar_start, 'HH24')) >=
                                7
                         AND TO_NUMBER (TO_CHAR (calendar_start, 'HH24')) <
                                15
                         AND estado = 'Aceite'
                    THEN
                       'apex-cal-yellow'
                    ELSE
                       NULL
                 END
            FROM PASSAGEM)
   LOOP
      -- registo no LOG , aqui nao registas na PASSAGEM, registas numa tabela ao lado de LOG para teres referencia de quando foi alterada a data da tarefa
      INSERT INTO PASSAGEM_LOG (passagem_id, start_date, end_date)
              VALUES (
                        i.id,
                        i.calendar_start + (SYSDATE - 8 / 24),
                        i.calendar_end + (SYSDATE - 8 / 24));

      -- update das horas de inico e fim para posicionamento no calendario
      UPDATE PASSAGEM
         SET calendar_start = i.calendar_start + (SYSDATE - 8 / 24) --incrementamos 8horas (periodod e um turno
                                                                   ,
             calendar_end = i.calendar_end + (SYSDATE - 8 / 24) --incrementamos 8horas (periodod e um turno
       WHERE ID = i.ID;
   END LOOP;

   COMMIT;
END;

Errors:

Error(11,17): PL/SQL: SQL Statement ignored    
Error(15,42): PL/SQL: ORA-00984: column not allowed here    
Error(15,42): PLS-00302: component 'ID' must be declared    
Error(19,17): PL/SQL: SQL Statement ignored    
Error(22,28): PL/SQL: ORA-00904: "I"."ID": invalid identifier    
Error(22,30): PLS-00302: component 'ID' must be declared

1 answer

  • answered 2018-11-14 10:58 user7294900

    You are missing ID for i, add it in select:

     FOR i IN (SELECT ID, calendar_start, calendar_end, estado