Home » SQL & PL/SQL » SQL & PL/SQL » Leading zeros in a spool file
Leading zeros in a spool file [message #210433] Wed, 20 December 2006 16:25 Go to next message
Mike Bentley
Messages: 8
Registered: September 2006
Junior Member
I'm having a problem with spooling data. I am using the following sqlplus script

sqlplus -s login/password@ocxd
set linesize 850
set pagesize 10000
set feedback off
set heading on
set underline off
set termout off
set echo off
set newpage none space 0 embed on
spool c:\oracle\ora92\bin\ctbnc.sql
select 'define vsystitle = ', 
        to_char(sysdate, 'yyyymmddhh24mi') from dual;
spool off
@c:\oracle\ora92\bin\ctbnc
spool c:\oracle\ora92\bin\smm_ptcase_&vsystitle..csv
select
	st.client_study_no "Study No", ', ',
	pr.middle_initial "Type", ', ',
	st.study_duration "Dept study #", ', ',
	st.treatment_period "GCRC", ', ',
	p.other_id1 "MRN#", ', ',
	substr(p.other_id2, 6, 4) "SS#", ', ',
	p.first_name, ', ',
	p.middle_initial, ', ',
	p.last_name, ', ',
	p.status, ', '
from 	study st,
	patient_association pa,
	patient p,
	site_placement sp,
	professional pr
where	trunc(pa.date_created)		= trunc(sysdate)
and	st.study_id 			= pa.study_id 
and	pa.patient_id 			= p.patient_id
-- AND	nvl(pr.middle_initial, ' ')	NOT IN ('X', 'N')
AND	st.study_id			= sp.study_id 
AND	nvl(sp.site_legal_staff, 0)	= pr.professional_id(+)
	and (pr.status			= 'A'
          or pr.status is null);
spool off

quit


This works fine except for one problem. The column p.other_id2 is a VARCHAR2(20) column containing the social security number. I want to display the last 4 digits. When I display this in SQLPLUS it is fine and I get all four, even leading zeros. However, when I spool it, the spool file removes any leading zeros, so the ss number 999-99-0999 display in a query as 0999 but in the spool file it is 999. I have tried the column command

COLUMN SSN# FORMAT 0999


and
COLUMN SSN# FORMAT 9999 


and
COLUMN SSN# FORMAT A4


and using
	to_char(substr(p.other_id2, 6, 4), '9999') "SS#", ', ',


I have also tried left padding it but as soon as I spool it, the leading zeros disappear. These formats only seem to apply to the displayed data. So, how do I get it to keep the leading zeros in the spool file?

Thanks
Mike
Re: Leading zeros in a spool file [message #210448 is a reply to message #210433] Wed, 20 December 2006 19:43 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's pretty strange, I can't repeat your results - it works OK for me.

Here's my test script:
set linesize 850
set pagesize 10000
set feedback off
set heading on
set underline off
set termout off
set echo off
set colsep ', '
set newpage none space 0 embed on
spool a
select
        '-->',
        substr(123450005,6, 4) "SS#",
        '<--'
from    dual
/
spool off


For me, it wrote the string 0005 to the file

Suggestion though:
- Get rid of SPACE 0
- Add SET COLSEP ', '
- Get rid of the hard-coded comma-separators
- Add SET TRIMSPOOL ON

I won't help your current problem, but it will improve your script.

Ross Leishman

[Updated on: Wed, 20 December 2006 19:46]

Report message to a moderator

Previous Topic: record type - Urgent help required
Next Topic: Decode function (merged)
Goto Forum:
  


Current Time: Thu Dec 08 04:18:36 CST 2016

Total time taken to generate the page: 0.12211 seconds