No error when buffer limit is reached

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

The following causes database job to hang forever:

bms_output.put_line('grant select on "' || recTables.owner || '"."' ||

Error report - ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes

Cause

The DB agent is only able to perform plain JDBC functions.

Environment

Release: AUTWAB99000-11.2-Automic Workload Automation-Base Edition
Component:

Resolution

Workaround:
Use the dbms_output.enable(size) function to override CA Automic setting for the buffer size without causing a job error. 

The DB agent is only able to use plain JDBC functions.

This can be tested by running a simple job on the AE database:

!if not specified this is the default: 
!SQL_ON_ERROR ACTION="ABEND"; 
!if specified error will be ignored 
!SQL_ON_ERROR ACTION="RESUME"; 
select * from oh where oh_idnr = 5; 
select * from ahx wherer ahx = 5; 
select * from oh where oh_idnr = 5; 

This results with the following - 

2018-07-04 14:38:47 - U02000005 Job 'TH.JOBS.SQL.ERROR' with RunID '335847692' started. 
2018-07-04 14:38:47 - U02012001 Successfully established connection to 'obelix:1521/AE' with user 'uc4'. 
2018-07-04 14:38:47 Oracle 
2018-07-04 14:38:47 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 
2018-07-04 14:38:47 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
2018-07-04 14:38:47 - U02012014 JDBC Driver version: 12.1.0.2.0 
2018-07-04 14:38:47 select * from oh where oh_idnr = 5 
2018-07-04 14:38:47 - U02012003 1 row(s) affected 
2018-07-04 14:38:47 select * from ahx wherer ahx = 5 
2018-07-04 14:38:47 ORA-00933: SQL command was not completed correctly 

2018-07-04 14:38:47 - U02004025 Job-script execution was aborted. 
2018-07-04 14:38:47 - U02012006 SQL Rollback executed 

and if you enable the RESUME option - 

2018-07-04 14:43:52 - U02000005 Job 'TH.JOBS.SQL.ERROR' with RunID '335849980' started. 
2018-07-04 14:43:52 - U02012001 Successfully established connection to 'obelix:1521/AE' with user 'uc4'. 
2018-07-04 14:43:52 Oracle 
2018-07-04 14:43:52 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 
2018-07-04 14:43:52 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 
2018-07-04 14:43:52 - U02012014 JDBC Driver version: 12.1.0.2.0 
2018-07-04 14:43:52 SQL_ON_ERROR ACTION="RESUME" 
2018-07-04 14:43:52 select * from oh where oh_idnr = 5 
2018-07-04 14:43:52 - U02012003 1 row(s) affected 
2018-07-04 14:43:52 select * from ahx wherer ahx = 5 
2018-07-04 14:43:52 ORA-00933: SQL command was not completed correctly 
2018-07-04 14:43:52 select * from oh where oh_idnr = 5 
2018-07-04 14:43:52 - U02012003 1 row(s) affected 
2018-07-04 14:43:52 - U02004026 Job script ended normally.