Oracle: Sql Developer: Error in printing a clob output parameter of a stored procedure - Stack Overflow

admin管理员组

文章数量:1362953

I have an Oracle stored procedure that has an output parameter as a clob. I am trying to execute this stored procedure and print that output(about 1.5 MB) to dbms output in SQL Developer. The SP runs fine but the output is not being printed. The error occurs in the output statement. How do I print the out parameter? Could it be that the buffer overflow happens in my output window?

Sample Stored Procedure:

create or replace procedure "sp_get_clob_data"
(
p1 varchar2, 
p2 varchar2,
p3 out clob
) as
begin
declare v_p1 varchar2(30);
declare v_p2 varchar2(30);
declare i_num integer;
begin
v_p1 := p1;
v_p2 := p2;
i_num := 3000;

FOR p_num in 1..i_num
LOOP

    p3:= p3 || CHAR(10) || TO_CHAR(p_num) || ": " || v_p1 || ' ' || v_p2;

end loop;

end;
end sp_get_clob_data;

Trying to execute the above sp in SQL Developer:

declare csv_output clob;
begin
    sp_get_data('TEST', 'LINE', csv_output);
    declare 
    vClob CLOB := csv_output;
    vPos number;
    vLen number;
    begin
        vLen := DBMS_LOB.GetLength(vClob);
        vPos := 1;
        while vPos < vLen 
        LOOP
            DBMS_OUTPUT.Put(DBMS_LOB.Substr(vCLOB, 200, vPos));
            vPos := vPos + 200;
        end loop;
        dbms_output.new_line;    
    
    end;    
end;

I am getting the error:

ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at line 13
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

本文标签： Oracle Sql Developer Error in printing a clob output parameter of a stored procedureStack Overflow