?
Solved

Output with UTL_FILE

Posted on 2006-05-19
6
Medium Priority
?
923 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:db_senior
5 Comments
 
LVL 6

Accepted Solution

by:
chedgey earned 252 total points
ID: 16716640
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
 
LVL 1

Author Comment

by:db_senior
ID: 16717022
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
 
LVL 6

Expert Comment

by:chedgey
ID: 16717036
The header is only text so try:

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

Regards

Garnet
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 248 total points
ID: 16719563
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
 
LVL 1

Expert Comment

by:Computer101
ID: 20228864
Forced accept.

Computer101
EE Admin
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month14 days, 21 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question