Write data to flat file in a loop using UTL_FILE

Question

I am attempting to export the employee names in table employees into a flat file. The flat file should have the following structure:

HEADER 
DETAILS JACK
DETAILS JUNE
TRAILER

What I am struggling with is to how I can run this in a loop to store the names in single rows in the same file. My current script is only exporting one name at a time into separate files. Since the filename remains the same, the files are overwriting each time the procedure is executed.

Please note that I wish to have filename as a variable if possible.

Create table Employees (Id number(10),Name varchar(40))

Insert into Employees values (1,'JOHN');
Insert into Employees values (2,'JACK');
Insert into Employees values (3,'JUNE');
-----------------------

CREATE OR REPLACE Procedure PRINT_NAMES(aId       in Employees.Id%Type,
                                        aFileName in varchar2)

 Is

  fDirectory varchar(30) := 'SB1KK_TEMP';
  fName      Employees.name%Type;

  pFile Utl_File.file_type;
  fLine Varchar2(1024);

Begin



  pFile := UTL_FILE.fopen(fDirectory, aFileName, 'w');

  --File Header
  fLine := RPAD('HEADER', 10) || To_char(trunc(sysdate), 'yyyymmdd') || '000000';
  UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));

  --File Details - This Section must be run in a loop
  Select Name into fName From Employees where id = aId; 
  fLine := RPAD('DETAILS', 10) || RPAD(' ', 50) ||
           To_char(trunc(sysdate), 'yyyymmdd') || RPAD(fName, 11);
  UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));


  --File Trailer
  fLine := RPAD('TRAILER', 10) || To_char(trunc(sysdate), 'yyyymmdd') || '000000';
  UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));
  UTL_FILE.fclose(pFile);

End;
/

The stored procedure is run in a loop. The file TMP_LOG.txt is created over and over for each person in table employees.

Begin
  For IDS in (Select * From Employees Where id in (2,3))
    Loop
  PRINT_NAMES(aId => IDS.ID, aFileName => 'TMP_LOG.TXT');
    End Loop;
End;

Alex Poole · Accepted Answer · 2016-09-07 10:50:57Z

2

You need to do the loop inside your procedure, as a comment in your code already suggests, and not when you call the procedure. But that means you need to pass multiple IDs in. A simple way to do that, if you're allowed to create new user-defined types, is with a table collection:

CREATE Type EmployeeIds as Table of Number(10)
/

Then your procedure declaration becomes:

CREATE OR REPLACE Procedure PRINT_NAMES(aIds      in EmployeeIds,
                                        aFileName in varchar2)

and you can do a cursor loop:

  For IDS in (Select * From Employees Where ID member of aIds) Loop
    fLine := RPAD('DETAILS', 10) || RPAD(' ', 50) ||
             To_char(trunc(sysdate), 'yyyymmdd') || RPAD(IDS.Name, 11);
    UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));
  End Loop;

You don't need the fname local variable.

So altogether that becomes:

CREATE OR REPLACE Procedure PRINT_NAMES(aIds      in EmployeeIds,
                                        aFileName in varchar2)

 Is

  fDirectory varchar(30) := 'SB1KK_TEMP';

  pFile Utl_File.file_type;
  fLine Varchar2(1024);

Begin



  pFile := UTL_FILE.fopen(fDirectory, aFileName, 'w');

  --File Header
  fLine := RPAD('HEADER', 10) || To_char(trunc(sysdate), 'yyyymmdd') || '000000';
  UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));

  --File Details - This Section must be run in a loop
  For IDS in (Select * From Employees Where ID member of aIds) Loop
    fLine := RPAD('DETAILS', 10) || RPAD(' ', 50) ||
             To_char(trunc(sysdate), 'yyyymmdd') || RPAD(IDS.Name, 11);
    UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));
  End Loop;

  --File Trailer
  fLine := RPAD('TRAILER', 10) || To_char(trunc(sysdate), 'yyyymmdd') || '000000';
  UTL_FILE.put_line(pFile, convert(fLine, 'WE8ISO8859P1', 'UTF8'));
  UTL_FILE.fclose(pFile);

End;
/

And then you call it with a collection of IDs, using the same UDT:

Begin
  PRINT_NAMES(aIds => EmployeeIds(2,3), aFileName => 'TMP_LOG.TXT');
End;
/

with produces a file containing:

HEADER    20160907000000
DETAILS                                                     20160907JACK
DETAILS                                                     20160907JUNE
TRAILER   20160907000000

You can have a collection variable that you populate and then pass in to the procedure instead, e.g.:

Declare
  lIds EmployeeIds;
Begin
  -- populate the collection from the table using criteria you need
  Select ID Bulk Collect Into lIds From EmployeesX Where ID in (2,3);
  PRINT_NAMES(aIds => lIds, aFileName => 'TMP_LOG.TXT');
End;
/

... using whatever filters you want to pick the IDs to be included.

edited Sep 7, 2016 at 10:50

answered Sep 7, 2016 at 10:29

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

MrM Over a year ago

Thanks Alex! Exactly what I was looking for. Let’s say that the employees table holds thousands of rows. How do I pass them in the collection of ids without having to list evry single one?

Alex Poole Over a year ago

@user3651825 - that depends on the criteria you'll use to select them. You can have your calling block populate a collection from a query and then pass that. I've added a demo getting the same rows, which is closer to your original calling loop I think, but that only makes sense if you're really using some other column to pick the IDs.

MrM Over a year ago

I will have to read up on working with collections and user defined datatypes. Thanks again! No further questions.

Collectives™ on Stack Overflow

Write data to flat file in a loop using UTL_FILE

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related