CREATE OR REPLACE PROCEDURE CONVERTE_FOTOPRATO_LONG_RAW
AS
BEGIN
   FOR CUR_FOTOS IN
   (
      SELECT NRFOTOPRATOBLOB, CDFILAUXPRAT, CDPRATO, IDRECEITA, CDFILIAL, CDSERVFILI, NRFOTO
        FROM FOTOPRATOBLOB
       WHERE FGFOTOPRATO IS NULL
   ) LOOP
      IF CUR_FOTOS.IDRECEITA = 'P' THEN
         UPDATE FOTOPRATOBLOB
            SET FGFOTOPRATO = (SELECT TO_LOB(FGFOTOPRAPAD) FROM FOTOPRAPAD
                                WHERE CDFILAUXPRAT = CUR_FOTOS.CDFILAUXPRAT
                                  AND CDPRATO = CUR_FOTOS.CDPRATO
                                  AND NRFOTOPRAPAD = CUR_FOTOS.NRFOTO)
          WHERE NRFOTOPRATOBLOB = CUR_FOTOS.NRFOTOPRATOBLOB;
      END IF;
      IF CUR_FOTOS.IDRECEITA = 'F' THEN
         UPDATE FOTOPRATOBLOB
            SET FGFOTOPRATO = (SELECT TO_LOB(FGFOTOPRAFIL) FROM FOTOPRAFIL
                                WHERE CDFILAUXPRAT = CUR_FOTOS.CDFILAUXPRAT
                                  AND CDPRATO = CUR_FOTOS.CDPRATO
                                  AND CDFILIAL = CUR_FOTOS.CDFILIAL
                                  AND NRFOTOPRAFIL = CUR_FOTOS.NRFOTO)
          WHERE NRFOTOPRATOBLOB = CUR_FOTOS.NRFOTOPRATOBLOB;
      END IF;
      IF CUR_FOTOS.IDRECEITA = 'S' THEN
         UPDATE FOTOPRATOBLOB
            SET FGFOTOPRATO = (SELECT TO_LOB(FGFOTOPRASER) FROM FOTOPRASER
                                WHERE CDFILAUXPRAT = CUR_FOTOS.CDFILAUXPRAT
                                  AND CDPRATO = CUR_FOTOS.CDPRATO
                                  AND CDFILIAL = CUR_FOTOS.CDFILIAL
                                  AND CDSERVFILI = CUR_FOTOS.CDSERVFILI
                                  AND NRFOTOPRASER = CUR_FOTOS.NRFOTO)
          WHERE NRFOTOPRATOBLOB = CUR_FOTOS.NRFOTOPRATOBLOB;
      END IF;
   END LOOP;
END;
/

DECLARE
   V_JOB NUMBER;
   V_INVALID NUMBER;
BEGIN
   SELECT COUNT(1) 
      INTO V_INVALID
   FROM USER_OBJECTS
   WHERE OBJECT_NAME = 'CONVERTE_FOTOPRATO_LONG_RAW'
     AND STATUS = 'INVALID';
   IF V_INVALID = 0 THEN
      -- Apagar formato antigo de JOB
      FOR C_JOBS IN (SELECT JOB
                      FROM ALL_JOBS
                     WHERE WHAT LIKE '%CONVERTE_FOTOPRATO_LONG_RAW%') LOOP
         DBMS_JOB.REMOVE(C_JOBS.JOB);
      END LOOP;
      --Verifica se o job ja existe
      SELECT COUNT(*) INTO V_JOB
      FROM USER_SCHEDULER_JOBS
      WHERE JOB_NAME = 'JOB_FOTOPRATO_LRAW';
      
      IF V_JOB > 0 THEN
         DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'JOB_FOTOPRATO_LRAW');
      END IF;

      DBMS_SCHEDULER.CREATE_JOB (
      JOB_NAME => 'JOB_FOTOPRATO_LRAW',
      JOB_TYPE => 'PLSQL_BLOCK',
      JOB_ACTION => 'BEGIN CONVERTE_FOTOPRATO_LONG_RAW; COMMIT; END;',
      START_DATE => systimestamp,
      repeat_interval => 'FREQ=SECONDLY;INTERVAL=30',
      JOB_CLASS => '"DEFAULT_JOB_CLASS"',
      ENABLED => TRUE,
      AUTO_DROP => FALSE,
      COMMENTS => '');

      COMMIT;

   ELSE
      EXECUTE IMMEDIATE 'DROP PROCEDURE CONVERTE_FOTOPRATO_LONG_RAW'; 
   END IF;
END; 
/