CREATE PROCEDURE File_check
AS
  v_loop_till_success1 VARCHAR2(1) := 'N';
  v_loop_till_success  VARCHAR2(1) := 'N';
  vinhandle            utl_file.file_type;
  mstate               VARCHAR2(1000);
  mcode                NUMBER;
  ffdate               DATE;
  vnewline             VARCHAR2(250);
BEGIN
    LOOP
        BEGIN
            vinhandle := utl_file.Fopen('INPUT_DIR', 'SMS.TXT', 'R');

            IF utl_file.Is_open(vinhandle) THEN
              INSERT INTO tb_file
              VALUES      ('SMS.TXT',
                           SYSDATE,
                           'Y');
            ELSE
              INSERT INTO tb_file
              VALUES      ('SMS.TXT',
                           SYSDATE,
                           'N');
            END IF;

            SELECT status
            INTO   v_loop_till_success1
            FROM   tb_file;

            IF v_loop_till_success1 = 'Y' THEN
              v_loop_till_success := 'Y';
            END IF;

            EXIT WHEN v_loop_till_success = 'Y';

            dbms_lock.Sleep(10);
        END;
    END LOOP;
END file_check;