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

Get a line from a file with UTL_FILE package

   <source lang="sql">

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></source>

Read file

   <source lang="sql">

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></source>

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

   <source lang="sql">

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;</source>

Show file content

   <source lang="sql">

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></source>