Oracle stored procedures can be executed using escalations using Direct SQL action under if action or Else action tab. We can use the Direct SQL action to submit any legal SQL command to the database. With the Direct SQL action, the SQL command is not expected to return data. you can define a Direct SQL action to perform only inserts or updates to a database.

Normally if the command works in SQL*Plus it will work in direct SQL as AR System passes direct SQL commands to the database without checking the syntax, all commands are submitted to the database. You must make sure that all submitted commands achieve the needed result.

To run a stored procedure in SQLPlus the command is something like this.

SQL> EXECUTE procedure_name('passed_value');

This does not work in direct SQL. The Oracle error returned is ORA-00900 which indicates the command is not valid for the command set. You can use following command in the Remedy Direct SQL command line.

BEGIN procedure_name('passed_value'); END;

This will allow the stored procedure to run from the Direct SQL command line of the Remedy active link/escalation.

The reason for this is that SQL*Plus uses an extended command set that is not available in the Oracle API that Remedy uses to connect to the database. The EXECUTE call is equivalent to the BEGIN ... END; call issued from an anonymous PL/SQL block. Remedy can use the anonymous PL/SQL block to call the procedure.

We need to keep in mind that Oracle stored procedures do not return values. This will prevent you from using stored procedures in set fields actions.But complete stored procedure can not be both created and executed through a Direct SQL action as first time escalation qualification satisfies the escalation will create the procedure, but the next time Oracle will throw error as it will try to create procedure with the same name.

If you wants to perform some operations from the procedure and get the result then a dummy table in database should be used wherein the intermediate results will be stored using Insert from the procedure in Direct SQL and then you can use Set Fields action by selecting the SQL from 'read value from' drop down field to query the database for information and then use the returned value to set a field.