Forum Stats

  • 3,726,961 Users
  • 2,245,298 Discussions
  • 7,852,511 Comments

Discussions

Export CLOB

Anand Pandya
Anand Pandya Member Posts: 33 Red Ribbon
How to write clob to file.

Best Answer

  • Anand Pandya
    Anand Pandya Member Posts: 33 Red Ribbon
    edited September 2020 Accepted Answer

    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;

    /

Answers

  • Anand Pandya
    Anand Pandya Member Posts: 33 Red Ribbon
    edited September 2020 Accepted Answer

    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;

    /

Sign In or Register to comment.