After switching Oracle agent to GENERIC, DBMS_OUTPUT.put_line() no longer works

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

A SQL agent that was originally set as type=ORACLE or type=ORACLE_OCI and connection objects with type Oracle is now set up as type=GENERIC or connection type Generic JDBC.  The following used to work in the job:

 

BEGIN

dbms_output.put_line('Hello World');

END;@

 

and the output would be:

Hello World

 

After updating the type to GENERIC, there is no output.

Environment

Agent: Database agent for jobs

Cause

This is caused by a limitation with the jdbc thin client using the generic jdbc driver/type

Resolution

This behavior is caused by a limitation with the jdbc driver.  There are some ways around this limitation:

1. Continue with Generic JDBC and Re-tool SQL Jobs

   You can retain the Generic JDBC connection objects.  To bypass the JDBC limitation with polling DBMS_OUTPUT, the SQL jobs would need to be re-tooled to use SELECT CASE statements instead of IF/THEN statements with DBMS_OUTPUT:

   SELECT 
      CASE 
         WHEN COUNT(*) > 0 THEN 'SUCCEED' 
         ELSE 'FAILED' 
      END AS EXECUTION_STATUS 
   from your_table 
   where LOAD_DATE = TRUNC(SYSDATE);

2. Use the Native "Oracle" Connection Type

   Setting the database type to "Oracle" in the connection object (and agent) also utilizes the thin client, which could help to avoid re-tooling SQL jobs as mentioned in Option 1 entirely.  However, there are a few caveats to this approach including it not working on RAC

   * RAC Compatibility: This native method generally does not work with true RAC connections.
   
   * Re-work: It requires updating the agent and connection objects to be type ORACLE, which is intensive if moving from the Generic JDBC objects.  However, directly from ORACLE OCI to the ORACLE type can be done fully with the transport case and db change utility.

3. Hybrid Approach

   If the jobs that use DBMS_OUTPUT do not use a true RAC connection (something you will want to confirm with your DBAs), these could be put on their own agent and connection objects that use the ORACLE type for both.