CODEWITHSUNDEEP
oracle-databaseplsqlsqlplusdbms-output
M. Kemp
M. Kemp

Reputation: 131

PUT_LINE is limited to 100 lines

I cannot output more than 100 lines using dbms_output.put_line in an Oracle PL/SQL anonymous block.

Please see the sample code for the two examples: One example that works, one that doesn't work. Thank you.

# # 
### CODE SAMPLE #1
### THE FOLLOWING CODE SAMPLE WORKS FINE.
### 0718.002, Start time is: 08/19/2019 02:32:04pm

CTS@BPSMDSTS-C1>><<test_dbms_output>> begin
  2       dbms_output.put_line ('Before the loop');
  3       <<do_the_loop>> for i in 1 .. 98 loop
  4       -- <<do_the_loop>> for i in 1 .. 99 loop
  5            dbms_output.put_line ('i: [' || trim (to_char (i)) || ']');
  6            end loop do_the_loop;
  7       dbms_output.put_line ('After the loop');
  8       end test_dbms_output;
  9  /
Before the loop
i: [1]
i: [2]
i: [3]
i: [4]
i: [5]
... etc. deleted output for brevity, and due to redundancy ...
i: [94]
i: [95]
i: [96]
i: [97]
i: [98]
After the loop

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10

The time now: 08/19/2019 02:32:05pm
# # 
### CODE SAMPLE #2
### THE FOLLOWING CODE SAMPLE DOES NOT WORK. NOTHING IS OUTPUT.
### 0718.002, Start time is: 08/19/2019 02:32:33pm

CTS@BPSMDSTS-C1>><<test_dbms_output>> begin
  2       dbms_output.put_line ('Before the loop');
  3       -- <<do_the_loop>> for i in 1 .. 98 loop
  4       <<do_the_loop>> for i in 1 .. 99 loop
  5            dbms_output.put_line ('i: [' || trim (to_char (i)) || ']');
  6            end loop do_the_loop;
  7       dbms_output.put_line ('After the loop');
  8       end test_dbms_output;
  9  /

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01

The time now: 08/19/2019 02:32:33pm

In CODE SAMPLE #1, the expected results are achieved: One line of output is generated for each loop. In CODE SAMPLE #2, the results are unexpected. No output is generated.

Upvotes: 0

Views: 846

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Execute the following commands in SQL*Plus prior to running your script:

SET SERVEROUTPUT ON SIZE 250000
SET LINESIZE 500
SET TRIMSPOOL ON

This will give DBMS_OUTPUT a 250000 byte output buffer to work with, will change the max line size to 500 characters, and will trim output lines after a line ending character. You can change 250000 to 1000000 or even UNLIMITED if you're confident your program will terminate.

Upvotes: 1

Related Questions