fixed column width on output...

[eyetry]

We had someone that was running something like the following then manually modifying the file when done. I want to get rid of manual processes like this. I rewrote this today but my script now writes the data to a file but the fields are not fixed length. How do I easily do that.

WHENEVER SQLERROR EXIT FAILURE ROLLBACK

set head off
set space 0
set feedback off
set pagesize 0
set echo off
set termout off

spool \\path\file_name.txt
set linesize 106

column id_no format a3
column alt_id format a9
column start_date format a8
column stop_date format a8
column tp_number format a15
column routing_data format a3
column partner_name format a60

SELECT UPPER(SUBSTR(valid_code, 1, 3)) id_no,
SUBSTR(UPPER(alt_id), 1, 9) alt_id,
TO_CHAR(start_date,'YYYYMMDD') start_date,
TO_CHAR(stop_date,'YYYYMMDD') stop_date,
UPPER(tp_number) tp_number,
SUBSTR(UPPER(routing_data_vc), 1, 3) routing_data,
UPPER(partner_name) partner_name
FROM prov.pro_partner_control pro,
del_valid_code del
WHERE del.valid_code_type = pro.company_vc
AND trunc(start_date) <= trunc(sysdate)
AND NVL(trunc(stop_date), '31-DEC-2099') >= trunc(sysdate)
ORDER BY id_no, alt_id, start_date, stop_date;

spool off
exit

 

[SantaMufasa]

Eyetry,

Can you please post a couple of lines of output that illustrate the problem that you are having? That will help us troubleshoot your problem.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[eyetry]

Actually, I fixed the fixed length issue by using rpad where the records get written to the file. However, my fix does not seem to work when an output field is null

l_print_line := rpad(c1.id_no,3,' ') ||
rpad(c1.alt_id,9,' ') ||
rpad(c1.start_date,8,' ') ||
rpad(c1.stop_date,8,' ') || --not padding when stop_date is null
rpad(c1.tp_number,9,' ') || --not padding when tp_number is null
rpad(c1.routing_data,3,' ') ||
rpad(c1.partner_name,60,' ');

So, this:

12312345678 20080101123name1
321987654321200801012008123112345678 123name2

Should be this:

12312345678 20080101 123name1
321987654321200801012008123112345678 123name2

 

[eyetry]

Thanks, all. Figured it out. I did this...

l_print_line := rpad(nvl(c1.id_no,' '),3,' ') ||
rpad(nvl(c1.alt_id,' '),9,' ') ||
rpad(nvl(c1.start_date,' '),8,' ') ||
rpad(nvl(c1.stop_date,' '),8,' ') ||
rpad(nvl(c1.tp_number,' '),9,' ') ||
rpad(nvl(c1.routing_data,' '),3,' ') ||
rpad(nvl(c1.partner_name,' '),60,' ');

 

[LKBrwnDBA]

Don't know why it did not work for you:

SQL> Drop Table Mytable;
SQL> Create Table Mytable
  2  (Valid_Code Varchar2(5)
  3  ,Alt_Id Varchar2(10)
  4  ,Start_Date Date
  5  ,Stop_Date Date
  6  ,Tp_Number Varchar2(20)
  7  ,Routing_Data_Vc Varchar2(5)
  8  ,Partner_Name Varchar2(60)
  9  );
SQL> 
SQL> Alter Session Set Nls_Date_Format='Yyyymmdd';
SQL> Insert Into Mytable Values ('123xx','12345678'  ,'20080101',Null      ,Null             ,'123','Name1');
SQL> Insert Into Mytable Values ('321xx','987654321' ,'20080101','20081231','12345678'       ,'123','Name2');
SQL> Insert Into Mytable Values ('12345','Id00093271','20070304','20080303','Tpn0001222333'  ,'Atl','Partner#0001');
SQL> Insert Into Mytable Values ('23456','Id003654'  ,'20070101','20080101','Tpn111222333444','Nyc','Partner#0002');
SQL> Insert Into Mytable Values ('34567','Id00090471','20071010','20081009','Tpn1233123122'  ,'Lax','Partner#0003');
SQL> Insert Into Mytable Values ('19424','Id00080571','20070904','20080904','Tpn0344243433'  ,'Ftl','Partner#0004');
SQL> Insert Into Mytable Values ('18055','Id00671'   ,'20070805','20080805','Tpn070147622343','Mia','Partner#0005');
SQL> Insert Into Mytable Values ('10764','Id00090471','20070706','20080706','Tpn007611562355','Mex','Partner#0006');
SQL> Insert Into Mytable Values ('67385','Id00080571','20070607','20080607','Tpn0006226530'  ,'Atl','Partner#0007');
SQL> Insert Into Mytable Values ('58376','Id00671'   ,'20070508','20080508','Tpn004516264632','Dfw','Partner#0008');
SQL> Insert Into Mytable Values ('49367','Id00060771','20070409','20080409','Tpn004164465633','Nmx','Partner#0009');
SQL> Insert Into Mytable Values ('41504','Id00050871','20070401','20080401','Tpn007655232334','Swk','Partner#0010');
SQL> Insert Into Mytable Values ('52200','Id00040971','20070502','20080502','Tpn002415435335','Qty','Partner#0011');
SQL> Alter Session Set Nls_Date_Format='Dd-Mon-Yy';
SQL> Commit;
SQL> 
SQL> Set Head Off
SQL> Set Space 0
SQL> Set Feedback Off
SQL> Set Pagesize 0
SQL> Set Echo Off
SQL> Set Termout On
SQL> --Set Termout Off
SQL> Set Linesize 106
SQL> 
SQL> Column Id_No      Format A3
SQL> Column Alt_Id             Format A9
SQL> Column Start_Date     Format A8
SQL> Column Stop_Date     Format A8
SQL> Column Tp_Number Format A15
SQL> Column Routing_Data      Format A3
SQL> Column Partner_Name     Format A60
SQL> Spool File_Name.Txt
SQL> Prompt ....+....1....+....2....+....3....+....4....+....5....+...
....+....1....+....2....+....3....+....4....+....5....+...
SQL> Select Upper(Substr(Valid_Code, 1, 3)) Id_No,
  2         Substr(Upper(Alt_Id), 1, 9) Alt_Id,
  3         To_Char(Start_Date,'Yyyymmdd') Start_Date,
  4         To_Char(Stop_Date,'Yyyymmdd') Stop_Date,
  5         Upper(Tp_Number) Tp_Number,
  6         Substr(Upper(Routing_Data_Vc), 1, 3) Routing_Data,
  7         Upper(Partner_Name) Partner_Name
  8    From Mytable
  9   Order By Id_No, Alt_Id, Start_Date, Stop_Date
 10  /
107ID00090472007070620080706TPN007611562355MEXPARTNER#0006
12312345678 20080101                       123NAME1
123ID00093272007030420080303TPN0001222333  ATLPARTNER#0001
180ID00671  2007080520080805TPN070147622343MIAPARTNER#0005
194ID00080572007090420080904TPN0344243433  FTLPARTNER#0004
234ID003654 2007010120080101TPN111222333444NYCPARTNER#0002
321987654321200801012008123112345678       123NAME2
345ID00090472007101020081009TPN1233123122  LAXPARTNER#0003
415ID00050872007040120080401TPN007655232334SWKPARTNER#0010
493ID00060772007040920080409TPN004164465633NMXPARTNER#0009
522ID00040972007050220080502TPN002415435335QTYPARTNER#0011
583ID00671  2007050820080508TPN004516264632DFWPARTNER#0008
673ID00080572007060720080607TPN0006226530  ATLPARTNER#0007
SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb