Open the CLOB : CLOB « Large Objects « Oracle PL/SQL Tutorial






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.








34.4.CLOB
34.4.1.Creating Tables Containing CLOB Objects
34.4.2.Initialize CLOB column
34.4.3.Read clob data to varchar2 type variable
34.4.4.Read clob type data, DBMS_LOB.READ
34.4.5.Adding Content to a CLOB
34.4.6.Loading data to the CLOB by using BFILE
34.4.7.Performing basic string operations on CLOBs
34.4.8.Reading and Writing to a CLOB
34.4.9.Obtain Clob data pointer
34.4.10.close Clob data pointer
34.4.11.Open the CLOB
34.4.12.Insert into clob column
34.4.13.Update clob data
34.4.14.Compare date value after to_char() and trim()
34.4.15.Convert string to clob
34.4.16.Copy clob data