- Oracle-Developer.com -
Home | Discussion Forums (Get expert advice) |
About Us | Links | Job Openings
Oracle Developer > Scripts > Using DBMS_OUTPUT for Logging
|Using Logging in Oracle Apps Packages
When it comes time to implementing transactional or error logging in Oracle Applications Custom packages or scripts, you have several options including using the FND_FILE package. For example, if you wanted to capture a message about an Oracle Exception, you could use:
Naturally, you would want to include additional information about the error in order for the message to be useful to the support staff. However, this package will only display the error by viewing the Concurrent job output.
You could also implement a custom procedure in Oracle to send an SMTP message (email) dynamically to get a more immediate notification. However, if the procedure is performing a significant number of transactions, such as calling several different API's per transaction, it is critical to be able to trace the source of the error and view what led up to the error. Otherwise, the support staff will be trying to debug the problem with only a minimal amount of debugging information. This reduces productivity and can delay the resolution of production problems.
How DBMS_OUTPUT is Used
Almost every experienced developer knows how to use DBMS_OUTPUT to view logging information using development and debugging. DBMS_OUTPUT sends a message to the session that can be viewed in SQL Plus and other programs by setting serveroutput on at the session level. The following script demonstrates the use of DBMS_OUTPUT to capture some logging:
set serveroutput on;
If the customer is not found, the Oracle message such as "ORA-01403: no data found" will be displayed such as:
Now, from the DBMS_OUTPUT it is easy to determine where the point of failure occurred. However, this output is not going to be available when the program is defined and executed as a concurrent program in Oracle Applications Concurrent Manager. This output could be very valuable to finding exactly where this program has the error and what caused it. This information could easily be placed on the concurrent log but then that would require someone to lookup the concurrent job and view the output. This could be an inefficient process since a concurrent job can be run many times and it does not notify the support staff immediately.
There is a fairly simple way to capture all of the DBMS output and place it in an email to the support staff without having to implement a great deal of additional code.
Capturing the DBMS Output and sending it by Email
The DBMS Output buffer is simply a stack that stores messages and then relays those messages to the session if the server output is enabled. In order to capture this output real-time from a concurrent job and send it to the support staff by email, a simple procedure can be written to save the DBMS output message stack to a local varchar2 and then email that local varchar2 to the support staff.
return varchar2 is
l_output := get_dbms_output;
The l_output is a varchar2 local variable that now holds all of the DBMS_OUTPUT buffer data at the time that the assignment is issued. This information can easily be emailed to the support staff by using a custom send mail program such as:
Now, the support staff can quickly diagnose and resolve
production errors produced from the Oracle Applications Concurrent Jobs.
This improves productivity and error resolution.
Owned and Operated by Varun Jain, Inc, www.varunjaininc.com
Copyright ©2007 Oracle-Developer.com