Just when you thought life became easier with emerging technologies in 2003 you come across these little bugging problems of SQL*Plus, the worst developer tool on earth…

Leading blanks are stripped by default when you do an DBMS_OUTPUT.Put_Line – not the trailing of course – those are more important in code-generators – they make the files really big (assuming a LINESIZE of 200).

Well, fortunately there's a way to fix the output of the following sample:

this is the script test.sql:

</p>

	<p><span class="caps">BEGIN</span></p>

	<p>  dbms_output.put_line('    <span class="caps">TEST</span> with 4 spaces');</p>

	<p>  dbms_output.put_line('      <span class="caps">TEST</span> with 2 tabs');</p>

	<p>END;</p>

	<p>/</p>

	<p>

use the format wrap to avoid trimming the leading blanks and you are done :-)

Explanation:

Only the default WORD_WRAPPED causes the trim to occur. The other options WRAPPED and TRUNACTED don't trim and hence don't suppress blank output (or leading spaces).

When generating code in general I use the following sqlplus settings:

</p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">NEWPAGE</span>      0</span></p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">SPACE</span>        4</span></p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">PAGESIZE</span>     0</span></p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">TERMOUT</span>     ON</span></p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">FEEDBACK</span>   OFF</span></p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">HEADING</span>    OFF</span></p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">VERIFY</span>     OFF</span></p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">TIME</span>       OFF</span></p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">TIMING</span>     OFF</span></p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">ECHO</span>       OFF</span></p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">PAUSE</span>      OFF</span></p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">RECSEPCHAR</span> </span>' '</p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">SERVEROUT</span> ON</span> size 1000000 format wrap                               </p>

	<p><span class="caps">SET</span> <span class="caps"><span class="caps">LINESIZE</span>   200</span></p>

	<p>