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

Open the output file for writing

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.

Save lines to a file

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  /

Save string to a file with UTL_FILE.PUTF

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>

Write text to a file with UTL_FILE package

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>