Home » SQL & PL/SQL » SQL & PL/SQL » Carriage return in column data for spool (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Carriage return in column data for spool [message #660902] Tue, 28 February 2017 14:22 Go to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
Hi Everyone,

I am trying to spool the contents of a table having a column which has a carriage return in the data. Below is the setuup:

  create table "test_spool" 
   (	"acct_id" number(12,0), 
	"id" number(9,0), 
	"re_dt" date, 
	"comment" number(12,0), 
	"i_cmnt" number(12,0), 
	"t_comnt" char(255 char), 
	"lst_comnt" char(1 char), 
	"user" char(12 char), 
	"upd_row" date
   ) 
Insert into TEST_SPOOL (acct,id,re_dt,comment,i_cmnt,T_COMNT,lst_comnt,user,upd_row) values 
(806098,20613,to_date('09-DEC-10 12:31:33 PM','DD-MON-RR HH12:MI:SS AM'),1,1,
'Real Estate review date 10-26-09.  Recommendation: retain.','Y','BC67        ',
to_date('09-DEC-10 12:34:38 PM','DD-MON-RR HH12:MI:SS AM'));
Insert into TEST_SPOOL (acct,id,re_dt,comment,i_cmnt,T_COMNT,lst_comnt,user,upd_row) values 
(806098,20613,to_date('07-JUL-09 05:05:25 PM','DD-MON-RR HH12:MI:SS AM'),1,2,
'Primary Residence
Court approved.','Y','BC67        ',to_date('14-OCT-10 03:52:24 PM','DD-MON-RR HH12:MI:SS AM'));
Insert into TEST_SPOOL (acct,id,re_dt,comment,i_cmnt,T_COMNT,lst_comnt,user,upd_row) values 
(806098,20613,to_date('07-JUL-09 05:05:25 PM','DD-MON-RR HH12:MI:SS AM'),3,1,
'Insert "Court-Approved" in the comments box.','Y','EB72        ',to_date('13-JUL-09 11:53:27 AM','DD-MON-RR HH12:MI:SS AM'));
Insert into TEST_SPOOL (acct,id,re_dt,comment,i_cmnt,T_COMNT,lst_comnt,user,upd_row) values 
(806098,20613,to_date('07-JUL-09 05:05:25 PM','DD-MON-RR HH12:MI:SS AM'),1,1,
'Primary Residence','N','DM80        ',to_date('07-JUL-09 05:05:33 PM','DD-MON-RR HH12:MI:SS AM'));

As you can see the second row above has a carriage return in the column value for t_comnt. When spooling to a file I get the below for the second row in the output file:
806098,20613,07-JUL-09,1,2,Primary Residence,Y,BC67,14-OCT-10
,,,,,Court approved.,,,
The t_comnt gets split into two lines which is incorrect. Is there a way to instruct oracle to handle these new line scenarios in column values?

Just to add, I am using the below format parameters:
set head off
set echo off
set verify off
set feedback off
set linesize 32767
set pages 0
SET AUTOPRINT ON
set trimspool on
set trimout on
set termout off
SET RECSEP OFF

[Updated on: Tue, 28 February 2017 14:49] by Moderator

Report message to a moderator

Re: Carriage return in column data for spool [message #660903 is a reply to message #660902] Tue, 28 February 2017 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If there is a new line in the data then Oracle will give it back you; if it didn't it it would not do his job properly.
Now you just have to tell it what you want in place of new line in your data and use REPLACE.

Re: Carriage return in column data for spool [message #660904 is a reply to message #660902] Tue, 28 February 2017 14:39 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
I don't believe you.
SQL> create table "test_spool" 
   (	"acct_id" number(12,0), 
	"id" number(9,0), 
	"re_dt" date, 
	"comment" number(12,0), 
	"i_cmnt" number(12,0), 
	"t_comnt" char(255 char), 
	"lst_comnt" char(1 char), 
	"user" char(12 char), 
	"upd_row" date
   )   2    3    4    5    6    7    8    9   10   11  
 12  /

Table created.

SQL> Insert into TEST_SPOOL (acct,id,re_dt,comment,i_cmnt,T_COMNT,lst_comnt,user,upd_row) values 
(806098,20613,to_date('09-DEC-10 12:31:33 PM','DD-MON-RR HH12:MI:SS AM'),1,1,
'Real Estate review date 10-26-09.  Recommendation: retain.','Y','BC67        ',
to_date('09-DEC-10 12:34:38 PM','DD-MON-RR HH12:MI:SS AM'));  2    3  
Insert into TEST_SPOOL (acct,id,re_dt,comment,i_cmnt,T_COMNT,lst_comnt,user,upd_row) values
                                      *
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification


[Updated on: Tue, 28 February 2017 14:46] by Moderator

Report message to a moderator

Re: Carriage return in column data for spool [message #660905 is a reply to message #660902] Tue, 28 February 2017 14:43 Go to previous messageGo to next message
John Watson
Messages: 7620
Registered: January 2010
Location: Global Village
Senior Member
Your identifierss are lower case enclosed in double quotes. That is unusual in the Oracle world.
Re: Carriage return in column data for spool [message #660906 is a reply to message #660903] Tue, 28 February 2017 14:44 Go to previous messageGo to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
Thanks Michel, I can indeed use replace(t_comnt,chr(10),'<CARRG>')and then maybe replace <CARRG> in the output file with a carriage return to get the file data in sync with what we have in the file. But, these scenario is occurring in hundreds of file in my application and I am trying to create a generic spooler utlitity for all.
I think Oracle treats the carriage return in the data as a line separator and hence I get an extra line with the erroneous data. Can you suggest if I can change the default new line character as line separator to maybe a string like '<EOL>', will it be hlpful. If yes, then I am still stuck to change the default behaviour. Basically treat the data as a single huge line where each row is separated by a <EOL> instead of a new line feed
Re: Carriage return in column data for spool [message #660907 is a reply to message #660905] Tue, 28 February 2017 14:46 Go to previous messageGo to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
LOlzz...thats actually when you pull out the DDL from Oracle developer. And it was in upper case, I read somewhere in forum standards that no uppercase Razz
Re: Carriage return in column data for spool [message #660908 is a reply to message #660906] Tue, 28 February 2017 14:51 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I am trying to create a generic spooler utlitity for all.
So do not use SQL*Plus (or similar which ends each returned row with a newline character), if you write your own tool then you can have the eol character(s) you want.

[Updated on: Tue, 28 February 2017 14:52]

Report message to a moderator

Re: Carriage return in column data for spool [message #660909 is a reply to message #660908] Tue, 28 February 2017 15:06 Go to previous messageGo to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
Sheer Injustice by Oracle!!! Wish it could have provided something to change default row terminators. Anyways. Thanks All.
Re: Carriage return in column data for spool [message #660911 is a reply to message #660909] Tue, 28 February 2017 17:19 Go to previous messageGo to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
Just to update, using UTL_FILE instead of the spool in sqlplus solved the issue. the line is now sent to output file as it is:
Re: Carriage return in column data for spool [message #660913 is a reply to message #660911] Wed, 01 March 2017 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

is?

Re: Carriage return in column data for spool [message #660921 is a reply to message #660909] Wed, 01 March 2017 06:38 Go to previous messageGo to next message
EdStevens
Messages: 985
Registered: September 2013
Senior Member
abhi_orcl wrote on Tue, 28 February 2017 15:06
Sheer Injustice by Oracle!!! Wish it could have provided something to change default row terminators. Anyways. Thanks All.
Oracle has nothing to do with it. It is simply sending the data stream to the the standard output device and/or writing it to a file. It's not oracle's fault or concern that the data stream includes the standard ascii code for "newline". How that ascii code is actually interpreted and acted upon is up to whatever program (like notepad) that is reading it and rendering it on screen or printer.

You could just as easily have another use case where you WANT that newline to take effect, where you deliberately included it in the data for that very purpose. How do you expect Oracle to know?
Re: Carriage return in column data for spool [message #660927 is a reply to message #660921] Wed, 01 March 2017 10:09 Go to previous messageGo to next message
abhi_orcl
Messages: 40
Registered: December 2016
Member
@EdStevens : I am not asking Oracle to understand my file and do the magic. I was expecting Oracle to provide a workaround which I can use to change my file row separator. For instance, BCP utility in Sybase is a similar one which also acts as a spooler but has provided an option to enable users to select their choice of row separator while creating output files with DB table data.
Please note: Dear forum members/moderators, instead of bashing and pouncing aggressively to show off your immense knowledge, respect people's post who might be a bit amateur in Oracle technologies. If you can't help and feel the post is silly, please feel free to just shut your mouth or mark the post as irrelevant.
Re: Carriage return in column data for spool [message #660929 is a reply to message #660927] Wed, 01 March 2017 12:08 Go to previous message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
BCP utility in Sybase is a similar one
Similar to what? You didn't tell us how you "spool" your data and what tool you use.
Note that Oracle returns you rows, your client tool adds the new line character, this is what Ed wanted to explain you.

Sybase BCP is an export/import utility; Oracle uses DataPump for this, this is not the purpose of SQL*Plus or the like which are SQL clients and not export/import utilities.
Maybe you are not using the correct tool.

Previous Topic: Today I came across different space issue in 12c DB (merged 2)
Next Topic: How to query and not include weekend or Bankholidays
Goto Forum:
  


Current Time: Mon Oct 22 00:24:21 CDT 2018