SQLPlus removes trailing spaces in clob field on insert

Question

Hi,

I'm using SQL Plus 11.1.0.6.0 to run a script that performs batch inserts into an Oracle 10g db. The problem i noticed is when inserting some code string into a clob field that has some lines with trailing spaces such as:

....public void myMethod().... --trailing space here
....{
........int myVar = 1;
........ -- empty line with trailing spaces
........myVar+=1
....}

The string that gets inserted in the table looses those empty trailing spaces in the empty lines and becomes:

....public void myMethod() --trailing space is lost
....{
........int myVar = 1;
-- empty line without trailing spaces
........myVar+=1
....}

Although it makes no difference to the useful data, this is quite frustrating because it's causing the data to differ from the original and fails some tests.

All i could find was SET TRIMSPOOL/TRIMOUT OFF which doesn't change anything, does anyone have some other ideas?

Answer 1

Without posting your script it's hard to be sure, but you probably shouldn't be inserting text strings directly into a CLOB in SQL*Plus. If you were to use a PL/SQL proc that pulled in your text from a file and call the PL/SQL from SQL*Plus it should keep all the formatting.

But that may be an almighty PITA. But it's well documented in the O'Reilly PL/SQL texts.

Answer 2

In the end i solved it by a hack like this (considering original example):

declare
myLargeValue_2 clob;
begin
myLargeValue_2 := '....public void myMethod()'||'....
'||'....{
........int myVar = 1;
'||'........' -- empty line with trailing spaces

and so on

Basically concatenated explicitly all the whitespaces