Home » SQL & PL/SQL » SQL & PL/SQL » Spooling data in pipe delimitted file (Oracle 9i R2)
Spooling data in pipe delimitted file [message #443439] Mon, 15 February 2010 15:14 Go to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Hi,
I am trying to Spool the data in pipe delimitted csv file but some of the records going on another line from the same records.
Currently some of the data going to next line as below oulined in the 2nd and 3rd line (in bold - |Home & Family) .
I have following sql setting in my spool file:
set linesize 4000 pagesize 0 trimspool on feedback off verify off echo off
set define off
spool Stk_hold_Sec_Tsk.csv


I tried increase linesize to 5000 but its not helping.
Ex.
PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager
[b]|House & street[/b]

PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User
[b]|House & street[/b]

PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street


Data should be like into the file:
PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager|House & street
PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User|House & street
PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street


I think it should be something with linesize or pagesize but not sure, could you please help me out?

Thank you!
Re: Spooling data in pipe delimitted file [message #443441 is a reply to message #443439] Mon, 15 February 2010 15:29 Go to previous messageGo to next message
joy_division
Messages: 4617
Registered: February 2005
Location: East Coast USA
Senior Member
Looks like you have carriage returns in your data.

How can someone debug an invisible SELECT statement?
Re: Spooling data in pipe delimitted file [message #443442 is a reply to message #443441] Mon, 15 February 2010 15:34 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

How you you be sure the problem is with the content of the file as opposed to just a presentation issue?

[Updated on: Mon, 15 February 2010 15:35]

Report message to a moderator

Re: Spooling data in pipe delimitted file [message #443443 is a reply to message #443441] Mon, 15 February 2010 15:41 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks joy for your qucik response.
How can i troubleshoot the criage return?

do you mean i need to post the select statement?

Regards,
Poratips
Re: Spooling data in pipe delimitted file [message #443444 is a reply to message #443442] Mon, 15 February 2010 15:44 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks Swan.

Result from V$version:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production



We have Unix - Sun Solaris system

Solaris 9 9/05 s9s_u8wos_05 SPARC


Thanks for your help!

Re: Spooling data in pipe delimitted file [message #443445 is a reply to message #443444] Mon, 15 February 2010 17:05 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>Solaris 9 9/05 s9s_u8wos_05 SPARC
Yea!

od -c <spool-file> | more

 od -c cal5.sql
0000000   s   e   l   e   c   t       t   r   u   n   c   (   (   d   t
0000020   e   -   t   o   _   d   a   t   e   (   '   &   B   E   G   _
0000040   D   A   T   E   '   ,   '   Y   Y   Y   Y   -   M   M   -   D
0000060   D   '   )   )   /   7   )   +   1       W   e   e   k       ,
0000100   d   t   e  \n   f   r   o   m           (   s   e   l   e   c
0000120   t       t   o   _   d   a   t   e   (   '   &   &   B   E   G
0000140   _   D   A   T   E   '   ,   '   Y   Y   Y   Y   -   M   M   -
0000160   D   D   '   )       +   l   e   v   e   l   -   1           d
0000200   t   e       f   r   o   m               d   u   a   l  \n    
0000220                           c   o   n   n   e   c   t       b   y
0000240       l   e   v   e   l       <   =       T   O   _   D   A   T
0000260   E   (   '   &   &   E   N   D   _   D   A   T   E   '   ,   '
0000300   Y   Y   Y   Y   -   M   M   -   D   D   '   )   +   1       -
0000320       t   o   _   d   a   t   e   (   '   &   &   B   E   G   _
0000340   D   A   T   E   '   ,   '   Y   Y   Y   Y   -   M   M   -   D
0000360   D   '   )   )  \n   /  \n


The "\n" are newline characters in the file.
This will allow you to see how many & where the newlines are.
Re: Spooling data in pipe delimitted file [message #443446 is a reply to message #443445] Mon, 15 February 2010 17:11 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks so much Swan.
I pulled data from the database and see that it has problem.
Is it any way i can resolved or avoid while doing extract?
My files are automated and runs nightly through calling from unix cron script and ftp to the other server.
But having some of the field data into table this was, how can i take care?

apprciated your help!



Re: Spooling data in pipe delimitted file [message #443448 is a reply to message #443446] Mon, 15 February 2010 17:16 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>I pulled data from the database and see that it has problem.

Too bad we can't see what the problem is. Sad

If the problem is "embedded" newline characters, what can/should be done to or with them?

So exactly what is the problem?
How can we propose any solution when we don't know what requirements that needs to be met?
Re: Spooling data in pipe delimitted file [message #443582 is a reply to message #443448] Tue, 16 February 2010 07:08 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Swan,
Thanks once again for your help.
I don't understand: "If the problem is "embedded" newline characters, what can/should be done to or with them?"

Is it sqlplus support /n like we do in sql*loader?

Thanks for your guidence!
Re: Spooling data in pipe delimitted file [message #443584 is a reply to message #443439] Tue, 16 February 2010 07:16 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've got a carriage returns in your data. They are causing a problem, so what do you want to happen to them? Presumably they are there for a reason.
If you just want to remove them simply use replace to repalce them with null in the select.
Re: Spooling data in pipe delimitted file [message #443608 is a reply to message #443584] Tue, 16 February 2010 08:22 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thanks, i tried with replace(field_name,to_char(chr(10)),' ') but its not helping.
I will try with chr(13)...

thanks,
poratips
Re: Spooling data in pipe delimitted file [message #443615 is a reply to message #443608] Tue, 16 February 2010 08:53 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
i tried with replace(field_name,to_char(chr(10)),' ') and also replace(field_name,to_char(chr(13)),' ')but its not helping.
Re: Spooling data in pipe delimitted file [message #443616 is a reply to message #443615] Tue, 16 February 2010 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

We can't see what you have or how Oracle responds

too bad for all concerned that CUT & PASTE are broken for you
Re: Spooling data in pipe delimitted file [message #443617 is a reply to message #443439] Tue, 16 February 2010 08:56 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
You may have to use both. Use the ascii function to work out what's there at the moment.
Re: Spooling data in pipe delimitted file [message #443618 is a reply to message #443617] Tue, 16 February 2010 09:05 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Sorry about that.
How can i use both for one field?

Regards,
poratips
Re: Spooling data in pipe delimitted file [message #443619 is a reply to message #443439] Tue, 16 February 2010 09:06 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use ascii to find out what's there first.
Re: Spooling data in pipe delimitted file [message #443620 is a reply to message #443616] Tue, 16 February 2010 09:11 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Swan,
Sorry about it, as i am getting same results so i just mentioned that its not helping me chr(10) or chr(13).

When i do the extract, some of the values going to next line:

PSS:Production Manager|ZS:PsS:PP:PROD_TCODES|P2S: PP - Production Transactions|House & street
PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager
[b]|House & street[/b]

PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User
[b]|House & street[/b]

PsS:Master Data (PDMs)|ZS:GEN:GENERAL_USER|GEN: General User|House & street


That "b]|House & street[/b]" going to next line

PSS:Production Manager|ZC:BW:PsS_RPT_MGR|BW PsS Reports Manager
[b]|House & street[/b]

PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User
[b]|House & street[/b]



Sorry about if i misunderstood your guidelines.

Regards,
poratips
Re: Spooling data in pipe delimitted file [message #443626 is a reply to message #443439] Tue, 16 February 2010 09:40 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
The solution will be simple if you just do what I suggest:
Select one row of one the columns that's giving you trouble.
Use substr to get the characters between the end of one line and the begining of the next.
Then use ascii or dump to find out exactly what characters are being used to denote end of line.
e.g.
SQL> set define off
SQL> WITH DATA AS (SELECT 'PsS:Production Manager|ZC:BW:PsS_RPT_USER|BW PsS Reports User
  2  |House & street' str FROM dual)
  3  SELECT ascii(substr(str, (instr(str, 'User',1,1) + 4), (instr(str, '|House',1,1) - (instr(str, 'User',1,1) + 4))))
  4  FROM DATA;

ASCII(SUBSTR(STR,(INSTR(STR,'USER',1,1)+4),(INSTR(STR,'|HOUSE',1,1)-(INSTR(STR,'
--------------------------------------------------------------------------------
                                                                              10

SQL> 
Re: Spooling data in pipe delimitted file [message #443640 is a reply to message #443626] Tue, 16 February 2010 12:12 Go to previous message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thank you!

I will try it out.
I have also tried with CHR(9) and waiting for the result.

Thanks for your help!


Previous Topic: UTL_TCP returning blank line?
Next Topic: how can i read blob data from database into my directory (merged by CM)
Goto Forum:
  


Current Time: Wed Sep 28 17:43:19 CDT 2016

Total time taken to generate the page: 0.16711 seconds