Oracle PL/SQL Tutorial/System Packages/File Read with UTL FILE

Get a line from a file with UTL_FILE package

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE get_line(file_in IN UTL_FILE.FILE_TYPE, line_out OUT VARCHAR2, eof_out OUT BOOLEAN)
  2  IS
  3  BEGIN
  4     UTL_FILE.GET_LINE (file_in, line_out);
  5     eof_out := FALSE;
  6  EXCEPTION
  7     WHEN NO_DATA_FOUND
  8     THEN
  9        line_out := NULL;
 10        eof_out  := TRUE;
 11  END;
 12  /

SQL>
SQL>

Read file

SQL>
SQL> CREATE OR REPLACE PROCEDURE readit
  2  IS
  3    v_filehandle_input UTL_FILE.FILE_TYPE;
  4    v_newline     VARCHAR2(32767);
  5  BEGIN
  6    v_filehandle_input := UTL_FILE.FOPEN("c:\temp","functions.sql", "r", 32767 );
  7   loop
  8    BEGIN
  9      UTL_FILE.GET_LINE(v_filehandle_input,v_newline);
 10      DBMS_OUTPUT.PUT_LINE(v_newline);
 11    EXCEPTION
 12      WHEN NO_DATA_FOUND THEN EXIT ;
 13    END;
 14   end loop;
 15    UTL_FILE.FCLOSE (v_filehandle_input) ;
 16  end;
 17  /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE READIT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/22     PL/SQL: Item ignored
3/22     PLS-00201: identifier "UTL_FILE" must be declared
6/3      PL/SQL: Statement ignored
6/3      PLS-00320: the declaration of the type of this expression is
         incomplete or malformed
9/5      PL/SQL: Statement ignored
9/23     PLS-00320: the declaration of the type of this expression is
         incomplete or malformed
15/3     PL/SQL: Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
15/20    PLS-00320: the declaration of the type of this expression is
         incomplete or malformed
SQL>
SQL>
SQL>
SQL>

Read from the CLOB in chunks of 1000 characters and write to the output file

SQL>
SQL> CREATE TABLE myClob
  2  (id NUMBER PRIMARY KEY,
  3   clob_data CLOB);
Table created.
SQL>
SQL> DECLARE
  2    clob_pointer CLOB;
  3    v_Buf VARCHAR2(1000);
  4    Amount BINARY_INTEGER :=1000;
  5    Position INTEGER :=1;
  6    fp UTL_FILE.FILE_TYPE;
  7  BEGIN
  8
  9    insert into myClob values (1,EMPTY_CLOB());
 10
 11    SELECT clob_data INTO clob_pointer FROM myClob WHERE id = 1;
 12
 13    if (DBMS_LOB.ISOPEN(clob_pointer)!=1) then
 14
 15      DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READONLY);
 16
 17    end if;
 18
 19    fp :=UTL_FILE.FOPEN("BFILE_DIR","output.dat","w");
 20
 21    LOOP
 22
 23      BEGIN
 24
 25        DBMS_LOB.READ (clob_pointer,Amount,Position,v_Buf);
 26
 27        UTL_FILE.PUT_LINE(fp,v_Buf,TRUE);
 28
 29        Position :=Position +Amount;
 30
 31      EXCEPTION
 32
 33        WHEN NO_DATA_FOUND THEN
 34
 35          EXIT;
 36
 37      END;
 38
 39    END LOOP;
 40
 41    UTL_FILE.FCLOSE(fp);
 42
 43    DBMS_LOB.CLOSE (clob_pointer);
 44  END;
 45  /

SQL>
SQL> drop table myClob;

Show file content

SQL>
SQL> CREATE OR REPLACE PROCEDURE show_file (loc IN VARCHAR2,file IN VARCHAR2)
  2  IS
  3     fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (loc, file, "R");
  4     line VARCHAR2(2000);
  5  BEGIN
  6     DBMS_OUTPUT.PUT_LINE (file);
  7     LOOP
  8        UTL_FILE.GET_LINE (fid, line);
  9        DBMS_OUTPUT.PUT_LINE (line);
 10     END LOOP;
 11  EXCEPTION
 12     WHEN OTHERS THEN UTL_FILE.FCLOSE (fid);
 13  END;
 14  /

SQL>