OraFAQ Forum: SQL & PL/SQL » Leading zeros in a spool file

Home » SQL & PL/SQL » SQL & PL/SQL » Leading zeros in a spool file

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Leading zeros in a spool file [message #210433]

Wed, 20 December 2006 16:25

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

Report message to a moderator

Re: Leading zeros in a spool file [message #210448 is a reply to message #210433]

Wed, 20 December 2006 19:43

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

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.

Report message to a moderator