Hi,
i have Problems executing a Report containing the following SQL-Statement:
WITH t AS (SELECT LISTAGG (FIELD, '; ') WITHIN GROUP (ORDER BY id) FROM test_table) SELECT * FROM t
The exception I get is the following:
ERROR AsyncJasperPrintAccessor,pool-4-thread-453:321 - Error during report execution
com.jaspersoft.jasperserver.api.JSSecurityException: Ein Fehler ist aufgetreten. Wenden Sie sich an Ihren Systemadministrator. (6632)
Arguments:
at com.jaspersoft.jasperserver.api.security.validators.Validator.validateSQL(Validator.java:500)
at com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuter.createDatasource(JRTimezoneJdbcQueryExecuter.java:168)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245)
at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723)
at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:438)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:550)
at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$SynchronousExecutor.execute(EngineServiceImpl.java:932)
at net.sf.jasperreports.engine.fill.BaseFillHandle.startFill(BaseFillHandle.java:169)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$AsynchronousReportFiller.fillReport(EngineServiceImpl.java:888)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl.fillReport(EngineServiceImpl.java:1790)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runWithDataSource(EngineServiceImpl.java:1130)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportFill.runReport(EngineServiceImpl.java:1059)
at com.jaspersoft.jasperserver.api.engine.jasperreports.service.impl.EngineServiceImpl$ReportRunnable.run(EngineServiceImpl.java:954)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
I know that JasperServer does not support WITH-Statements out of the box, so I already (a few months ago) changed Validator.ValidSQL in Security-Settings to the following:
Validator.ValidSQL=(?is)^\\s*(select|call|with)\\s+[^;]+;?\\s*$
I figured out, that the problem is the LISTAGG ... WITHIN GROUP Clause which causes the validation to fail.
I could not find any setting for allowing that? Is there any official solution for using Oracle Functions on JasperReports Server?
4 Answers:
Did you give a try on below ?
webapps/jasperserver/WEB-INF/classes/esapi/security-config.properties
security.validation.sql.on=false
https://community.jaspersoft.com/questions/541509/error-has-occurred-663...
UPDATE:
I figured out, that this is not only the case for LISTAGG Function, it seems that also Oracle's DECODE is seen as invalid SQL.
I turned off the Validation now, but definately need a better solution.
Any help or other suggestions concerning this issue? I can't be the only one using Oracle Functions in a JasperReport (-:
UPDATE:
I think I found the solution for these Problems.
In <queryString>-Tag in the Report, there is a property "language". When setting this property to "plsql" instead of "sql", the validation does not fail and JasperReport Server therefore accepts the oracle functions!
