utl_file fixed width

[Jami]

Hi all,

I am trying to create a file in fixed width format. The problem is that when I query the database, if one field is empty for each record, that field is not padded. For example, below is my code and the title field (the first field I am attempting to output) does not currently have data and therefore, is not being padded. Can anyone help by telling me how I can code this so that every field is padded by the number I specify, regardless of whether data is returned by my query? Thanks!!

create or replace procedure itt_fixed_file as
----------------Storage Variables ------------------------
n number := 0; error_msg VARCHAR2(300) := SQLERRM;
--------------UTL FILE Variables----------------------
p_file_name VARCHAR2(20); p_file_dir VARCHAR2(70);
v_file_handle UTL_FILE.FILE_TYPE;
-------truncate table variables---------
--v_cursor number; v_dropstring VARCHAR2(100);
--v_numrows NUMBER;
----------------------Cursor-------------------------------
CURSOR itt_rec IS
SELECT upper(address1) as address1,
upper(address2) as address2, upper(adsource) as adsource,
upper(answeringservicecode) as answeringservicecode,
upper(apptcode) as apptcode, upper(aptnum) as aptnum,
upper(buyingmotive1) as buyingmotive1,
upper(buyingmotive2) as buyingmotive2,
upper(careerinterest1) as careerinterest1,
upper(careerinterest2) as careerinterest2,
upper(careerinterest3) as careerinterest3,
upper(careerinterest4) as careerinterest4,
upper(careerinterest5) as careerinterest5,
upper(careerinterest6) as careerinterest6,
upper(careerinterest7) as careerinterest7,
upper(careerinterest8) as careerinterest8,
upper(careerinterest9) as careerinterest9,
upper(careerinterest10) as careerinterest10,
upper(cellphone) as cellphone, upper(city) as city,
upper(country) as country, upper(crcreativity) as crcreativity,
upper(crfun) as crfun, upper(crhighpay) as crhighpay,
upper(crpersonalsat) as crpersonalsat,
upper(crpresitge) as crpresitge, upper(dob) as dob,
upper(email) as email, upper(fname)as fname,
upper(gender) as gender, upper(highschool) as highschool,
upper(homephone) as homephone,
upper(hscnum) as hscnum, upper(idate) as idate,
upper(itime) as itime, upper(lname) as lname,
upper(minitial) as minitial, upper(monthhsgrad) as monthhsgrad,
upper(pager) as pager, upper(postsecondaryplans) as postsecondaryplans, upper(province) as province, upper(referralfrom) as referralfrom, upper(referringwebsite) as referringwebsite,
upper(schoolcode) as schoolcode, upper(sendinfo) as sendinfo,
upper(ssn) as ssn, upper(state) as state,
upper(suffix) as suffix, upper(techinterest) as techinterest,
upper(title) as title, upper(tollfreenumberdialed) as
tollfreenumberdialed, upper(trackingcode) as trackingcode,
upper(workphone) as workphone, upper(yearhsgrad) as yearhsgrad, upper(zipcode) as zipcode, upper(zonename) as zonename
FROM dev.itt_leads;
file_rec itt_rec%ROWTYPE;
----------------Main Program Logic---------------------
begin
DBMS_OUTPUT.ENABLE(50000);
-------Truncate table commands-----------
--v_cursor := DBMS_SQL.OPEN_CURSOR;
--v_dropstring := ‘TRUNCATE TABLE dev.itt_leads’;
p_file_name := 'itt_leads.txt';
p_file_dir := '/export/home/u01/app/oracle/product/8.1.6';
v_file_handle := UTL_FILE.FOPEN(p_file_dir, p_file_name, 'w');
FOR file_rec IN itt_rec
LOOP
UTL_FILE.PUT_LINE(v_file_handle,
RPAD(file_rec.title,6) || RPAD(file_rec.fname,20) ||
RPAD(file_rec.minitial,1) || RPAD(file_rec.lname,40) || RPAD(file_rec.suffix,6) || RPAD(file_rec.gender,1) || RPAD(file_rec.address1,40) || RPAD(file_rec.address2,40) || RPAD(file_rec.aptnum,10) || RPAD(file_rec.city,30) ||
RPAD(file_rec.state,2) || RPAD(file_rec.zipcode,20) || RPAD(file_rec.province,40) || RPAD(file_rec.country,30) || RPAD(file_rec.homephone,20) || RPAD(file_rec.workphone,20) || RPAD(file_rec.cellphone,20) || RPAD(file_rec.pager,20) || RPAD(file_rec.email,240) || RPAD(file_rec.yearhsgrad,4) || RPAD(file_rec.monthhsgrad,2) || RPAD(file_rec.dob,10) || RPAD(file_rec.highschool,30) || RPAD(file_rec.adsource,7) ||
RPAD(file_rec.tollfreenumberdialed,10) || RPAD(file_rec.answeringservicecode,10) ||
RPAD(file_rec.idate,10) || RPAD(file_rec.itime,8) || RPAD(file_rec.trackingcode,30) ||
RPAD(file_rec.referringwebsite,40) ||
RPAD(file_rec.referralfrom,60) ||
RPAD(file_rec.techinterest,1) || RPAD(file_rec.sendinfo,1) ||
RPAD(file_rec.careerinterest1,2) ||
RPAD(file_rec.careerinterest2,2) ||
RPAD(file_rec.careerinterest3,2) ||
RPAD(file_rec.careerinterest4,2) ||
RPAD(file_rec.careerinterest5,2) ||
RPAD(file_rec.careerinterest6,2) ||
RPAD(file_rec.careerinterest7,2) ||
RPAD(file_rec.careerinterest8,2) ||
RPAD(file_rec.careerinterest9,2) ||
RPAD(file_rec.careerinterest10,2) ||
RPAD(file_rec.postsecondaryplans,2) ||
RPAD(file_rec.buyingmotive1,20) ||
RPAD(file_rec.buyingmotive2,20) || RPAD(file_rec.ssn,11) ||
RPAD(file_rec.zonename,3) || RPAD(file_rec.schoolcode,9) ||
RPAD(file_rec.crpersonalsat,1) || RPAD(file_rec.crhighpay,1) ||
RPAD(file_rec.crpresitge,1) || RPAD(file_rec.crfun,1) ||
RPAD(file_rec.crcreativity,1) || RPAD(file_rec.hscnum,9) ||
RPAD (file_rec.apptcode,1));
END LOOP; UTL_FILE.FCLOSE(v_file_handle);
-------Truncate table commands-----------
--DBMS_SQL.PARSE(v_cursor,v_dropstring,DBMS_SQL.V7);
--v_numrows := DBMS_SQL.EXECUTE(v_cursor);
--DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

