OraFAQ Forum: Client Tools » Removing final carriage return from spooled output

Home » Other » Client Tools » Removing final carriage return from spooled output (11g on windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Removing final carriage return from spooled output [message #468301]

Thu, 29 July 2010 10:24

strauss_jon
Messages: 8
Registered: December 2007
Location: Whyteleafe

Junior Member

Hi there

I'm going potty with this, such a seemingly simple thing but can't get round it.

I spool an output using the following script and get a final carriage return at the end. This fails our SSIS bulk insert task. How do i get rid of this carriage return please? Sample below:

SET SPACE 0
SET HEAD OFF
SET FEEDBACK OFF
SET TERMOUT OFF
set echo off
set newpage 0
set space 0
set pagesize 0
set feedback off
set trimspool on
set heading off
set linesize 15000
SET TRIMOUT ON
SPOOL "outputFile.spl"
SELECT STATEMENT HERE;
spool off
exit

Thanks in advance! Jon

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468305 is a reply to message #468301]

Thu, 29 July 2010 10:51

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You cannot change SQL*Plus so either handle the file to remove the last character, either modify your bulk insert task to handle this one.

Regards
Michel

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468309 is a reply to message #468301]

Thu, 29 July 2010 11:35

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

strauss_jon wrote on Thu, 29 July 2010 11:24

... Blah, blah, blah ...
... How do i get rid of this carriage return please?

If you get rid of the carriage return, how will you distinguish each record (record terminator character's)? The spool file you are creating may have VARIABLE length records (depending on our query) using the carriage return to separate (terminate) them "SET TRIMSPOOL ON" did that.

Here are your options:

1) Generate FIXED length records "SET LIN {nnn} TRIMS OFF" and include the CR as a one or two character field (depending on WinDoze or Unix) at the end of the record in the SSIS definition.

2) Use UTL_FILE package to create the file.

[Updated on: Thu, 29 July 2010 11:51] by Moderator

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468312 is a reply to message #468309]

Thu, 29 July 2010 12:03

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

He only wants to get rid of the CR at the end, not at the end of each line.

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468327 is a reply to message #468312]

Thu, 29 July 2010 13:05

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

cookiemonster wrote on Thu, 29 July 2010 13:03

He only wants to get rid of the CR at the end, not at the end of each line.

Oooh...Kay,

Maybe one (or more) of these settings would work:

SET EMB ON
SET NEWP NONE
SET RECSEP OFF
-- Etc...

[Updated on: Thu, 29 July 2010 13:09] by Moderator

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468328 is a reply to message #468327]

Thu, 29 July 2010 13:09

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No none unless you find one. Razz

Regards
Michel

[Updated on: Thu, 29 July 2010 13:09]

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468343 is a reply to message #468328]

Thu, 29 July 2010 15:18

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Unless the OP's server is WinDoze, I tested in Unix and get no "empty" rows (CR) at the end of the file.

PS: Besides, I said "Maybe"
.

[Updated on: Fri, 30 July 2010 01:49] by Moderator

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468438 is a reply to message #468343]

Fri, 30 July 2010 01:49

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Unless the OP's server is WinDoze

Title: "11g on windows 7".

Quote:

PS: Besides, I said "Maybe"

So was your post of any help? I summarize: "maybe there is a parameter to solve your problem, maybe there is none". Razz

Regards
Michel

[Updated on: Fri, 30 July 2010 01:50]

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468471 is a reply to message #468438]

Fri, 30 July 2010 02:51

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468474 is a reply to message #468301]

Fri, 30 July 2010 03:25

strauss_jon
Messages: 8
Registered: December 2007
Location: Whyteleafe

Junior Member

Nah, don't think it can be done easily without scripting the action. Can't believe SSIS's load component can't handle this. Anyway, there's alternatives in SSIS so i'll try others. Was just hoping there would be an Oracle set option (or something similar) which could sort this out.

Thanks for your posts anyway. Cheers, Jon

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468475 is a reply to message #468301]

Fri, 30 July 2010 03:26

strauss_jon
Messages: 8
Registered: December 2007
Location: Whyteleafe

Junior Member

Btw, tried these to no avail...
SET EMB ON
SET NEWP NONE
SET RECSEP OF

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468527 is a reply to message #468475]

Fri, 30 July 2010 09:54

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I don't have windows around and cannot test it.
To recap, you just want to remove the last line in spooled output
or
Last character in last line?

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468550 is a reply to message #468343]

Fri, 30 July 2010 11:15

Frank
Messages: 7901
Registered: March 2000

Senior Member

LKBrwn_DBA wrote on Thu, 29 July 2010 22:18

Unless the OP's server is WinDoze, I tested in Unix and get no "empty" rows (CR) at the end of the file.

Unix' line terminators are LF, not CR.
If you feel you should display your supremacy by using "windoze" instead of windows, at least make sure you got your unix right.

Report message to a moderator

Re: Removing final carriage return from spooled output [message #468961 is a reply to message #468550]

Mon, 02 August 2010 12:01

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

Frank wrote on Fri, 30 July 2010 12:15

Unix' line terminators are LF, not CR.
If you feel you should display your supremacy by using "windoze" instead of windows, at least make sure you got your unix right.

Yep, you got me there with the CR part.
On the other hand, It's not "supremacy", but the undeniable fact is that Microsoft OS take a very long time to load (compared to other OS's) and therefore one may "doze off" while waiting...therefore "WinDoze" is an appropriate moniker for this OS.

[Updated on: Wed, 04 August 2010 06:40] by Moderator

Report message to a moderator

Previous Topic:	Unable to connect to Oracle from Toad
Next Topic:	column heading

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 10:11:54 CDT 2024