No error when buffer limit is reached
Article ID: 104903
Updated On:
Products
CA Automic Workload Automation - Automation Engine
AUTOMIC WORKLOAD AUTOMATION
Issue/Introduction
The following causes database job to hang forever:
Error report - ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
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:
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.
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.
Feedback
Yes
No
Powered by
