Produce text file with set positions using UTL FILE

[jaid89]

Hi,

Currently, my procedure (using utl_file) writes output to a text file that is pipe delimited. Text file has 25 fields. Is it possible, instead of having it pipe delimited, to write each field in a set column number (i.e. field 1 would start at col 1, field 2 would start at col 10, etc)?

Thanks!

 

[SantaMufasa]

Jaid,

Yes, you can output in "fixed-length" fashion by either using "utl_file.put" to stage up fixed-length fields, or by concatenating the fixed-length fields together before you use the "utl_file.put_line" packaged procedure for outputting.

As an example, let's assume last_name in columns 1-25 and first_name in columns 26-50. I would use this code fragment:

utl_file.put_line(substr(last_name||rep(' ',25),1,25))||
substr(first_name||rep(' ',25),1,25));

I concatenated to each output expression, a string of 25 blanks (using my personal, user-defined "rep" function), just in case the output expression is NULL.

The "rep" function replicates a string n-times. Here is the code should you wish to use it instead of typing in a quoted string of 25 blank spaces:

create or replace function rep
   (string_to_rep in varchar2, reps in number)
   return varchar2
is
begin
   return lpad(string_to_rep,reps*length(string_to_rep),string_to_rep);
end;

Here is a trivial example of rep's use:

SQL> select rep('Hello ',10) from dual;

REP('HELLO',10)
-----------------------------------------------------------
Hello Hello Hello Hello Hello Hello Hello Hello Hello Hello

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:06 (05Mar04) UTC (aka "GMT" and "Zulu"), 16:06 (05Mar04) Mountain Time)

 

[jaid89]

Thanks Dave! =)