0

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.
3
  • 1
    It's not clear what you're asking as the error itself is crystal clear. You can't print more than 32767 bytes in a single line. So don't do that. Commented Apr 2 at 19:06
  • 1
    TO_CHAR(10) gives the string '10'. Did you mean to use CHR(10) to generate a newline? Commented Apr 2 at 19:08
  • @stdunbar: Thanks for your comment; I just added a new_line after dbms_output.put and I am able to see the clob output. Commented Apr 2 at 19:11

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.