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 BEGIN
23
24 DBMS_LOB.READ (clob_pointer, Amount, Position, v_Buf);
25
26 UTL_FILE.PUT_LINE(fp, v_Buf, TRUE);
27
28 Position :=Position + Amount;
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 EXIT;
32 END;
33 END LOOP;
34
35 UTL_FILE.FCLOSE(fp);
36
37 DBMS_LOB.CLOSE (clob_pointer);
38 END;
39 /
SQL>
SQL> drop table myClob;
Table dropped.