I'm using SQL developer and trying to spool out a fair amount of data. The script stops part way through as the buffer overflow is exceeded.
I understand this is because the Script Output cannot handle this amount of data but the solution to this SET TERMOUT OFF does not work.
I've provided an example below to illustrate this command not working
SET TERMOUT OFF
/
DECLARE
CURSOR C1 IS
select distinct '1' NUMBERR from dual;
BEGIN
for c1_rec in c1 loop
DBMS_OUTPUT.PUT_LINE(C1_REC.NUMBERR);
END LOOP;
end;
Output:
1
PL/SQL procedure successfully completed.
As you can see, the '1' is still output to the Script Output indicating the SET TERMOUT isn't working
From the SQL*Plus documentation, which largely applies to SQL Developer too:
TERMOUT OFF does not affect output from commands you enter interactively
If you had set termout off in a script file and ran it with @<script> then it would suppress out the output; but it would still be trying to to write the same data if you were spooling.
If you're seeing ORU-10027: buffer overflow then you're trying to solve the wrong problem anyway; even spooling, you'd hit the same error - it would just appear in the file and not in the script output window.
The buffer relates to dbms_output itself:
set serveroutput on size 2000
begin
dbms_output.put_line(dbms_random.string('a', 2001));
end;
/
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
...
To avoid that error set the buffer high enough for the amount of output you expect, or to its maximum for the Oracle and client version:
set serveroutput on size unlimited
but as you've seen that still caps you to 1000000 bytes in SQL Developer, up to version 18.2 anyway.
Even with termout off and spooling to a file, you'll still get
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
reported in the file.
If you need more output than that then you could write the output to a temporary table and then query it after your PL/SQL block, perhaps. Or you could use utl_file to write to a file (which has to be on the server, not the client, which is a downside) instead of spooling. Or you could use SQL*Plus, which doesn't impose that limitation.