Home » SQL & PL/SQL » SQL & PL/SQL » Writing File in PL/SQL (newline)
Writing File in PL/SQL (newline) [message #435038] Mon, 14 December 2009 00:00 Go to next message
usman.javaid
Messages: 7
Registered: December 2009
Location: Pakistan
Junior Member

I am writing a file in pl/sql and saving it in *.csv format but all the records are coming in same line but i want each record in separate line. i am using FND_FILE.PUT_LINE (FND_FILE.OUTPUT,........) to write file and using CHR(13)||CHR(10) for going to next line but it is not working. Can anyone help?
Re: Writing File in PL/SQL (newline) [message #435039 is a reply to message #435038] Mon, 14 December 2009 00:07 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Try UTL_FILE.NEW_LINE.

regards,
Delna
Re: Writing File in PL/SQL (newline) [message #435040 is a reply to message #435038] Mon, 14 December 2009 00:12 Go to previous messageGo to next message
usman.javaid
Messages: 7
Registered: December 2009
Location: Pakistan
Junior Member

MY CODE IS

FND_FILE.PUT_LINE (
FND_FILE.OUTPUT,
'"'
|| NVL (abc0, 0)
|| '"'
|| NVL (abc1, 0)
|| '"'
|| NVL (abc11, 0)
|| '"'
|| NVL (abc111, 0)
|| '"'
|| NVL (abc1111, 0)
|| '"'
|| NVL (abc11111, 0)
|| '"'
|| NVL (abc111111, 0)
|| '"'
|| NVL (v_Carryover, 0)
|| '"'
|| CHR(13)||CHR(10)
);
Re: Writing File in PL/SQL (newline) [message #435042 is a reply to message #435038] Mon, 14 December 2009 00:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
put_line should add a line break (if I am not mistaken). Adding chr(13)||chr(10) should add an extra empty line.
Maybe you can show us a snippet of your code plus part of the outcome?
Re: Writing File in PL/SQL (newline) [message #435043 is a reply to message #435039] Mon, 14 December 2009 00:13 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>Try UTL_FILE.NEW_LINE.
what does above have to do with FND_FILE? (other than _FILE is at the end of each)?
Re: Writing File in PL/SQL (newline) [message #435049 is a reply to message #435042] Mon, 14 December 2009 00:36 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello Frank sir
Quote:
put_line should add a line break


As per my experience, PUT_LINE doesn't add any line breaks to the file in which we are writing data.

Please correct me, if I am wrong...

regards,
Delna
Re: Writing File in PL/SQL (newline) [message #435075 is a reply to message #435049] Mon, 14 December 2009 03:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You must be thinking of UTL_FILE.PUT.

From the documentation for UTL_FILE.PUT_LINE:
PUT_LINE Procedure

This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be open for write operations. PUT_LINE terminates the line with the platform-specific line terminator character or characters
Re: Writing File in PL/SQL (newline) [message #435088 is a reply to message #435075] Mon, 14 December 2009 04:16 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
JRowbottom sir

Don't know exactly, what problem I was facing and why couldn't I solve the problem at the time when I was using UTL_FILE.PUT_LINE, but following is the code which shows the correct behaviour of PUT_LINE.

SQL>select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

5 rows selected.

SQL>create directory my_dir as 'd:\my_dir';

Directory created.

SQL>declare
  2      v_handle utl_file.file_type;
  3  begin
  4      v_handle := utl_file.fopen('MY_DIR','abc.txt','w',1000);
  5      
  6      if(utl_file.is_open(v_handle)) then
  7          utl_file.put_line(v_handle, 'Hi!', false);
  8          utl_file.put_line(v_handle, 'This is Oracle PUT_LINE test.', false);
  9          utl_file.put_line(v_handle, 'Thanks...', false);
 10          
 11          utl_file.fflush(v_handle);
 12          
 13          utl_file.fclose(v_handle);
 14      end if;
 15  end;
 16  /

PL/SQL procedure successfully completed.


And following is the file content.
Quote:
Hi!
This is Oracle PUT_LINE test.
Thanks...


regards,
Delna
Re: Writing File in PL/SQL (newline) [message #435089 is a reply to message #435038] Mon, 14 December 2009 04:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What exactly is FND_FILE?
Re: Writing File in PL/SQL (newline) [message #435090 is a reply to message #435089] Mon, 14 December 2009 04:22 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
JRowbottom wrote on Mon, 14 December 2009 11:18
What exactly is FND_FILE?

Seems to be an eBusinessSuite API to UTL_FILE (Found here)
Previous Topic: identify negative values
Next Topic: Filter the Character
Goto Forum:
  


Current Time: Sat Oct 01 12:39:55 CDT 2016

Total time taken to generate the page: 0.08522 seconds