1. UNIFYNow Forum
  2. Troubleshooting
0
Published

Database Execute fails to execute Oracle stored procedure

Beau Harrison (Senior Product Software Engineer) 8 years ago • updated by anonymous 8 years ago • 1

Description

The Database Execute Query Operation, in combination with the Oracle Database Agent, fails to execute a stored procedure.

Affected Software

  • FIM Event Broker
  • Oracle database

Symptom

When executing a stored procedure using the Database Execute Query Operation against the Oracle Database Agent, the following error (or similar) appears: 

Operation c8dc0587-f197-4cb9-b15d-243629bad723 failed in operation list with id c1c7e163-d3a0-49ad-997f-ee818cf8d8d0 for the following reason. This is retry number 0: Oracle.DataAccess.Client.OracleException ORA-00900: invalid SQL statement at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at Unify.Framework.Data.AdoNetDataControl.ExecuteNonQuery(String commandText, IEnumerable`1 parameters, CommandType commandType)
at Unify.Product.EventBroker.OperationListExecutorBase.RunNextOperations(IEnumerator`1 operationEnumerator)

Solution

This error means that either the service account for the Oracle Database Agent does not have correct permission, or that the format of the operation statement is not correct.

The correct format is similar to the following: 

BEGIN
    SCHEMA_NAME.STORED_PROCEDURE_NAME();
END

To grant the stored procedure to the service account: 

grant EXECUTE on "SCHEMA_NAME"."STORED_PROCEDURE_NAME" to "EBUSER";
Published
Beau Harrison (Senior Product Software Engineer) 8 years ago

Customer support service by UserEcho