Sql PLus - extra linefeed

From: Ken Naim <kennaim_at_gmail.com>
Date: Wed, 29 Mar 2006 18:31:47 -0600
Message-ID: <06fd01c65391$54e56180$fd346f44@KenHome>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 29 2006 - 18:31:47 CST