I have a spool script that creates a fixed length output file. I need spaces in the end of the spooled file.
But those spaces are getting trimmed in the end. The output linsize should be 1000 with all spaces from col 656 but the output gets terminated at 656 with no spaces in the end.
SET TIMING OFF
SET TERM OFF
SET HEADING OFF
SET PAGESIZE OFF
SET LINESIZE 1000
--SET TRIMSPOOL ON
--set TRIMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET ECHO OFF
spool abc.txt
select
rpad(Record_Type,3, ' ')||''||
lpad('0',9,'0')||''||
rpad(' ',3,' ')||''||
lpad('0',9,'0')||''||
rpad(' ',5,' ')||''||
lpad('0',10,'0')||''||
rpad(' ',12,' ')||''||
rpad(nvl(Employee_CUMB_ID,' '), 15, ' ')||''||
lpad(nvl(Dependent_CUMB_ID, '0'),15,'0')||''||
lpad(nvl(Sequence, '0'), 5, '0')||''||
rpad(' ',50,' ')||''||
rpad(nvl(Diag_cd1,' '), 10, ' ')||''||
rpad(nvl(Diag_cd2,' '), 10, ' ')||''||
rpad(nvl(Diag_cd3,' '), 10, ' ')||''||
rpad(nvl(Diag_cd4,' '), 10, ' ')||''||
rpad(nvl(Diag_cd5,' '), 10, ' ')||''||
rpad(nvl(Diag_cd6,' '), 10, ' ')||''||
rpad(nvl(Diag_cd7,' '), 10, ' ')||''||
rpad(nvl(Diag_cd8,' '), 10, ' ')||''||
rpad(nvl(Diag_cd9,' '), 10, ' ')||''||
rpad(nvl(Diag_cd10,' '), 10, ' ')||''||
rpad(nvl(Diag_cd11,' '), 10, ' ')||''||
rpad(nvl(Diag_cd12,' '), 10, ' ')||''||
rpad(nvl(Diag_cd13,' '), 10, ' ')||''||
rpad(nvl(Diag_cd14,' '), 10, ' ')||''||
rpad(nvl(Diag_cd15,' '), 10, ' ')||''||
rpad(nvl(Diag_cd16,' '), 10, ' ')||''||
rpad(nvl(Diag_cd17,' '), 10, ' ')||''||
rpad(nvl(Diag_cd18,' '), 10, ' ')||''||
rpad(nvl(Diag_cd19,' '), 10, ' ')||''||
rpad(nvl(Diag_cd20,' '), 10, ' ')||''||
rpad(nvl(Diag_cd21,' '), 10, ' ')||''||
rpad(nvl(Diag_cd22,' '), 10, ' ')||''||
rpad(nvl(Diag_cd23,' '), 10, ' ')||''||
rpad(nvl(Diag_cd24,' '), 10, ' ')||''||
rpad(nvl(Diag_cd25,' '), 10, ' ')||''||
rpad(nvl(Diag_cd26,' '), 10, ' ')||''||
rpad(nvl(Diag_cd27,' '), 10, ' ')||''||
rpad(nvl(Diag_cd28,' '), 10, ' ')||''||
rpad(nvl(Diag_cd29,' '), 10, ' ')||''||
rpad(nvl(Diag_cd30,' '), 10, ' ')||''||
rpad(nvl(Diag_cd31,' '), 10, ' ')||''||
rpad(nvl(Diag_cd32,' '), 10, ' ')||''||
rpad(nvl(Diag_cd33,' '), 10, ' ')||''||
rpad(nvl(Diag_cd34,' '), 10, ' ')||''||
rpad(nvl(Diag_cd35,' '), 10, ' ')||''||
rpad(nvl(Diag_cd36,' '), 10, ' ')||''||
rpad(nvl(Diag_cd37,' '), 10, ' ')||''||
rpad(nvl(Diag_cd38,' '), 10, ' ')||''||
rpad(' ', 10, ' ')||''||
rpad(nvl(Diag_cd40,' '), 10, ' ')||''||
lpad('0',10,'0')||''||
rpad(nvl(diagnosis_code_set,' '), 10,' ')||''||
lpad('0',10,'0')||''||
rpad(nvl(diagnosis_type,' '), 2, ' ')||''||
lpad('0',10,'0')||''||
rpad(nvl(npi_id,' '), 15, ' ')||''||
rpad(' ',5,' ')||''||
lpad(nvl(Visit_number, '0'), 8, '0')||''||
rpad(' ',5,' ')||''||
rpad(nvl(Visit_Patient_Class,' '), 3, ' ')||''||
rpad(' ',5,' ')||''||
case when VISIT_DT is not null then to_char(VISIT_DT,'yyyymmdd') else lpad('0',8,'0') end||''||
lpad('0',10,'0')||''||
--lpad(nvl(VISIT_Duration,'0'), 8, '0')||''||
lpad('0', 8, '0')||''||
lpad('0',10,'0')||CHR(32)||''||
lpad(nvl(Visit_reason, ' '),200,' ')||''||
rpad(' ',145,' ')
from ST_claims_final
;
spool off;
TRIMSPOOL OFF and TRIMOUT OFF is also not working.
Please help.