Home » SQL & PL/SQL » SQL & PL/SQL » An initial blank line in a spool file
An initial blank line in a spool file [message #199480] Tue, 24 October 2006 13:24 Go to next message
Mike Bentley
Messages: 8
Registered: September 2006
Junior Member
I have created a comma delimited spool file for a data feed. The code is below. The data is extracted and formated fine. However, I get a blank line at the start of the spool file. Can I get rid of this or do I just have to live with it?

sqlplus -s user/pass@ocxd
set linesize 850
set feedback off VERIFY OFF 
set heading off 
spool c:\oracle\ora92\bin\ctbnc.sql
select 'define vsystitle = ', 
        to_char(sysdate, 'yyyymmddhh24miss') from dual;
spool off
@c:\oracle\ora92\bin\ctbnc
spool c:\oracle\ora92\bin\smh_visits_&vsystitle..csv
SELECT	rpad(s.client_study_no, 25, ' ')||',',
	rpad(s.brief_title, 30, ' ')||',',
	rpad(pr.middle_initial, 5, ' ')||',',
	rpad(s.study_duration, 30, ' ')||',',
	rpad(nvl(s.treatment_period, ' '), 30, ' ')||',',
	'x'||lpad(p.other_id1, 20, '0')||',',
	rpad(nvl(substr(p.other_id2, 6, 4), ' '), 4, ' ')||',',
	'x'||to_char(a.begin_datetime, 'mm-dd-yy')||',',
	rpad(p.first_name, 30, ' ')||',',
	rpad(p.middle_initial, 5, ' ')||',',
	rpad(p.last_name, 30, ' ')||',',
	rpad(v.visit_name, 30, ' ')||',',
        'x'||to_char(a.date_created, 'mm-dd-yy')||','
FROM	sst.study s,
	sst.site_placement sp,
	sst.patient p,
	sst.visit v,
	sst.appointment a,
	sst.professional pr
WHERE	a.visit_id 			= v.visit_id
AND	(trunc(a.date_created)		= trunc(sysdate)
	OR trunc(a.begin_datetime)	= trunc(sysdate)
	OR (trunc(a.date_modified)	= trunc(sysdate)
	    AND EXISTS
		(SELECT 'x' FROM audit_trail at
		WHERE	a.appointment_id	= at.pk1
		AND	at.table_name		= 'APPOINTMENT'
		AND	at.field_name		= 'BEGIN_DATETIME'
		AND 	trunc(at.date_of_change) = trunc(sysdate))))
AND	a.patient_id			= p.patient_id
AND	a.study_id			= s.study_id
AND	s.study_id			= sp.study_id
AND	sp.site_legal_staff		= pr.professional_id
AND	s.status 			= 'A'
AND	sp.status			= 'A'
AND	p.status			= 'A'
AND	v.status			= 'A'
AND	a.status			= 'A';
spool off


Thanks
Mike
Re: An initial blank line in a spool file [message #199487 is a reply to message #199480] Tue, 24 October 2006 14:37 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
try

set embed on

before running the spool and select.
Re: An initial blank line in a spool file [message #199490 is a reply to message #199480] Tue, 24 October 2006 14:53 Go to previous messageGo to next message
Mike Bentley
Messages: 8
Registered: September 2006
Junior Member
That worked, thanks very much

Mike
Re: An initial blank line in a spool file [message #199492 is a reply to message #199490] Tue, 24 October 2006 14:54 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
Your welcome
Previous Topic: Disable All constraints in single SQL statement...
Next Topic: Validating a DDL before execution
Goto Forum:
  


Current Time: Sat Dec 03 12:07:32 CST 2016

Total time taken to generate the page: 0.19616 seconds