Blog

In the following example the procedure create_csv creates a CLOB, which is then printed out.

CLOB can be printed out, either simply, using DBMS_OUTPUT.PUT_LINE, or with the procedure print_clob_out, which is demonstrated below.

The procedure print_clob_out is recommended for big CLOBs.

declare
    
    csv_file clob;

    procedure create_csv (p_clob out clob) is
        L_CLOB  clob;
        P_Text_I  varchar2(500);
    Begin
        dbms_lob.createtemporary(L_CLOB, false, 10 );    

        P_Text_I := 'A;B;' || chr(10);
        Dbms_Lob.Write(Lob_Loc => L_CLOB, 
			Amount => Length(P_Text_I), 
			Offset => DBMS_LOB.GETLENGTH(L_CLOB) + 1, 
			Buffer => P_Text_I);

        P_Text_I := '1;2;' || chr(10);
        Dbms_Lob.Write(Lob_Loc => L_CLOB, 
			Amount => Length(P_Text_I), 
			Offset => DBMS_LOB.GETLENGTH(L_CLOB) + 1, 
			Buffer => P_Text_I);

        P_Text_I := '3;4;' || chr(10);
        Dbms_Lob.Write(Lob_Loc => L_CLOB, 
			Amount => Length(P_Text_I), 
			Offset => DBMS_LOB.GETLENGTH(L_CLOB) + 1, 
			Buffer => P_Text_I);
        
        p_clob := l_clob;
    end;            
    
  PROCEDURE print_clob_out(
    p_result      IN   CLOB,
    p_no_header        VARCHAR2 DEFAULT 'N')
  IS
    l_lf_pos    NUMBER          := 1;
    l_len       NUMBER;
    l_line      VARCHAR2(255 CHAR);
    i           int := 1;
  BEGIN

    l_len := DBMS_LOB.getlength(p_result);
    
    WHILE(l_lf_pos < l_len) LOOP

        l_line := DBMS_LOB.SUBSTR(p_result, 254, l_lf_pos);    
        dbms_output.put(l_line);
        l_lf_pos := l_lf_pos + 254;
        i := i + 1;     
    END LOOP;
    dbms_output.put_line('');
  END print_clob_out;    
begin
   create_csv (p_clob => csv_file);
   --dbms_output.put_line(csv_file);
   print_clob_out(csv_file);
end;

Output (can be opened as .csv file in excel):

A;B;

1;2;

3;4;