Re: Explanation of UTL_FILE.PUT

From: <fitzjarrell_at_cox.net>
Date: 23 Oct 2006 12:28:13 -0700
Message-ID: <1161631693.951463.25690@m73g2000cwd.googlegroups.com>

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 22, 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 - 14:28:13 CDT

Re: Explanation of UTL_FILE.PUT_LINE please