End of Line Character on last column with LOB2Table (BLOB)

[Paul Ungaretti]

I've noticed some odd behaviour with the last column imported. I
use a MAC and Save my xls as "Windows Commas Seperated (*.csv)".
Once imported to a table, when I query the table it appears fine; however, there appears to be some typ eof special charater appended to the end of the last column. I suspect it is a End of Line charater.

For example, if the colmn appears to have the value 'MICHAEL' then the following query will not work where column_last = 'MICHAEL'; however, substr(column_last,1,7) = 'MICHAEL' does work. Not a big deal for viewing text; however, when using queries that entail select * from table MINUS select * from table, the extra "special charater" at the end is throwing off my expected results. I'd be happy to troubleshoot further, but I don't know what the special charater is within the DB. To workaround the issue I've just been addidng an extra column of data and not using the last column, but I thought it best to report this observation.

[Michael]

Hi Paul,

I guess that when you create the csv file with Excel with the setting "Windows Commas Seperated (.csv)" that the typical line separator (or End Of Line, EOL) for DOS/Windows will be used, that is CR+LF. (For Unix it's only LF.)
For details, take a look at https://en.wikipedia.org/wiki/Newline

Now in Oracle CR is chr(13) and LF is chr(10).
So, if you specify only chr(10), i.e. LF, as your line/row separator in your lob2table call then the CR, i.e. chr(13), will be left as a part of the last column of your row.
And this matches exactly the behaviour that you observe (CR is a non-printable character).

To correct this you just have to specify the line separator as chr(13) || chr(10).
For example:

select d.deptno, d.dname,
       t.row_no, t.column1, t.column2, t.column3, t.column4
from   dept d
       cross join table(
         lob2table.separatedcolumns(
           d.myclob,            /* the data LOB */
           chr(13) || chr(10),  /* row separator */
           ',',                 /* column separator */
           '"'                  /* delimiter (optional) */
         )
       ) t
where  d.deptno in(10, 40)

Hope this helps!
(Your workaround with the extra columns should then be unnecessary.)

Best,
Michael