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:

fnd_file.put_line(fnd_file.log,SQLERRM);

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;
DECLARE
l_customer varchar2(30);
BEGIN
dbms_output.enable(1000000);
DBMS_OUTPUT.PUT_LINE('REPORT: BEGIN SCRIPT - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));
DBMS_OUTPUT.PUT_LINE('REPORT: GET CUSTOMER - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));
BEGIN
 select customer_name
 into l_customer
 from ra_customers
 where customer_id = 10;
DBMS_OUTPUT.PUT_LINE('REPORT: CUSTOMER: ' || l_customer);
EXCEPTION
 when others then
  DBMS_OUTPUT.PUT_LINE('ERROR - CUSTOMER: ' || DBMS_UTILITY.FORMAT_ERROR_STACK);
END;
DBMS_OUTPUT.PUT_LINE('REPORT: FINISH CUSTOMER - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));
DBMS_OUTPUT.PUT_LINE('REPORT: END SCRIPT - ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS AM'));
END;
/

If the customer is not found, the Oracle message such as "ORA-01403: no data found" will be displayed such as:

SQL>/
REPORT: BEGIN SCRIPT - 12/19/2007 05:28:11 PM
REPORT: GET CUSTOMER - 12/19/2007 05:28:11 PM
ERROR - CUSTOMER: ORA-01403: no data found
REPORT: FINISH CUSTOMER - 12/19/2007 05:28:11 PM
REPORT: END SCRIPT - 12/19/2007 05:28:11 PM
PL/SQL procedure successfully completed.
SQL>

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.

function get_dbms_output return varchar2 is
outtab dbms_output.chararr;
fetchln INTEGER := 1000;
l_output varchar2(32000);
l_count number := 0;
BEGIN
dbms_output.get_lines(outtab, fetchln);
l_output := '--- BEGIN DBMS DEBUG ' || TO_CHAR(fetchln) || ' LINES ---' || CHR(10);
FOR i IN 1 .. fetchln LOOP
l_output := l_output || outtab(i) || CHR(10);
dbms_output.put_line(outtab(i));
END LOOP;
RETURN l_output;
END;

This function captures all the data from the DBMS buffer and copies it to a local variable before writing the data back to the DBMS buffer.  Now from the concurrent program code, simply issue:

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:

SEND_MAIL(
msg_to => oracle-support@company.com
,msg_subject => 'Debug Information'
,msg_text => l_output
);

Now, the support staff can quickly diagnose and resolve production errors produced from the Oracle Applications Concurrent Jobs.  This improves productivity and error resolution.