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 |
|
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 #660904 is a reply to message #660902] |
Tue, 28 February 2017 14:39 |
|
BlackSwan
Messages: 26766 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 #660929 is a reply to message #660927] |
Wed, 01 March 2017 12:08 |
|
Michel Cadot
Messages: 68625 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.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 17:33:44 CDT 2024
|