The SQL agent is unable to invoke stored procedure

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

The SQL agent is able to run select statements but is unable to run stored procedures such as:

DBMS_MVIEW.REFRESH( LIST -> 'CM_CCB_ADJ_CHAR_MV, METHOD => 'C' )

When attempting to run it, it fails with the following error:

ORA-00900: invalid SQL statement.

Environment

Release : 12.3

Component : AUTOMATION ENGINE

Resolution

The following lines needs to be added

SQL_SET_STATEMENT_TERMINATOR TERM="@";

BEGIN

(SQL STORE PROCEDURE GOES HERE)

END;

@

 

Additional Information

The @ or the symbol used in the first line is very important as it denotes the end of the statement.