Export CLOB

Anand Pandya

How to write clob to file.

Anand Pandya

CREATE OR REPLACE PROCEDURE clob_to_file (p_clob      IN  CLOB,

                                          p_dir       IN  VARCHAR2,

                                          p_filename  IN  VARCHAR2)

AS

  l_file    UTL_FILE.FILE_TYPE;

  l_buffer  VARCHAR2(32767);

  l_amount  BINARY_INTEGER := 32000;

  l_pos     INTEGER := 1;

  l_new_pos iNTEGER :=1;

  l_count number :=1;

  l_total_length number:=0;

BEGIN

  l_total_length:= DBMS_LOB.getlength(p_clob);

  l_file := UTL_FILE.fopen(p_dir, p_filename, 'w',32767);

  LOOP

    DBMS_LOB.read (p_clob, l_amount, l_new_pos, l_buffer);

    l_pos:=INSTR(l_buffer,'

',-1);

    if l_pos = 0 then

        L_POS:=l_total_length;

    end if;

    UTL_FILE.put(l_file, SUBSTR(l_buffer,1,L_POS));

    utl_file.fflush(l_file);

    l_new_pos:=l_new_pos+L_POS;

    l_count:=l_count+1;

    IF l_pos = l_total_length THEN

    EXIT;

    END IF;

  END LOOP;

  IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    -- Expected end.

    IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

  WHEN OTHERS THEN

    IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

    RAISE;

END clob_to_file;

/

Anand Pandya

CREATE OR REPLACE PROCEDURE clob_to_file (p_clob      IN  CLOB,

                                          p_dir       IN  VARCHAR2,

                                          p_filename  IN  VARCHAR2)

AS

  l_file    UTL_FILE.FILE_TYPE;

  l_buffer  VARCHAR2(32767);

  l_amount  BINARY_INTEGER := 32000;

  l_pos     INTEGER := 1;

  l_new_pos iNTEGER :=1;

  l_count number :=1;

  l_total_length number:=0;

BEGIN

  l_total_length:= DBMS_LOB.getlength(p_clob);

  l_file := UTL_FILE.fopen(p_dir, p_filename, 'w',32767);

  LOOP

    DBMS_LOB.read (p_clob, l_amount, l_new_pos, l_buffer);

    l_pos:=INSTR(l_buffer,'

',-1);

    if l_pos = 0 then

        L_POS:=l_total_length;

    end if;

    UTL_FILE.put(l_file, SUBSTR(l_buffer,1,L_POS));

    utl_file.fflush(l_file);

    l_new_pos:=l_new_pos+L_POS;

    l_count:=l_count+1;

    IF l_pos = l_total_length THEN

    EXIT;

    END IF;

  END LOOP;

  IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    -- Expected end.

    IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

  WHEN OTHERS THEN

    IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

    RAISE;

END clob_to_file;

/