java.sql.SQLException: Missing IN or OUT parameter

[MarkBaker]

I've a fairly complex report using a servlet and a system incrementer; but it runs quite happily using iReport.

 

Having finally got my classpath set correctly, I'm trying to run it from a servlet; but am getting the following error.

Code:

2007-03-21 17:18:23,237 FATAL [com.redeye.reports.beans.RunJasperReport] - Error running jasper report.
java.sql.SQLException: Missing IN or OUT parameter at index:: 1
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:1681)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3280)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:115)
at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:665)
at net.sf.jasperreports.engine.fill.JRFillDataset.setDatasource(JRFillDataset.java:609)
at net.sf.jasperreports.engine.fill.JRFillDataset.setParameterValues(JRFillDataset.java:598)
at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:879)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:708)
at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:666)
at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:63)
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:402)
at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:234)
at com.redeye.reports.util.JasperReportUtil.compileToPrint(JasperReportUtil.java:142)
at com.redeye.reports.servlet.RunJasperReportServlet.doGet(RunJasperReportServlet.java:78)
at com.redeye.reports.servlet.RunJasperReportServlet.doPost(RunJasperReportServlet.java:57)[code]

 

The query is also fairly complex:

Code:

SELECT a.campaign, a.schedule_id, a.schedule_name, a.sent, a.subject,  
       NVL(a.assumed_delivered,0) assumed_Delivered, 
       NVL(a.unsubscribes,0) unsubscribes, 
       a.email_served_by_redeye_yn email_served_by_redeye_yn,
       NVL(ROUND(DECODE(NVL(a.assumed_delivered,0), 0, 0, a.unsubscribes*100/a.assumed_delivered),2),0) UnsubscribeRate,
       NVL(b.Opens,0) Opens,
       NVL(ROUND(DECODE(NVL(a.assumed_delivered,0), 0, 0, b.SumOpens*100/a.assumed_delivered),2),0)  OpenRate,
       NVL(b.Clicks,0) Clicks,
       NVL(ROUND(DECODE(NVL(a.assumed_delivered,0), 0, 0,b.SumClicks*100/a.assumed_delivered),2),0)  ClickRate,
       NVL(b.Arrivals,0) Arrivals,
       NVL(b.responded,0) Responded,
       NVL(b.responses,0) Responses,
       NVL(b.Items,0) Items,
       NVL(b.Revenue,0) Revenue,
       NVL(ROUND(DECODE(NVL(a.assumed_delivered,0), 0, 0,b.SumRespondents*100/a.assumed_delivered),2),0) Conversion, 
       NVL(b.AvgOrderSize ,0) AvgOrderSize,
       NVL(b.AvgBasketSize ,0) AvgBasketSize,
       b.Minday Minday,
       NVL(c.reach,0) reach, 
       c.MinImpressDay MinImpressDay
FROM emails_delivered_summary a,
(SELECT schedule_id,  
       SUM(NVL(opens,0)) Opens, 
       SUM(NVL(opens,0)) SumOpens,
       SUM(NVL(clicks,0)) Clicks, 
       SUM(NVL(clicks,0)) SumClicks, 
       SUM(NVL(arrivals,0)) Arrivals,
       SUM(NVL(respondents,0)) Responded,
       SUM(NVL(responses,0)) Responses,  
       SUM(NVL(items,0)) Items,
       SUM(NVL(revenue,0)) Revenue,
       SUM(NVL(respondents,0)) SumRespondents,
       ROUND(DECODE(SUM(NVL(respondents,0)), 0, 0,SUM(NVL(revenue,0))/SUM(NVL(respondents,0))),2)AvgOrderSize,
       ROUND(DECODE(SUM(NVL(items,0)), 0, 0,SUM(NVL(revenue,0))/SUM(NVL(items,0))),2)AvgBasketSize,
       MIN(DAY) Minday
FROM email_metrics_summary_view 
WHERE 1=1
$P!{START_FILTER}
$P!{END_FILTER}
$P!{RESPONSE_FILTER}
GROUP BY schedule_id) b,
(SELECT schedule_id, count(DISTINCT(personid)) reach, MIN(DAY) MinImpressDay
FROM email_impress_view
WHERE 1=1
$P!{START_FILTER}
$P!{END_FILTER}
group by schedule_id) c
WHERE a.schedule_id = b.schedule_id(+)
AND a.schedule_id = c.schedule_id(+)
$P!{CAMPAIGN_FILTER}
$P!{EMAIL_FILTER}
ORDER BY a.campaign ASC,a.sent ASC, a.schedule_name ASC

but it does run happily from iReport.

 

Parameters are all passed as java.lang.String, and are empty strings as default (no nulls).

 

Database is Oracle 10.2.0.1.0, and I'm using the most recent ojdbc14.jar for that version.

 

Any suggestions or help appreciated.

[yskripch]

I am also, curious if there is an answer for this inquiry.

 

I have a similar query. Where I use select stuff from some_table $P!{WhereStatement} I was told this should work, but I receive the same error.

[sudeep.duvvuru]

I faced similar error in reading Oracle DB Query where im using :PARAMETER_NAME oracle syntax instead of $P{PARAMETER_NAME} in my SQL that I have written