OraFAQ Forum: SQL & PL/SQL » To Log DBMS in a file from procedure

Home » SQL & PL/SQL » SQL & PL/SQL » To Log DBMS in a file from procedure

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To Log DBMS in a file from procedure [message #646725]

Thu, 07 January 2016 08:16

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

Hi Guys,

Is there a way to log DBMS_OUTPUT.PUT_LINE from procedure to log file

procedure test(p_in date,p1_in date)
is
begin
DBMS_OUTPUT.PUT_LINE('Process started');
.
.
.
end

Report message to a moderator

Re: To Log DBMS in a file from procedure [message #646726 is a reply to message #646725]

Thu, 07 January 2016 08:24

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Yes, write a procedure that calls dbms_output.get_lines and have that write to a file. Call that at the end of your process.

Report message to a moderator

Re: To Log DBMS in a file from procedure [message #646727 is a reply to message #646726]

Thu, 07 January 2016 08:29

rohit_shinez
Messages: 139
Registered: January 2015

Senior Member

How i need to something like this

dbms_output.get_line('process started')

How will i call it and write to specific path log file procedure_test.log

Report message to a moderator

Re: To Log DBMS in a file from procedure [message #646730 is a reply to message #646727]

Thu, 07 January 2016 08:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://docs.oracle.com/database/121/ARPLS/u_file.htm#ARPLS069

Report message to a moderator

Re: To Log DBMS in a file from procedure [message #646736 is a reply to message #646730]

Thu, 07 January 2016 09:29

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

http://docs.oracle.com/database/121/ARPLS/d_output.htm#ARPLS036

Report message to a moderator

Re: To Log DBMS in a file from procedure [message #646738 is a reply to message #646725]

Thu, 07 January 2016 10:19

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Now the question raises: what is your actual goal?
To spool what is displayed from SQ*Plus when recorded using dbsm_output?
To log into a file (from what?) what was recorded in dbms_output by called procedures?
To log into a file what do your procedures?
...?

Or in simple words, why your test procedure uses DBMS_OUTPUT which purpose is to be displayed on a screen and not UTL_FILE which purpose is to write into a file?

[Updated on: Fri, 08 January 2016 14:30]

Report message to a moderator

Re: To Log DBMS in a file from procedure [message #646789 is a reply to message #646738]

Fri, 08 January 2016 13:56

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Our make a logging procedure that uses pragma autonomous_transaction; to store the logging into a log table so no matter what happens to the procedure, the log is saved. If you use DBMS_OUTPUT, it is not displayed if the job aborts.

Report message to a moderator

Re: To Log DBMS in a file from procedure [message #646792 is a reply to message #646789]

Sat, 09 January 2016 01:25

garan
Messages: 27
Registered: January 2016

Junior Member

Hi

yes create a log table and write a separate procedure say APPL_LOG for logging the details as autonomous transaction and call that procedure for both information as well as error logging

Ex:

PROCEDURE P1 IS
BEGIN
APPL_LOG('PROCEDURE P1 STARTED')
------------
------------
EXCEPTION
WHEN OTHERS THEN
APPL_LOG('ERROR OCCURED IN P1');
END P1;

garan

Report message to a moderator

Re: To Log DBMS in a file from procedure [message #646793 is a reply to message #646792]

Sat, 09 January 2016 01:40

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes or no, if the result must be in a file per specifications then putting it in a table does not fit these ones.

In the end, WHEN OTHERS must be followed by RAISE (read the link).

Report message to a moderator

Re: To Log DBMS in a file from procedure [message #646874 is a reply to message #646793]

Tue, 12 January 2016 12:00

Bill B
Messages: 1971
Registered: December 2004

Senior Member

If it has to be in a flat file then use utl_file in the logging routine and everything will be saved in a flat file.

Report message to a moderator

Previous Topic:	Not able to create the Materialized view
Next Topic:	how to write below procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 00:13:48 CDT 2024