Sql PLus - extra linefeed
- From: "Ken Naim" <kennaim@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 29 Mar 2006 18:31:47 -0600
I am having a issue with the spooling of a query to file where sql plus is
inserting an extra linefeed and I cannot determine the cause. The file will
be imported into a 3rd party app that I do not have any control over. I
might be missing the obvious as I have working on it all afternoon so I
appreciate any assistance. I am running on oracle 10gR1 on windows. Sql plus
is running on the server.
The query is slightly complicated as it has to write a 3 line header prior
to each of 14 sections. Each section will contain about 3000 records. Each
line is 490 characters long so I will truncate the lines to illustrate the
problem. After each of the fourteen 3 line header records, 1 data record is
written followed by a blank line (issue) followed by all the other data
record for the section. The application is crapping out on the blank lines
breaking the entire import.
~~~~~~~~00IFe50k 05303199204053030003... Header 1a
~~~~~~~~01All Items by Quarter... Header 1b
~~~~~~~~02Fifty Thousand... Header 1c
~~~~~~~~08001001+00000000000+00000000000... Data 1
~~~~~~~~08001002+00000000000+00000000000... Data 2
~~~~~~~~08001003+00000000000+00000000000... Data 3
~~~~~~~~08001004+00000000000+00000000000... Data 4
~~~~~~~~00IFe50k 05303199204053030003... Header 2a
~~~~~~~~01All Items by Quarter... Header 2b
~~~~~~~~02Five Thousand... Header 2c
~~~~~~~~08001001+00000000000+00000000000... Data 3001
~~~~~~~~08001002+00000000000+00000000000... Data 3002
~~~~~~~~08001003+00000000000+00000000000... Data 3003
~~~~~~~~08001004+00000000000+00000000000... Data 3004
My script in progress follows.
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON lines 500
spool c:\reserve_pro.dat
select case when row_number() over (partition by jurisdiction,
occurrence_limit_name order by number_of_months) =1
then ( rpad('~',8,'~')||'00'||
rpad(case when jurisdiction='State'
then 'ISt'
when
jurisdiction='Federal'
then 'IFe'
end ||
case when occurrence_limit_name
='Twenty Five Thousand' then '25k'
when
occurrence_limit_name ='Fifty Thousand' then '50k'
when
occurrence_limit_name ='Two Hundred Fifty Thousand' then '250'
when
occurrence_limit_name ='Five Hundred Thousand' then '500'
when
occurrence_limit_name ='One Million' then '1M'
when
occurrence_limit_name ='Two Million' then '2M'
when
occurrence_limit_name ='Unlimited' then 'Unl'
end,
8,' ') ||
to_char(count(distinct number_of_months)
over (partition by jurisdiction, occurrence_limit_name),'FM000') ||
'03'||'1992'||'04'||
to_char(count(distinct number_of_months)
over (partition by jurisdiction, occurrence_limit_name),'FM000') ||
'03'||'0003'||rpad('x',28,'x')||'Accident'||'Y'||'000000000001'||'G'||
rpad('N',32,'N')||--net
rpad('N',32,'N')||--gross
rpad('N',32,'N')||--Ceded
'NNNNY'||'NNNNY'||'YNNNY'||'YNNYN'||'NNNNN'||'NNNNN'||'NN'|| -- Direct
rpad('N',32,'N')|| -- assumed
'N'||'N'||rpad('x',148,'x')||chr(10)||
--00 record end here and 01 record begins
rpad('~',8,'~')||'01'||rpad('All Claims by
Accident Quarter', 390,' ')||rpad('~',6,'~')||chr(10)|| --01 record end here
and 02 record begins
rpad('~',8,'~')||'02'||rpad(jurisdiction||' '|| occurrence_limit_name, 390,'
')||rpad('~',6,'~')||chr(10) --02 record ends here
)
else null
end ||
rpad('~',8,'~')||'08'||
to_char(dense_rank () over (partition by
jurisdiction, occurrence_limit_name order by ay_qtr),'FM000')||
to_char(dense_rank () over (partition by
jurisdiction, occurrence_limit_name order by number_of_months),'FM000')||
to_char(0,'S00000000000')|| --1
to_char(0,'S00000000000')|| --2
to_char(0,'S00000000000')|| --3
to_char(0,'S00000000000')|| --4
to_char(0,'S00000000000')|| --5
to_char(0,'S00000000000')|| --6
to_char(0,'S00000000000')|| --7
to_char(0,'S00000000000')|| --8
to_char(0,'S00000000000')|| --9
to_char(round(sum(claim_alae_incurred)),'S00000000000')|| --10
to_char(round(sum(claim_indemnity_incurred)),'S00000000000')|| --11
to_char(0,'S00000000000')|| --12
to_char(0,'S00000000000')|| --13
to_char(0,'S00000000000')|| --14
to_char(round(sum(claim_alae_paid)),'S00000000000')|| --15
to_char(round(sum(claim_indemnity_paid)),'S00000000000')|| --16
to_char(0,'S00000000000')|| --17
to_char(0,'S00000000000')|| --18
to_char(0,'S00000000000')|| --19
to_char(0,'S00000000000')|| --20
to_char(0,'S00000000000')|| --21
to_char(0,'S00000000000')|| --22
to_char(0,'S00000000000')|| --23
to_char(0,'S00000000000')|| --24
to_char(0,'S00000000000')|| --25
to_char(0,'S00000000000')|| --26
to_char(0,'S00000000000')|| --27
to_char(0,'S00000000000')|| --28
to_char(0,'S00000000000')|| --29
to_char(0,'S00000000000')|| --30
to_char(0,'S00000000000')|| --31
to_char(0,'S00000000000')|| --32
to_char(0,'S00000000000')|| --33
to_char(0,'S00000000000')|| --34
to_char(0,'S00000000000')|| --35
to_char(0,'S00000000000')|| --36
to_char(0,'S00000000000')|| --37
to_char(0,'S00000000000')|| --38
to_char(0,'S00000000000')|| --39
rpad('~',6,'?') field
from mv_capped_losses
where rownum<100
group by jurisdiction, occurrence_limit_name, ay_qtr, number_of_months
order by jurisdiction, occurrence_limit_name, ay_qtr, number_of_months;
spool off
exit
Kenneth Naim
--
//www.freelists.org/webpage/oracle-l
Other related posts:
