Home » SQL & PL/SQL » SQL & PL/SQL » DBMS_OUTPUT and UTL_FILE (merged)
DBMS_OUTPUT and UTL_FILE (merged) [message #384357] Tue, 03 February 2009 08:52 Go to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

Forgive me for asking what may seem like a daft question but as I was typing an email to my UAT guy I realised I wasn't quite sure of my answer.

We have a package that downloads data from our 10g database (Oracle Database 10g Release 10.2.0.4.0 - Production) to our 9i database (Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production). The 10g database is accessed via a database link, but the job is run from the machine where the 9i database resides.

The package is run via a UNIX shell script (AIX - ksh) using sqlplus
sqlplus -s ${logon} <<!!! >> $OUTFILE
 
   set serveroutput on size 1000000
   set verify off
   set term off
   set line 500
 
   Begin
       DBMS_OUTPUT.put_line('Start of download);
       download.process('P');
       DBMS_OUTPUT.put_line('End of download);
   Exception
      When others then
      DBMS_OUTPUT.put_line('Error in download');
   End;
/
!!!


The package itself uses DBMS_OUTPUT to write messages to the log file - $OUTFILE in the script - as it was developed prior to UTL_FILE being available. All works fine but I have always believed that once UTL_FILE became available we should use that in preference to DBMS_OUTPUT for writing log files.

However having a hunt around the internet I can't find anything that specifically tells me why. I know the limitation of the buffer for DBMS_OUTPUT but we have never reached this with this job (runs frequently so only ever downloads a small amount of records). Both packages can write to the same unix directory (there is no problem getting the directory authorised by my DBA).
I know that UTL_FILE can read a file as well but I don't need that functionality here.

My intention is to move to UTL_FILE anyway, but can anyone give me an insight into why its 'better' than DBMS_OUTPUT??

Thanks
Re: DBMS_OUTPUT and UTL_FILE [message #384363 is a reply to message #384357] Tue, 03 February 2009 09:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First did you read the usage and limitations of both packages in the documentation?

Regards
Michel
Re: DBMS_OUTPUT and UTL_FILE [message #384368 is a reply to message #384357] Tue, 03 February 2009 09:07 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
DBMS_OUTPUT displays the said message to screen. It is just a buffered information. You need to redirect the output to an OS file.
UTL_FILE writes directly to the OS file.
Re: DBMS_OUTPUT and UTL_FILE [message #384401 is a reply to message #384363] Tue, 03 February 2009 11:21 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Michel Cadot wrote on Tue, 03 February 2009 15:03
First did you read the usage and limitations of both packages in the documentation?

Regards
Michel



Well I tried hunting down info but I can't be sure I actually found the information you describe, most info I found just told me how to use the packages.

Can you point me in the right direction, thanks.
Re: DBMS_OUTPUT and UTL_FILE [message #384402 is a reply to message #384368] Tue, 03 February 2009 11:22 Go to previous message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Mahesh Rajendran wrote on Tue, 03 February 2009 15:07
DBMS_OUTPUT displays the said message to screen. It is just a buffered information. You need to redirect the output to an OS file.
UTL_FILE writes directly to the OS file.


Thanks for the info, in my situation that means the same result. I just wanted to know why I should prefer UTL_FILE to write my log files.
Previous Topic: How to Debugg
Next Topic: Find string in view Definitition (Merged N times)
Goto Forum:
  


Current Time: Mon Dec 05 19:05:01 CST 2016

Total time taken to generate the page: 0.05553 seconds