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@xxxxxxxxxxx] 
Sent: Wednesday, March 29, 2006 10:16 PM
To: Ken Naim
Cc: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx



Wednesday, March 29, 2006, 7:31:47 PM, Ken Naim (kennaim@xxxxxxxxx) 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. Sql
plus
KN> is running on the server.

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 2c 
KN> ~~~~~~~~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


Other related posts: