27 Jun 2017 15:59:33,232 [PVM:DB Process Worker-4] [ERROR] com.tibco.bx.core.BxSystemImpl - [ERROR] - {BX_TASK_REPORTED_ERROR} - BX-800009: Task reported an error �{extendedMessage=`Exception Cause is [null]   Exception Message is [INTEGER_ARRAY]   Exception Stack is [java.lang.Exception: INTEGER_ARRAY
    at com.tibco.bx.extension.database.core.JDBCCommon.sqlTypeFromString(JDBCCommon.java:313)
    at com.tibco.bx.extension.database.core.JDBCStatementActivity.evalPreparedStatement(JDBCStatementActivity.java:475)
    at com.tibco.bx.extension.database.core.JDBCStatementActivity.performDatabaseOperation(JDBCStatementActivity.java:111)
    at com.tibco.bx.extension.database.core.JDBCActivity.eval(JDBCActivity.java:1050)
    at com.tibco.bx.extension.database.core.DatabaseTaskActivity.doWork(DatabaseTaskActivity.java:179)
    at com.tibco.bx.extension.database.core.DatabaseTaskActivity.eval(DatabaseTaskActivity.java:106)
    at com.tibco.bx.core.behaviors.activity.BxExtensionBehavior.doEval(BxExtensionBehavior.java:162)
    at com.tibco.bx.core.behaviors.activity.BxActivityBaseBehavior.eval(BxActivityBaseBehavior.java:836)

Example :
A simple query as below is defined in the database task :
SELECT EC_WI_STATUS.WORK_ITEM_ID FROM EC_WI_STATUS WHERE TO_CHAR EC_WI_STATUS.WORK_ITEM_ID IN  (?)

An integer array with values of workitem ids, is being passed as "IN" parameter to the database task. At runtime when this database task is executed the above error is thrown and the process gets halted.
 

Passing an array parameter in the database service task does not work

ALL

ORACLE doesn't support array parameters in the IN clauses of SQL statements.

User data for a bind variable is always treated as pure data and never as part of the SQL statement. Because of this, trying to use a comma separated list of items in a single bind variable will be recognized by Oracle only as a single value, not as multiple values. The common use case is when allowing a web user to choose multiple options from a list and wanting to do a query on all values. See page 169 :

http://www.oracle.com/technetwork/database/database-technologies/php/201212-ug-php-oracle-1884760.pdf

Since underlying database doesn't support array parameters in the IN clauses of SQL statements hence the above error at runtime.

The workaround is to use string with each value in single quotes and comma separated.
 

1. https://docs.tibco.com/pub/business-studio-bpm-edition/4.1.0/doc/html/GUID-86FD1BE2-FB03-41A2-8597-9B527361D7CE.html
2. http://www.oracle.com/technetwork/database/database-technologies/php/201212-ug-php-oracle-1884760.pdf