| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Sql PLus - extra linefeed
Setting wrap off caused the first line of the header to be printed and
skipped everything after the first chr(10) (header lines 2 and 3 and data
line 1), even when I bumped the linesize to 2000. This is just frustrating.
I wish I was back on a unix box so I could just do a grep -v and get rid of
the extra lines.
Thanks,
Ken Naim
-----Original Message-----
From: Jonathan Gennick [mailto:jonathan_at_gennick.com]
Sent: Wednesday, March 29, 2006 10:16 PM
To: Ken Naim
Cc: oracle-l_at_freelists.org
Subject: Re: Sql PLus - extra linefeed
Hello Ken,
Do any of the columns in your output wrap? A long text value that wraps to two lines will cause SQL*Plus to skip a line. If that's the case, you might try SET WRAP OFF.
Best regards,
Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
Wednesday, March 29, 2006, 7:31:47 PM, Ken Naim (kennaim_at_gmail.com) wrote:
KN> I am having a issue with the spooling of a query to file where sql plus
is
KN> inserting an extra linefeed and I cannot determine the cause. The file
will
KN> be imported into a 3rd party app that I do not have any control over. I KN> might be missing the obvious as I have working on it all afternoon so I KN> appreciate any assistance. I am running on oracle 10gR1 on windows. Sqlplus
KN> The query is slightly complicated as it has to write a 3 line header
prior
KN> to each of 14 sections. Each section will contain about 3000 records.
Each
KN> line is 490 characters long so I will truncate the lines to illustrate
the
KN> problem. After each of the fourteen 3 line header records, 1 data record
is
KN> written followed by a blank line (issue) followed by all the other data
KN> record for the section. The application is crapping out on the blank
lines
KN> breaking the entire import.
KN> ~~~~~~~~00IFe50k 05303199204053030003... Header 1a KN> ~~~~~~~~01All Items by Quarter... Header 1b KN> ~~~~~~~~02Fifty Thousand... Header 1c KN> ~~~~~~~~08001001+00000000000+00000000000... Data 1 KN> ~~~~~~~~08001002+00000000000+00000000000... Data 2 KN> ~~~~~~~~08001003+00000000000+00000000000... Data 3 KN> ~~~~~~~~08001004+00000000000+00000000000... Data 4 KN> ~~~~~~~~00IFe50k 05303199204053030003... Header 2a KN> ~~~~~~~~01All Items by Quarter... Header 2b KN> ~~~~~~~~02Five Thousand... Header 2cKN> ~~~~~~~~08001001+00000000000+00000000000... Data 3001
KN> ~~~~~~~~08001002+00000000000+00000000000... Data 3002 KN> ~~~~~~~~08001003+00000000000+00000000000... Data 3003 KN> ~~~~~~~~08001004+00000000000+00000000000... Data 3004
KN> My script in progress follows.
KN> SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON lines 500
KN> spool c:\reserve_pro.dat
KN> select case when row_number() over (partition by jurisdiction,
KN> occurrence_limit_name order by number_of_months) =1
KN> then ( rpad('~',8,'~')||'00'||
KN> rpad(case when jurisdiction='State'
KN> then 'ISt'
KN> when
KN> jurisdiction='Federal'
KN> then 'IFe'
KN> end ||
KN> case when
occurrence_limit_name
KN> ='Twenty Five Thousand' then '25k'
KN> when
KN> occurrence_limit_name ='Fifty Thousand' then '50k'
KN> when
KN> occurrence_limit_name ='Two Hundred Fifty Thousand' then '250'
KN> when
KN> occurrence_limit_name ='Five Hundred Thousand' then '500'
KN> when
KN> occurrence_limit_name ='One Million' then '1M'
KN> when
KN> occurrence_limit_name ='Two Million' then '2M'
KN> when
KN> occurrence_limit_name ='Unlimited' then 'Unl'
KN> end,
KN> 8,' ') ||
KN>
KN> to_char(count(distinct number_of_months)
KN> over (partition by jurisdiction,
KN> occurrence_limit_name),'FM000') ||
KN> '03'||'1992'||'04'||
KN>
KN> to_char(count(distinct number_of_months)
KN> over (partition by jurisdiction,
KN> occurrence_limit_name),'FM000') ||
KN>
KN> '03'||'0003'||rpad('x',28,'x')||'Accident'||'Y'||'000000000001'||'G'||
KN> rpad('N',32,'N')||--net
KN> rpad('N',32,'N')||--gross
KN> rpad('N',32,'N')||--Ceded
KN>
KN> 'NNNNY'||'NNNNY'||'YNNNY'||'YNNYN'||'NNNNN'||'NNNNN'||'NN'|| -- Direct
KN> rpad('N',32,'N')|| -- assumed
KN>
KN> 'N'||'N'||rpad('x',148,'x')||chr(10)||
KN> --00 record end here and 01 record begins
KN>
KN> rpad('~',8,'~')||'01'||rpad('All Claims by
KN> Accident Quarter', 390,' ')||rpad('~',6,'~')||chr(10)|| --01 record end
here
KN> and 02 record begins
KN>
KN> rpad('~',8,'~')||'02'||rpad(jurisdiction||' '||
KN> occurrence_limit_name, 390,'
KN> ')||rpad('~',6,'~')||chr(10) --02 record ends here
KN> )
KN> else null
KN> end ||
KN> rpad('~',8,'~')||'08'||
KN> to_char(dense_rank () over (partition by
KN> jurisdiction, occurrence_limit_name order by ay_qtr),'FM000')||
KN> to_char(dense_rank () over (partition by
KN> jurisdiction, occurrence_limit_name order by
KN> number_of_months),'FM000')||
KN> to_char(0,'S00000000000')|| --1
KN> to_char(0,'S00000000000')|| --2
KN> to_char(0,'S00000000000')|| --3
KN> to_char(0,'S00000000000')|| --4
KN> to_char(0,'S00000000000')|| --5
KN> to_char(0,'S00000000000')|| --6
KN> to_char(0,'S00000000000')|| --7
KN> to_char(0,'S00000000000')|| --8
KN> to_char(0,'S00000000000')|| --9
KN>
KN> to_char(round(sum(claim_alae_incurred)),'S00000000000')|| --10
KN>
KN> to_char(round(sum(claim_indemnity_incurred)),'S00000000000')|| --11
KN> to_char(0,'S00000000000')|| --12
KN> to_char(0,'S00000000000')|| --13
KN> to_char(0,'S00000000000')|| --14
KN>
KN> to_char(round(sum(claim_alae_paid)),'S00000000000')|| --15
KN>
KN> to_char(round(sum(claim_indemnity_paid)),'S00000000000')|| --16
KN> to_char(0,'S00000000000')|| --17
KN> to_char(0,'S00000000000')|| --18
KN> to_char(0,'S00000000000')|| --19
KN> to_char(0,'S00000000000')|| --20
KN> to_char(0,'S00000000000')|| --21
KN> to_char(0,'S00000000000')|| --22
KN> to_char(0,'S00000000000')|| --23
KN> to_char(0,'S00000000000')|| --24
KN> to_char(0,'S00000000000')|| --25
KN> to_char(0,'S00000000000')|| --26
KN> to_char(0,'S00000000000')|| --27
KN> to_char(0,'S00000000000')|| --28
KN> to_char(0,'S00000000000')|| --29
KN> to_char(0,'S00000000000')|| --30
KN> to_char(0,'S00000000000')|| --31
KN> to_char(0,'S00000000000')|| --32
KN> to_char(0,'S00000000000')|| --33
KN> to_char(0,'S00000000000')|| --34
KN> to_char(0,'S00000000000')|| --35
KN> to_char(0,'S00000000000')|| --36
KN> to_char(0,'S00000000000')|| --37
KN> to_char(0,'S00000000000')|| --38
KN> to_char(0,'S00000000000')|| --39
KN> rpad('~',6,'?') field
KN> from mv_capped_losses
KN> where rownum<100
KN> group by jurisdiction, occurrence_limit_name, ay_qtr, number_of_months
KN> order by jurisdiction, occurrence_limit_name, ay_qtr, number_of_months;
KN> spool off
KN> exit
KN> Kenneth Naim
KN> --
KN> http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 30 2006 - 10:57:16 CST
 |
 |