Invoking a DB procedure with Arraylist as an input parameter

Requirement: 

Can we invoke an Oracle DB procedure where the input parameter or the DB procedure is an array (a table from a DB perspective)? The Class type here is set as Collection. 

Resolution: 

Short answer: The use of arrays is not standard and not supported in JasperReports when working with stored procedures input parameters. At this point the user would have two options:

1. Create a custom QueryExecuter extending the JRJdbcQueryExecuter

2. Modify the stored procedure accepting data in a different format other than an array 

Both options are not trivial.

More detailed answer: There is no way today in JasperReports to pass an array as an input parameter of a stored procedure. The java.sql.Array type is designed to be implemented by a specific JDBC driver and used to read data from a ResultSet more than set a stored procedure input parameter.

Anyway, this article describes the procedure to set an array of custom table records as input parameters for a stored procedure:

https://forums.oracle.com/ords/apexds/post/how-to-pass-a-array-of-object-to-oracle-stored-procedure-9728

The process requires several steps that are very Oracle-specific, like the creation of the array to be passed to the stored procedure, based on the specific array type:

StructDescriptor structDescriptor = new StructDescriptor(

"APPS.DEPARTMENT_TYPE", connection);e s

STRUCT priceStruct = new STRUCT(structDescriptor, connection, dep);

STRUCT[] priceArray = { priceStruct };

ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(

"APPS.DEPT_ARRAY", connection);

ARRAY array = new ARRAY(arrayDescriptor, connection, priceArray); 

and the assignment of the parameter to the OracleCallableStatement:

((OracleCallableStatement) callStatement).setArray(1, array); 

Please note that there is no setArray() method on standard java.sql.CallableStatement, to confirm that this would work only in Oracle.

The plsql query executer today simply adds the ability to use a cursor returned as an output parameter from an Oracle Stored Procedure to read the data for the report. Other databases return this cursor directly without requiring an output parameter, without requiring a specific feature.

If the user decides to implement a custom query executer, the user would have to add support for Array type of parameters and implement the code to translate the Collection of objects into the exact STRUCT format required, as per the example provided above.