ORACLE how to print text in a new line

Question

Basically its a letter printing in text I'm trying to do via an Oracle PL/SQL Procedure.

My Code below: -

DECLARE
       out_file UTL_FILE.file_type;    lv_file VARCHAR2 (200) DEFAULT TO_CHAR (SYSDATE, 'yyyymmddhhmiss')||'.txt' ;LVSQL varchar2(4000);    lV_TXT varchar2(4000);lv_txt1 varchar2(4000);lv_final_str varchar2(4000); BEGIN    out_file := UTL_FILE.fopen ('SPOOL_DIR', lv_file, 'W');    for i in 1..5 loop
       begin
            select tn,tt into lv_txt,lv_txt1 from 
            (SELECT rownum r, TNAME tn ,TABTYPE tt FROM tab) where r=j;
            lv_final_str:=lV_TXT||chr(9)||lv_txt1||CHR(13);
            utl_file.put_line(Out_file,lv_final_str );
       end;    end loop;

       UTL_FILE.fclose (out_file);
    END;

I'm able to print the values in next next line by concatenating the lv_final_str with CHR(13) and that I use the *utl_file.put_line(Out_file,lv_final_str );* within the loop

I want to do the same thing by collecting the text in a variable inside the loop. and writing the value of the variable outside the loop, using the below code.

DECLARE
   out_file UTL_FILE.file_type;
   lv_file VARCHAR2 (200) DEFAULT TO_CHAR (SYSDATE, 'yyyymmddhhmiss')||'.txt' ;LVSQL varchar2(4000);
   lV_TXT varchar2(4000);lv_txt1 varchar2(4000);tmpvar varchar2(4000);lv_build_str varchar2(4000);lv_final_str varchar2(4000);
BEGIN
   --SELECT TO_CHAR (SYSDATE, 'yyyymmddhhmiss') INTO lv_file FROM DUAL;
   out_file := UTL_FILE.fopen ('SPOOL_DIR', lv_file, 'W');
   for i in 1..5 loop
       begin
            select tn,tt into lv_txt,lv_txt1 from 
            (SELECT rownum r, TNAME tn ,TABTYPE tt FROM tab) where r=i;
            tmpvar:=lV_TXT||chr(9)||lv_txt1||CHR(13);
            lv_build_str := lv_build_str || chr(9)||tmpvar;
--            utl_file.put_line(Out_file,lv_final_str );
       end;
   end loop;
   lv_final_str:=lv_build_str;
    utl_file.put_line(Out_file,lv_final_str );
   UTL_FILE.fclose (out_file);
END;

how to do this, please help. I used chr(10) as well, it is not printing in new line. if you observe chr(9) which is used to print tab space is working well. only next line chr(10) or chr(13) is not working. Why...?

any help.. I'm trying since last 3 days... please help.

Are you writing to a file in a Unix environment, or Windows? Unix uses just a single LF character (10) whereas Windows expects a CR followed by LF (13,10). — Jeffrey Kemp, Jul 19 '13 at 05:07
@Jeffrey Kemp _Server OS_ is **Solaris 5.10** and _database_ is **Oracle 9.2.0.8.0** The file is written in solaris filesystem only which is oracle directory as well. — rocky, Jul 19 '13 at 05:54
@JeffreyKemp: I think I also should mention this, End Users access it via \\server-path\file_name.txt for printing, since its a samba directory can be accessed via any clent OS. in our case usually clients PC would be in Windows. — rocky, Jul 19 '13 at 06:05
@JeffreyKemp Thank you. It worked out bby appending both the chr(13)and chr(10) like this ||CHR(13)||CHR(10) together. Thanks for the guidance. — rocky, Jul 19 '13 at 09:13

score 2 · Accepted Answer · answered Jul 19 '13 at 09:36

2

It depends on whether you're writing to a file for a Unix environment, or Windows.

Unix uses just a single LF character CHR(10) whereas Windows expects a CR followed by LF CHR(13)||CHR(10).

answered Jul 19 '13 at 09:36

Jeffrey Kemp

59,135
14
106
158

ORACLE how to print text in a new line

1 Answers1