|
|
eyetry (Programmer) |
14 Aug 08 16:14 |
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
|
|
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 (Programmer) |
15 Aug 08 8:14 |
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 (Programmer) |
15 Aug 08 8:17 |
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,' ');
|
|
Don't know why it did not work for you: CODESQL> 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
|
|
|
 |
Talk To Other Members
Read More Threads Like This One