|
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.
|