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

Open the output file for writing

   <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      DBMS_LOB.OPEN (clob_pointer,DBMS_LOB.LOB_READONLY);
15    end if;
16
17    fp :=UTL_FILE.FOPEN("BFILE_DIR","output.dat","w");
18
19    LOOP
20      BEGIN
21        DBMS_LOB.READ (clob_pointer,Amount,Position,v_Buf);
22
23        UTL_FILE.PUT_LINE(fp,v_Buf,TRUE);
24
25        Position :=Position +Amount;
26
27      EXCEPTION
28
29        WHEN NO_DATA_FOUND THEN
30
31          EXIT;
32
33      END;
34
35    END LOOP;
36
37    UTL_FILE.FCLOSE(fp);
38
39    DBMS_LOB.CLOSE (clob_pointer);
40  END;
41  /

SQL> SQL> drop table myClob; Table dropped.</source>

Save lines to a file

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE put_line

 2     (file_in IN UTL_FILE.FILE_TYPE,
 3      line_in IN VARCHAR2,
 4      
 5      max_line_size_in IN INTEGER := 1023)
 6  IS
 7     v_line VARCHAR2(32767);
 8     v_linelen CONSTANT INTEGER := LENGTH (line);
 9     v_start INTEGER := 1;
10     v_max CONSTANT INTEGER := max_line_size_in - 1;
11  BEGIN
12     LOOP
13        EXIT WHEN v_start > v_linelen OR v_linelen IS NULL;
14        v_line := SUBSTR (line_in, v_start, v_max);
15        IF v_start + v_max < v_linelen
16        THEN
17           v_line := v_line || "-";
18        END IF;
19        UTL_FILE.PUT_LINE (file_in, v_line);
20        v_start := v_start + v_max;
21     END LOOP;
22  END;
23  /</source>
   
  

Save string to a file with UTL_FILE.PUTF

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE PROCEDURE saveStringToFile(fid IN UTL_FILE.FILE_TYPE,it_is IN VARCHAR2)

 2  IS
 3  BEGIN
 4     UTL_FILE.PUTF (fid, "string",it_is);
 5  END;
 6  /

SQL> SQL></source>

Write text to a file with UTL_FILE package

   <source lang="sql">

SQL> SQL> DECLARE

 2     fid UTL_FILE.FILE_TYPE;
 3     f VARCHAR(200);
 4     v VARCHAR2(32767);
 5     topdir VARCHAR2(100);
 6
 7     PROCEDURE recNgo (str IN VARCHAR2)
 8     IS
 9     BEGIN
10        DBMS_OUTPUT.PUT_LINE ("UTL_FILE error " || str || " in " || f);
11        UTL_FILE.FCLOSE (fid);
12     END;
13
14     PROCEDURE writeit
15     IS
16     BEGIN
17         f := "c:\a";
18         fid := UTL_FILE.FOPEN (f, "temp.txt", "W");
19         UTL_FILE.PUT_LINE (fid, v);
20         UTL_FILE.FCLOSE (fid);
21        DBMS_OUTPUT.PUT_LINE ("Successful write to " || f);
22      EXCEPTION
23         WHEN UTL_FILE.INVALID_PATH THEN recNgo ("invalid_path");
24         WHEN UTL_FILE.INVALID_MODE THEN recNgo ("invalid_mode");
25         WHEN UTL_FILE.INVALID_FILEHANDLE THEN recNgo ("invalid_filehandle");
26         WHEN UTL_FILE.INVALID_OPERATION THEN recNgo ("invalid_operation");
27         WHEN UTL_FILE.READ_ERROR THEN recNgo ("read_error");
28         WHEN UTL_FILE.WRITE_ERROR THEN recNgo ("write_error");
29         WHEN UTL_FILE.INTERNAL_ERROR THEN recNgo ("internal_error");
30     END;
31  BEGIN
32     writeit;
33  END;
34  /

SQL></source>