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 messageGo to next message
rleishman
Messages: 3728
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

Re: Leading zeros in a spool file [message #676931 is a reply to message #210433] Mon, 29 July 2019 04:09 Go to previous messageGo to next message
nr24789@gmail.com
Messages: 1
Registered: July 2019
Junior Member
Simplest way to achieve this!!!

concatenate your coulumn which has leading zero with prefix CHR(28) and you are done.

e.g.

spool file1.csv select numbercol from dual; select CHR(28)||numbercol from table; spool off;

CHR(28) is a invisible char representation of a ASCII value.
Re: Leading zeros in a spool file [message #676932 is a reply to message #676931] Mon, 29 July 2019 08:36 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
What is the column type for other_id2?
Re: Leading zeros in a spool file [message #676933 is a reply to message #676932] Mon, 29 July 2019 08:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
newbie resurrect a zombie thread from 2006 which deserves to remain dead
Re: Leading zeros in a spool file [message #676934 is a reply to message #676931] Mon, 29 July 2019 08:52 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
Original question and answers are from 2006 Smile
Re: Leading zeros in a spool file [message #676935 is a reply to message #676934] Mon, 29 July 2019 09:00 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
_jum wrote on Mon, 29 July 2019 09:52
Original question and answers are from 2006 Smile
LOL,
Thanks for the heads up. I didn't notice it's living dead status.
Previous Topic: Load multiline data in table using sql loader
Next Topic: Convert Columns to rows
Goto Forum:
  


Current Time: Thu Mar 28 11:36:16 CDT 2024