Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE in PL/SQl block
UTL_FILE in PL/SQl block [message #268967] Thu, 20 September 2007 04:36 Go to next message
oracle_dev
Messages: 24
Registered: April 2007
Junior Member
Hi,
I want to write the no. of records updated in utl_file for every 1000 rows update.

For update cursor is used.
I have given condition inside the cursor loop properly.

But I am able to see the update in the flat file only after exiting sql session When the procedure ends.
Re: UTL_FILE in PL/SQl block [message #268970 is a reply to message #268967] Thu, 20 September 2007 04:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try using UTL_FILE.FLUSH
Re: UTL_FILE in PL/SQl block [message #269487 is a reply to message #268967] Sat, 22 September 2007 15:43 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
My guess will be, you have not closed the file in your proc or anonymous block. Use flush if you really have to, because, by default flush is defaulted to true when you exceed the buffer limit.

Quote:

SQL> declare
2 my_file utl_file.file_type;
3 begin
4 my_file := utl_file.fopen('AR_TEMP','BUFFERFLUSH.txt','w');
5 utl_file.put_line(my_file,'HELLO WORLD');
6 end;
7 /

PL/SQL procedure successfully completed.

dir c:\temp\*.txt

Directory of C:\TEMP
22/09/2007 21:40 0 BUFFERFLUSH.txt

21:40:30 SQL> declare
21:40:59 2 my_file utl_file.file_type;
21:40:59 3 begin
21:40:59 4 my_file := utl_file.fopen('AR_TEMP','BUFFERFLUSH.txt','w');
21:40:59 5 utl_file.put_line(my_file,'HELLO WORLD');
21:40:59 6 utl_file.fclose(my_file);
21:40:59 7 end;
21:40:59 8 /

PL/SQL procedure successfully completed.

21:41:00 SQL>

dir c:\temp\*.txt

Directory of C:\TEMP

22/09/2007 21:41 13 BUFFERFLUSH.txt




Regards

Raj
Previous Topic: Cursor with %ROWTYPE
Next Topic: change column alignment
Goto Forum:
  


Current Time: Wed Dec 07 20:26:59 CST 2016

Total time taken to generate the page: 0.06874 seconds