Home » SQL & PL/SQL » Client Tools » Removing final carriage return from spooled output (11g on windows 7)
Removing final carriage return from spooled output [message #468301] Thu, 29 July 2010 10:24 Go to next message
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
Re: Removing final carriage return from spooled output [message #468305 is a reply to message #468301] Thu, 29 July 2010 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 59062
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
Re: Removing final carriage return from spooled output [message #468309 is a reply to message #468301] Thu, 29 July 2010 11:35 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 432
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 Go to previous messageGo to next message
cookiemonster
Messages: 10959
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.
Re: Removing final carriage return from spooled output [message #468327 is a reply to message #468312] Thu, 29 July 2010 13:05 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 432
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59062
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 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 432
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 Go to previous messageGo to next message
Michel Cadot
Messages: 59062
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 Go to previous messageGo to next message
ramoradba
Messages: 2452
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Laughing
Re: Removing final carriage return from spooled output [message #468474 is a reply to message #468301] Fri, 30 July 2010 03:25 Go to previous messageGo to next message
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
Re: Removing final carriage return from spooled output [message #468475 is a reply to message #468301] Fri, 30 July 2010 03:26 Go to previous messageGo to next message
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
Re: Removing final carriage return from spooled output [message #468527 is a reply to message #468475] Fri, 30 July 2010 09:54 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10625
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?

Re: Removing final carriage return from spooled output [message #468550 is a reply to message #468343] Fri, 30 July 2010 11:15 Go to previous messageGo to next message
Frank
Messages: 7877
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.
Re: Removing final carriage return from spooled output [message #468961 is a reply to message #468550] Mon, 02 August 2010 12:01 Go to previous message
LKBrwn_DBA
Messages: 432
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:
  


Current Time: Sun Sep 14 21:19:39 CDT 2014

Total time taken to generate the page: 0.06593 seconds