Skip to Main Content
  • Questions
  • Convert CrLf to Lf with utl_file.put_line

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Andrey.

Asked: December 17, 2015 - 6:02 pm UTC

Last updated: April 27, 2021 - 6:10 am UTC

Version: EMPLOYEES

Viewed 1000+ times

You Asked

Hi, Tom!

I have next pl/sql code for export of data from oracle to file in format of raw:

CREATE OR REPLACE PROCEDURE my_proc_export_to_csv is
begin
declare
file_type utl_file.file_type;
cursor row_cur is select * from sys.my_new_dir;
data_export sys.my_new_dir%rowtype;
begin
file_type:=utl_file.fopen('MY_DIR','DATA_EXPORT.RAW','w');
open row_cur;
loop
fetch row_cur into data_export;
exit when row_cur%notfound;
dbms_output.put_line(data_export.contact_id||' '||data_export.phone);
utl_file.put_line(file_type,data_export.contact_id||' '||data_export.phone);
end loop;
close row_cur;
utl_file.fclose(file_type);
end;
end my_proc_export_to_csv;

when I exported file and opened it, I saw at the end of the line crlf:

1313131 HoldersInCure4 CrLf

but i need it in Lf:

1313131 HoldersInCure4 Lf

how i can get it?

Help me plz.

and Connor said...

put_line chooses the end of line character(s) based on the platform. But you could do it yourself, ie,

utl_file.put(l_text||chr(10));

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks, Connor !

Andrey -, December 18, 2015 - 1:04 pm UTC

It didn't work ( May be because I have Windows? But what can i do if my platform is Windows?
Connor McDonald
December 19, 2015 - 4:20 am UTC

OK - do it in raw mode. I just tried on Windows

declare
file_type utl_file.file_type;
begin
file_type:=utl_file.fopen('TEMP','DATA.RAW','wb');
for i in 1 .. 10 loop
utl_file.put_raw(file_type,utl_raw.cast_to_raw('Hello'||chr(10)));
end loop;
utl_file.fclose(file_type);
end;
/


btw, I should have said before - DONT use

open cursor
loop
fetch
exit when not found
end loop;

Way too slow...change it to

for i in cursor loop
end loop;

Much faster.

nejia, April 26, 2021 - 9:24 am UTC

thank you I changed CHR(13) by CHR (10) and it worked
Connor McDonald
April 27, 2021 - 6:10 am UTC

glad it worked out

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library