0

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.

M. Kemp
  • 107
  • 1
  • 1
  • 6
  • Works fine for me, as long as `set serveroutput on` has been run - clearly it has for the first to work... Where is the other text in your output coming from, and are you sure something isn't just turning output off in between? Why do you refer to more than 100 lines when neither block is doing that? – Alex Poole Aug 19 '19 at 18:59
  • Hi Alex. The other text is part of a small program I wrote that spools for me and outputs to a file that is appended all day long as I work. It records the time so I can reference work I did earlier in the day, day before, etc. The first block loops through a count of 98. Then there is a pre-loop line ("Before the loop") and a post-loop line ("After the loop"), for a total of 100 lines of output. The second block loops through a count of 99, for a total of 101 lines of output. – M. Kemp Aug 20 '19 at 20:44
  • Right, I wasn't counting the before/after. But what is happening between the loops? Where is serveroutput turned on before the first block, and what might be turning it off in between? It isn't clear how your program is interacting with SQL\*Plus. It seems likely the problem is somewhere in there though. – Alex Poole Aug 20 '19 at 21:47

1 Answers1

1

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.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
  • Hi Bob, thanks for the help. Nothing I try works. When I go to www.livesql.oracle.com, it works fine. Weird, very weird. – M. Kemp Aug 20 '19 at 20:47