WOW...what a pain.....but if that's my only solution then that will
have to work. Thanks for explaining it so it's not over my head.
Tom
fitzjarrell_at_cox.net wrote:
> tawright915 wrote:
> > I want to write out a flat file in which the fields are fixed sized
> > fields. Basically the smae length of the fields in the DB. For
> > instance if name is 22 chars then the field must be 22chars wide even
> > if the name is only 'TOM'. So my confusion is what are the pipes in
> > the output:
> >
> > Example:
> > UTL_FILE.PUT_LINE(v_outputfile,v_vip||';'||v_bay||';'||v_p_no);
> > Currently I can see this is delimited by semi-colons. So if I wanted
> > the output to be fixed sized fields how would this look?
> >
> > Thanks
> > Tom
>
> You'll need to tell SQL*Plus you want those trailing spaces, most
> likely using RPAD(). Presuming your columns have widths of 220, 40 and
> 12 respectively you could do this:
>
> UTL_FILE.PUT_LINE(v_outputfile,rpad(v_vip, 23)||rpad(v_bay,
> 41)||rpad(v_p_nos,12));
>
> Adding 1 to all of the RPAD lengths except the last puts a separating
> space between field values (as shown in the example). As a working
> example using the SCOTT schema:
>
> SQL> l
> 1 declare
> 2 file_id utl_file.file_type;
> 3 file_buf varchar2(50);
> 4 cursor get_emp_data is
> 5 select empno, ename, job, sal, deptno, mgr
> 6 from emp;
> 7 begin
> 8 file_id := utl_file.fopen('d:\temp','fixedlen.txt', 'w');
> 9
> 10 for emprec in get_emp_data loop
> 11 utl_file.put_line(file_id, rpad(emprec.empno,
> 5)||rpad(emprec.ename, 11)||rpad(emprec.job, 10)||rpad(emprec.sal,
> 10)||rpad(emprec.deptno,3)||rpad(emprec.mgr, 4));
> 12 end loop;
> 13
> 14 utl_file.fclose(file_id);
> 15
> 16 file_id := utl_file.fopen('d:\temp','fixedlen.txt', 'r');
> 17
> 18 loop
> 19
> 20 utl_file.get_line(file_id, file_buf);
> 21 dbms_output.put_line(file_buf);
> 22
> 23 end loop;
> 24
> 25 exception
> 26
> 27 when no_data_found then
> 28 utl_file.fclose(file_id);
> 29
> 30* end;
> SQL> /
> 7369 SMITH CLERK 800 20 7902
> 7499 ALLEN SALESMAN 1600 30 7698
> 7521 WARD SALESMAN 1250 30 7698
> 7566 JONES MANAGER 2975 20 7839
> 7654 MARTIN SALESMAN 1250 30 7698
> 7698 BLAKE MANAGER 2850 30 7839
> 7782 CLARK MANAGER 2450 10 7839
> 7788 SCOTT ANALYST 3000 20 7566
> 7839 KING PRESIDENT 5000 10
> 7844 TURNER SALESMAN 1500 30 7698
> 7876 ADAMS CLERK 1100 20 7788
> 7900 JAMES CLERK 950 30 7698
> 7902 FORD ANALYST 3000 20 7566
> 7934 MILLER CLERK 1300 10 7782
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
> The formatting may not be correctly displayed should you be using
> Google or a newsreader with a proportional font, however rest assured
> each record is the same length, and each field except for the last in
> each record is padded with blanks to the field length plus 1. If you
> are using google to read this you can set the font to fixed width and
> see the results properly.
>
>
> David Fitzjarrell
Received on Mon Oct 23 2006 - 15:01:04 CDT
