Thanks for the question, Steve.
Asked: December 30, 2015 - 3:03 am UTC
Last updated: December 30, 2015 - 5:08 am UTC
Version: Oracle 11
Viewed 1000+ times
You Asked
I have Oracle table data that has a varchar2 column where some rows contain CR and LF chars in the data.
In Linux, I want to use sqlplus to spool this data to a pipe delimited file and then use this file to load the data into Netezza.
When I spool the data, I end up with multiple lines in the file for some source rows.
How do I keep the data in its original form in the spool file (I want to retain the embedded CR and LF chars).
create table foo (c1 varchar2(10));
insert into foo values ('a' || chr(10) || 'b' || chr(13) || 'c');
select * from foo; -- when i run this in sqlplus, I want have one line in my file.
and Connor said...
The *definition* of 'line' in a file is where you encounter the carriage return/line feeds. That's what a line is, eg
$>cat c:\temp\demo.txt
Hello
There
$>od -c c:\temp\demo.txt
0000000 H e l l o \r \n T h e r e
Check the Netezza import facilities to see if they can handle escape characters (like the '\r' etc above). If so, then you can use the REPLACE command in your SQL to escape the chars whilst you spool, eg
replace(str,chr(10),'\r')
Hope this helps.
Is this answer out of date? If it is, please let us know via a Comment