Read from the CLOB in chunks of 1000 characters and write to the output file : File Read with UTL_FILE « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. File Read with UTL_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;