Maximum number of Oracle multi-value query parameters

1) Problem description  

If you use in filtering to view Oracle data, an error occurs when the number of parameters is greater than 1000.  The specific error message is:Error code :11300001 Dataset configuration error.Expression tree is too large(maximum depth 1000)

As shown below:

 2) Solution

To solve this problem, you can use the or keyword to replace the in keyword in Oracle queries to achieve the same function.  Such as:  

 select * from SORDERDETAIL where ORDERID  in ('10001','10002','10003','10004')

Can be rewritten as:

select * from SORDERDETAIL where ORDERID ='10001' or  ORDERID='10002' or ORDERID='10003' or ORDERID='10004'

1. Writing query SQL  

Create a regular report and write the SQL query statement. In the SQL statement, use the "= "sign instead of the "in" keyword.  

select * from  SORDERDETAIL where ORDERID='${id}'

As shown below:

2.Configuring the drop-down check box data dictionary  

Configure the data dictionary according to the actual situation of the project. Here I use the ID values from 1 to 1001 automatically generated by the formula.  The formula is: range(10001,11113)  

3. Configuring delimiters  

Set the delimiter to 'or ORDERid ='  

4.Preview effect  

Save the template, click "Pagination Preview", the effect is as shown below:  

If occurs: SQL attack injection attacks are suspected because the disabled special keywords are used. If you have special needs, contact the system administrator.  

Then enter the decision-making platform as the administrator, clickManage>Security>Anti-SQL injection,click \b(?  I)or\b field in the selected keyword, and then let it change into the unselected keyword.  As shown below:  

The template is complete. For details, see: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Parameter\Maximum_number_of_Oracle_multi-value_query_parameters.cpt

Click to download the template:

Maximum_number_of_Oracle_multi-value_query_parameters.cpt