Output with UTL_FILE

Hi,
I create this procedure to write in output file:

CREATE OR REPLACE PROCEDURE WRITE_FILE
AS

       v_file  UTL_FILE.FILE_TYPE;

       directory         varchar2(50) := 'c:\write_file';
       file_name        varchar2(50) := 'w_file';

      CURSOR c_data IS
          SELECT empno, ename
          FROM   emp;


BEGIN

v_file := sys.utl_file.fopen(directory, file_name||'.txt','w');


  FOR cur_rec IN c_data LOOP
    UTL_FILE.PUT_LINE(v_file,
                      cur_rec.empno    || ';' ||
                      cur_rec.ename);
  END LOOP;

  UTL_FILE.FCLOSE(v_file);

EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_file);

END WRITE_FILE;

It's correct but the data haven't header columns.

How can I create this procedure to output file with  header columns?

Thanks!
LVL 1
db_seniorAsked:
Who is Participating?
 
chedgeyConnect With a Mentor Commented:
for a simple application like this why not just write the line to the file yourself:

CREATE OR REPLACE PROCEDURE WRITE_FILE
AS

       v_file  UTL_FILE.FILE_TYPE;

       directory         varchar2(50) := 'c:\write_file';
       file_name        varchar2(50) := 'w_file';

      CURSOR c_data IS
          SELECT empno, ename
          FROM   emp;


BEGIN

v_file := sys.utl_file.fopen(directory, file_name||'.txt','w');

********************************
UTL_FILE.PUT_LINE(v_file,'EMPNO;ENAME');
********************************

  FOR cur_rec IN c_data LOOP
    UTL_FILE.PUT_LINE(v_file,
                      cur_rec.empno    || ';' ||
                      cur_rec.ename);
  END LOOP;

  UTL_FILE.FCLOSE(v_file);

EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_file);

END WRITE_FILE;

Or, if you want the file to have no header if no data records are retrieved:

CREATE OR REPLACE PROCEDURE WRITE_FILE
AS

       v_file  UTL_FILE.FILE_TYPE;

       directory         varchar2(50) := 'c:\write_file';
       file_name        varchar2(50) := 'w_file';

       ********************************
       lFirst               varchar2(3) := 'Yes';
       ********************************

      CURSOR c_data IS
          SELECT empno, ename
          FROM   emp;


BEGIN

v_file := sys.utl_file.fopen(directory, file_name||'.txt','w');


  FOR cur_rec IN c_data LOOP

   ********************************
    If lFirst='Yes'
    Then
         UTL_FILE.PUT_LINE(v_file,'EMPNO;ENAME');
        lFirst := 'No';
    End If;
    ********************************

    UTL_FILE.PUT_LINE(v_file,
                      cur_rec.empno    || ';' ||
                      cur_rec.ename);
  END LOOP;

  UTL_FILE.FCLOSE(v_file);

EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE(v_file);

END WRITE_FILE;

Regards

Chedgey
0
 
db_seniorAuthor Commented:
Ok,
But if I have this cursor:

SELECT rpad(empno,32,' ') empno, rpad(ename,32,' ') ename
FROM   emp;


UTL_FILE.PUT_LINE(v_file,'EMPNO;ENAME') not align correctly the columns EMPNO and ENAME with data

Have you any idea?
0
 
chedgeyCommented:
The header is only text so try:

UTL_FILE.PUT_LINE(v_file,'EMPNO                           ;ENAME');

Regards

Garnet
0
 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
Or:
UTL_FILE.PUT_LINE(v_file,rpad('EMPNO',32,' ')||rpad('ENAME',32,' '));

The utl_file.put_line command will not automatically align things from two different utl_file.put_line commands for you.  Each one will just do exactly what you tell it to do.  It will not know anything about any other utl_file.put_line commands that you have in the same procedure.  Keep in mind that this utility (utl_file.put_line) was developed for debugging purposes.  It can be used to generate ASCII files for reports, but it is certainly not the only way, and not necessarily the best or easiest way to create ASCII files or reports from an Oracle database.
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.