CREATE OR REPLACE PROCEDURE compile_from_file (
   dir_in    IN   VARCHAR2
  ,file_in   IN   VARCHAR2
)
IS
   l_file    UTL_FILE.file_type;
   l_lines   DBMS_SQL.varchar2s;
   l_cur     PLS_INTEGER        := DBMS_SQL.open_cursor;
BEGIN
   l_file := UTL_FILE.fopen (dir_in, file_in, 'R');

   BEGIN
      LOOP
         UTL_FILE.get_line (l_file, l_lines (l_lines.COUNT + 1));
      END LOOP;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
   END;

   UTL_FILE.fclose (l_file);
   DBMS_SQL.parse (c => l_cur
                  ,statement => l_lines
                  ,lb => l_lines.FIRST
                  ,ub => l_lines.LAST
                  ,lfflg => TRUE
                  ,language_flag =>DBMS_SQL.native
                  );
   DBMS_SQL.close_cursor (l_cur);
EXCEPTION
   WHEN OTHERS
   THEN
      UTL_FILE.fclose (l_file);
      DBMS_SQL.close_cursor (l_cur);
      DBMS_OUTPUT.put_line ('Compile from file failure: ');
      DBMS_OUTPUT.put_line (SUBSTR (SQLERRM, 1, 255));
END compile_from_file;
/