how to pass multi-select parameters to oracle stored procedure

[flubet]

Hi,

A parameter named in_Region declared as Collection in i-report and need to pass this parameter to Oracle stored procedure.

in I-report query this is how i call the SP :

{

call REPORT.GetStatistic(

$P{in_Region}, 

$P{ORACLE_REF_CURSOR}

)}

 

in JasperServer , i created Input Control of type Multi-Select Query (Checkbox) 

 

and i got this error when i run in jasperserver

com.jaspersoft.jasperserver.api.JSExceptionWrapper: Report design not valid : 1. Parameter type not supported in query : in_Region class java.util.Collection

any idea?

[cameron_1]

You can't use collections with the $P{} parameter reference.

I'm more curious as to how your stored procedure is expecting the values to be given to it? Is it expecting some kind of formatted string of values to parse, or somethign else?

I suspect your solution will require you to have 2 parameters, 1 being a collection, used as an input control to collect your parameters, then another parameter to pass to your stored procedue (in the same way as you've done here with the $P{} use), this would be a string type, and it would have a default value set with some code to take your collection and turn it into whatever your stored procedure is expecting for the input.

[vinothkumarneptunesoftwar]

Hi

 

Can you please post with an Example or attach a jrxml file.

 

Regards

Vinoth

[vinothkumarneptunesoftwar]

hi

 

Im getting the below error

 

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : A38L32Ledger32Analysis32Yearly_A38L32Ledger32Yearly_1424933273556_166168

at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:467)

at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$18(ReportControler.java:442)

at com.jaspersoft.studio.editor.preview.view.control.ReportControler$4.run(ReportControler.java:334)

at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)

Caused by: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : A38L32Ledger32Analysis32Yearly_A38L32Ledger32Yearly_1424933273556_166168

at net.sf.jasperreports.engine.fill.JRFillSubreport.prepare(JRFillSubreport.java:848)

at net.sf.jasperreports.components.table.fill.FillTableSubreport.prepareSubreport(FillTableSubreport.java:167)

at net.sf.jasperreports.components.table.fill.FillTable.prepare(FillTable.java:414)

at net.sf.jasperreports.engine.fill.JRFillComponentElement.prepare(JRFillComponentElement.java:151)

at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:331)

at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:383)

at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:358)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummaryNoLastFooterSamePage(JRVerticalFiller.java:1072)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillSummary(JRVerticalFiller.java:1031)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportEnd(JRVerticalFiller.java:295)

at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:131)

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:540)

at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:120)

at java.lang.Thread.run(Unknown Source)

Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : A38L32Ledger32Analysis32Yearly_A38L32Ledger32Yearly_1424933273556_166168

at com.jaspersoft.jrx.query.PlSqlQueryExecuter.createDatasource(PlSqlQueryExecuter.java:115)

at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1119)

at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:691)

at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:434)

at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:508)

at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:393)

at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:704)

at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)

at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:203)

at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

... 1 more

Caused by: java.sql.SQLException: ORA-06550: line 1, column 39:

PLS-00103: Encountered the symbol "" The symbol "" was ignored.

 

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)

at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)

at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)

at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)

at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)

at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1007)

at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)

at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)

at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)

at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)

at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)

at com.jaspersoft.jrx.query.PlSqlQueryExecuter.createDatasource(PlSqlQueryExecuter.java:104)

... 11 more

 

[gururagavan2008]

You need to write a scriptlet to convert list to string and then handle this string in your DB side to achieve multi-select in a stored procedure.