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;

Answer 1

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.