Much thanks!
Jami

 

[carp]

If the field has a NULL value, you could use the NVL function to stick something in there:


RPAD(NVL(file_rec.title,' '),6)

Of course, this now introduces a character in the field instead of a NULL value. This may or may not be a problem, depending on what you are doing with the file.

 

[Jami]

Hi Carp,
Thanks for your reply. I added the NVL function, changing just this line and replaced my procedure. I see the new file has been created, but nothing has changed. This column is still not being created. Any ideas why? Again here is the output line I am writing to the file:

v_out_line :=
RPAD(NVL(file_rec.title,’’),6) || RPAD(file_rec.fname,20) || RPAD(file_rec.minitial,1) || RPAD(file_rec.lname,40) || RPAD(file_rec.suffix,6) || RPAD(file_rec.gender,1) || RPAD(file_rec.address1,40) || RPAD(file_rec.address2,40) || RPAD(file_rec.aptnum,10) || RPAD(file_rec.city,30) || RPAD(file_rec.state,2) || RPAD(file_rec.zipcode,20) || RPAD(file_rec.province,40) || RPAD(file_rec.country,30) || RPAD(file_rec.homephone,20) || RPAD(file_rec.workphone,20) || RPAD(file_rec.cellphone,20) || RPAD(file_rec.pager,20) || RPAD(file_rec.email,240) || RPAD(file_rec.yearhsgrad,4) || RPAD(file_rec.monthhsgrad,2) || RPAD(file_rec.dob,10) || RPAD(file_rec.highschool,30) || RPAD(file_rec.adsource,7) || RPAD(file_rec.tollfreenumberdialed,10) || RPAD(file_rec.answeringservicecode,10) || RPAD(file_rec.idate,10) || RPAD(file_rec.itime,8) || RPAD(file_rec.trackingcode,30) || RPAD(file_rec.referringwebsite,40) || RPAD(file_rec.referralfrom,60) || RPAD(file_rec.techinterest,1) || RPAD(file_rec.sendinfo,1) || RPAD(file_rec.careerinterest1,2) || RPAD(file_rec.careerinterest2,2) || RPAD(file_rec.careerinterest3,2) || RPAD(file_rec.careerinterest4,2) || RPAD(file_rec.careerinterest5,2) || RPAD(file_rec.careerinterest6,2) || RPAD(file_rec.careerinterest7,2) || RPAD(file_rec.careerinterest8,2) || RPAD(file_rec.careerinterest9,2) || RPAD(file_rec.careerinterest10,2) || RPAD(file_rec.postsecondaryplans,2) || RPAD(file_rec.buyingmotive1,20) || RPAD(file_rec.buyingmotive2,20) || RPAD(file_rec.ssn,11) || RPAD(file_rec.zonename,3) || RPAD(file_rec.schoolcode,9) || RPAD(file_rec.crpersonalsat,1) || RPAD(file_rec.crhighpay,1) || RPAD(file_rec.crpresitge,1) || RPAD(file_rec.crfun,1) || RPAD(file_rec.crcreativity,1) || RPAD(file_rec.hscnum,9) || RPAD (file_rec.apptcode,1);

Thanks again!
Jami

 

[carp]

You need to stick a blank space between the two single quotes:
RPAD(NVL(file_rec.title,’ ’),6)
not
RPAD(NVL(file_rec.title,’’),6)

You might want to stick a visible literal in there during tests just so you can see something:

RPAD(NVL(file_rec.title,’~’),6)

 

[karluk]

If you look closely at Carp's suggestion you will see that he recommends RPAD(NVL(file_rec.title,' '),6) whereas you wrote RPAD(NVL(file_rec.title,''),6), with no space between the single quotes. Try again with the space and it should work.

 

[Jami]

Thank you both again for you help. That was what I was lacking. Things are working for me now!
Jami