DBPROC Job Message Output

Products

• CA Workload Automation DE

Issue/Introduction

We have a DBPROC Job where we are executing a Procedure on remote Oracle Database. When we run the job, it displays a message on successful completion. Is it possible to output the message in the CAWA email we receive when the job completes successfully?

 

Environment

Release : 12.3

Resolution

The example below offers insight into how you can include the stored procedure's output in an email notification sent when the job completes.

 

 

First, this is the stored procedure used while testing:

CREATE OR REPLACE PROCEDURE procOneOUTParameter(outParam1 OUT VARCHAR2)
IS
BEGIN
  
  outParam1 := 'Hello World OUT parameter';END;
/

 

2. The DB Stored Procedure was setup as follows:

• Basic:
• • Procedure Name: procOneOUTParameter
  • Success criteria (Optional): .*Hello.* 
• Parameters:
  • Parameter: outParam1 out VARCHAR2
  • Value: (empty/blank)
• Notifications -> Email -> uncheck "Use Application-level defaults" -> New
  • Monitor states: Complete
  • To: Add my email address
  • Message: %(WOB._lstatus)

 

When I execute the job I am able to see the output returned by the Stored Procedure. Example: 

outParam1=Hello World OUT parameter