调用 'V' 时参数个数或类型错误

bex5chuxuezhe

select t.* ,v."+a+" as SAMPLY_BARNO from SAMPLYCHK t  optional join (select m.* from STATIC_CHKRESULTRPT m where m.SAMPLY_PLANINFOID='"+fid+"')v on t.MDS_CHKITEMID=v.MDS_CHKITEMID order by t.INDEXNO ";
这是我写的ksql,我在数据库执行是对的但在Java中执行会报错，这是报错信息
java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at com.justep.system.action.Engine.invokeActions(Native Method)
        at com.justep.system.action.Engine.invokeActions(Unknown Source)
        at com.justep.business.server.BusinessServer.doAction(Unknown Source)
        at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
        at com.justep.business.server.BusinessServer.excute(Unknown Source)
        at com.justep.business.server.BusinessServer.excute(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
        at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Thread.java:745)
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: select t.* ,v.SAMPLY_BARNO2 from SAMPLYCHK t  optional join (select m.* from STATIC_CHKRESULTRPT m where m.SAMPLY_PLANINFOID='883AA65510214EF7BA1680251210FCD5')v on t.MDS_CHKITEMID=v.MDS_CHKITEMID order by t.INDEXNO
--> sql: SELECT  t.fID AS t, t.MDS_CHKITEMID AS t_MDS_CHKITEMID, t.CHK_ITEMNAME AS CHK_ITEMNAME, t.CHK_CLEVUNIT AS CHK_CLEVUNIT, t.CHK_CLEVVALUE AS CHK_CLEVVALUE, t.INDEXNO AS t_INDEXNO, v.SAMPLY_BARNO2 AS SAMPLY_BARNO2 FROM SAMPLYCHK t LEFT JOIN (SELECT  m.fID AS m, m.SAMPLY_PLANINFOID AS SAMPLY_PLANINFOID, m.MDS_CHKITEMID AS MDS_CHKITEMID, m.SAMPLY_BARNO1 AS SAMPLY_BARNO1, m.SAMPLY_BARNO2 AS m_SAMPLY_BARNO2, m.SAMPLY_BARNO3 AS SAMPLY_BARNO3, m.SAMPLY_BARNO4 AS SAMPLY_BARNO4, m.SAMPLY_BARNO5 AS SAMPLY_BARNO5, m.SAMPLY_BARNO6 AS SAMPLY_BARNO6, m.SAMPLY_BARNO7 AS SAMPLY_BARNO7, m.SAMPLY_BARNO8 AS SAMPLY_BARNO8, m.SAMPLY_BARNO9 AS SAMPLY_BARNO9, m.SAMPLY_BARNO10 AS SAMPLY_BARNO10, m.SAMPLY_BARNO11 AS SAMPLY_BARNO11, m.SAMPLY_BARNO12 AS SAMPLY_BARNO12, m.SAMPLY_BARNO13 AS SAMPLY_BARNO13, m.SAMPLY_BARNO14 AS SAMPLY_BARNO14, m.SAMPLY_BARNO15 AS SAMPLY_BARNO15, m.SAMPLY_BARNO16 AS SAMPLY_BARNO16, m.SAMPLY_BARNO17 AS SAMPLY_BARNO17, m.SAMPLY_BARNO18 AS SAMPLY_BARNO18, m.SAMPLY_BARNO19 AS SAMPLY_BARNO19, m.SAMPLY_BARNO20 AS SAMPLY_BARNO20, m.SAMPLY_BARNO21 AS SAMPLY_BARNO21, m.SAMPLY_BARNO22 AS SAMPLY_BARNO22, m.SAMPLY_BARNO23 AS SAMPLY_BARNO23, m.SAMPLY_BARNO24 AS SAMPLY_BARNO24, m.SAMPLY_BARNO25 AS SAMPLY_BARNO25, m.SAMPLY_BARNO26 AS SAMPLY_BARNO26, m.INDEXNO AS INDEXNO FROM STATIC_CHKRESULTRPT m  WHERE m.SAMPLY_PLANINFOID = ?) v ON t.MDS_CHKITEMID = v.MDS_CHKITEMID  ORDER BY t.INDEXNO ASC
--> binds: [883AA65510214EF7BA1680251210FCD5]
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlSelect(Unknown Source)
        at com.justep.system.data.KSQL.select(Unknown Source)
        at SelectSamlpyNo.queryChkItem(SelectSamlpyNo.java:58)
        ... 27 more
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150039; 提示: 执行sql: SELECT  t.fID AS t, t.MDS_CHKITEMID AS t_MDS_CHKITEMID, t.CHK_ITEMNAME AS CHK_ITEMNAME, t.CHK_CLEVUNIT AS CHK_CLEVUNIT, t.CHK_CLEVVALUE AS CHK_CLEVVALUE, t.INDEXNO AS t_INDEXNO, v.SAMPLY_BARNO2 AS SAMPLY_BARNO2 FROM SAMPLYCHK t LEFT JOIN (SELECT  m.fID AS m, m.SAMPLY_PLANINFOID AS SAMPLY_PLANINFOID, m.MDS_CHKITEMID AS MDS_CHKITEMID, m.SAMPLY_BARNO1 AS SAMPLY_BARNO1, m.SAMPLY_BARNO2 AS m_SAMPLY_BARNO2, m.SAMPLY_BARNO3 AS SAMPLY_BARNO3, m.SAMPLY_BARNO4 AS SAMPLY_BARNO4, m.SAMPLY_BARNO5 AS SAMPLY_BARNO5, m.SAMPLY_BARNO6 AS SAMPLY_BARNO6, m.SAMPLY_BARNO7 AS SAMPLY_BARNO7, m.SAMPLY_BARNO8 AS SAMPLY_BARNO8, m.SAMPLY_BARNO9 AS SAMPLY_BARNO9, m.SAMPLY_BARNO10 AS SAMPLY_BARNO10, m.SAMPLY_BARNO11 AS SAMPLY_BARNO11, m.SAMPLY_BARNO12 AS SAMPLY_BARNO12, m.SAMPLY_BARNO13 AS SAMPLY_BARNO13, m.SAMPLY_BARNO14 AS SAMPLY_BARNO14, m.SAMPLY_BARNO15 AS SAMPLY_BARNO15, m.SAMPLY_BARNO16 AS SAMPLY_BARNO16, m.SAMPLY_BARNO17 AS SAMPLY_BARNO17, m.SAMPLY_BARNO18 AS SAMPLY_BARNO18, m.SAMPLY_BARNO19 AS SAMPLY_BARNO19, m.SAMPLY_BARNO20 AS SAMPLY_BARNO20, m.SAMPLY_BARNO21 AS SAMPLY_BARNO21, m.SAMPLY_BARNO22 AS SAMPLY_BARNO22, m.SAMPLY_BARNO23 AS SAMPLY_BARNO23, m.SAMPLY_BARNO24 AS SAMPLY_BARNO24, m.SAMPLY_BARNO25 AS SAMPLY_BARNO25, m.SAMPLY_BARNO26 AS SAMPLY_BARNO26, m.INDEXNO AS INDEXNO FROM STATIC_CHKRESULTRPT m  WHERE m.SAMPLY_PLANINFOID = ?) v ON t.MDS_CHKITEMID = v.MDS_CHKITEMID  ORDER BY t.INDEXNO ASC, binds: [883AA65510214EF7BA1680251210FCD5]出错
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.ksql.KSQLUtils.execSqlSelect(Unknown Source)
        ... 32 more
Caused by: java.sql.SQLException: ORA-06553: PLS-306: 调用 'V' 时参数个数或类型错误

        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:796)
        at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1031)
        at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:836)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1124)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3329)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        ... 33 more
[ERROR] 2017-11-29 13:52:20,948 com.justep.report.controller.DataController - 报表数据获取失败
执行KSQL出错, KSQL: select t.* ,v.SAMPLY_BARNO2 from SAMPLYCHK t  optional join (select m.* from STATIC_CHKRESULTRPT m where m.SAMPLY_PLANINFOID='883AA65510214EF7BA1680251210FCD5')v on t.MDS_CHKITEMID=v.MDS_CHKITEMID order by t.INDEXNO
--> sql: SELECT  t.fID AS t, t.MDS_CHKITEMID AS t_MDS_CHKITEMID, t.CHK_ITEMNAME AS CHK_ITEMNAME, t.CHK_CLEVUNIT AS CHK_CLEVUNIT, t.CHK_CLEVVALUE AS CHK_CLEVVALUE, t.INDEXNO AS t_INDEXNO, v.SAMPLY_BARNO2 AS SAMPLY_BARNO2 FROM SAMPLYCHK t LEFT JOIN (SELECT  m.fID AS m, m.SAMPLY_PLANINFOID AS SAMPLY_PLANINFOID, m.MDS_CHKITEMID AS MDS_CHKITEMID, m.SAMPLY_BARNO1 AS SAMPLY_BARNO1, m.SAMPLY_BARNO2 AS m_SAMPLY_BARNO2, m.SAMPLY_BARNO3 AS SAMPLY_BARNO3, m.SAMPLY_BARNO4 AS SAMPLY_BARNO4, m.SAMPLY_BARNO5 AS SAMPLY_BARNO5, m.SAMPLY_BARNO6 AS SAMPLY_BARNO6, m.SAMPLY_BARNO7 AS SAMPLY_BARNO7, m.SAMPLY_BARNO8 AS SAMPLY_BARNO8, m.SAMPLY_BARNO9 AS SAMPLY_BARNO9, m.SAMPLY_BARNO10 AS SAMPLY_BARNO10, m.SAMPLY_BARNO11 AS SAMPLY_BARNO11, m.SAMPLY_BARNO12 AS SAMPLY_BARNO12, m.SAMPLY_BARNO13 AS SAMPLY_BARNO13, m.SAMPLY_BARNO14 AS SAMPLY_BARNO14, m.SAMPLY_BARNO15 AS SAMPLY_BARNO15, m.SAMPLY_BARNO16 AS SAMPLY_BARNO16, m.SAMPLY_BARNO17 AS SAMPLY_BARNO17, m.SAMPLY_BARNO18 AS SAMPLY_BARNO18, m.SAMPLY_BARNO19 AS SAMPLY_BARNO19, m.SAMPLY_BARNO20 AS SAMPLY_BARNO20, m.SAMPLY_BARNO21 AS SAMPLY_BARNO21, m.SAMPLY_BARNO22 AS SAMPLY_BARNO22, m.SAMPLY_BARNO23 AS SAMPLY_BARNO23, m.SAMPLY_BARNO24 AS SAMPLY_BARNO24, m.SAMPLY_BARNO25 AS SAMPLY_BARNO25, m.SAMPLY_BARNO26 AS SAMPLY_BARNO26, m.INDEXNO AS INDEXNO FROM STATIC_CHKRESULTRPT m  WHERE m.SAMPLY_PLANINFOID = ?) v ON t.MDS_CHKITEMID = v.MDS_CHKITEMID  ORDER BY t.INDEXNO ASC
--> binds: [883AA65510214EF7BA1680251210FCD5]
com.justep.report.exception.ReportServerException: 报表数据获取失败
        at com.justep.report.controller.DataController.execute(Unknown Source)
        at com.justep.report.controller.GenericControllerChain.execute(Unknown Source)
        at com.justep.report.controller.InitController.execute(Unknown Source)
        at com.justep.report.controller.GenericControllerChain.execute(Unknown Source)
        at com.justep.report.Servlet.a(Unknown Source)
        at com.justep.report.Servlet.doPost(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Thread.java:745)
Caused by: com.justep.report.exception.ReportServerException: 执行KSQL出错, KSQL: select t.* ,v.SAMPLY_BARNO2 from SAMPLYCHK t  optional join (select m.* from STATIC_CHKRESULTRPT m where m.SAMPLY_PLANINFOID='883AA65510214EF7BA1680251210FCD5')v on t.MDS_CHKITEMID=v.MDS_CHKITEMID order by t.INDEXNO
--> sql: SELECT  t.fID AS t, t.MDS_CHKITEMID AS t_MDS_CHKITEMID, t.CHK_ITEMNAME AS CHK_ITEMNAME, t.CHK_CLEVUNIT AS CHK_CLEVUNIT, t.CHK_CLEVVALUE AS CHK_CLEVVALUE, t.INDEXNO AS t_INDEXNO, v.SAMPLY_BARNO2 AS SAMPLY_BARNO2 FROM SAMPLYCHK t LEFT JOIN (SELECT  m.fID AS m, m.SAMPLY_PLANINFOID AS SAMPLY_PLANINFOID, m.MDS_CHKITEMID AS MDS_CHKITEMID, m.SAMPLY_BARNO1 AS SAMPLY_BARNO1, m.SAMPLY_BARNO2 AS m_SAMPLY_BARNO2, m.SAMPLY_BARNO3 AS SAMPLY_BARNO3, m.SAMPLY_BARNO4 AS SAMPLY_BARNO4, m.SAMPLY_BARNO5 AS SAMPLY_BARNO5, m.SAMPLY_BARNO6 AS SAMPLY_BARNO6, m.SAMPLY_BARNO7 AS SAMPLY_BARNO7, m.SAMPLY_BARNO8 AS SAMPLY_BARNO8, m.SAMPLY_BARNO9 AS SAMPLY_BARNO9, m.SAMPLY_BARNO10 AS SAMPLY_BARNO10, m.SAMPLY_BARNO11 AS SAMPLY_BARNO11, m.SAMPLY_BARNO12 AS SAMPLY_BARNO12, m.SAMPLY_BARNO13 AS SAMPLY_BARNO13, m.SAMPLY_BARNO14 AS SAMPLY_BARNO14, m.SAMPLY_BARNO15 AS SAMPLY_BARNO15, m.SAMPLY_BARNO16 AS SAMPLY_BARNO16, m.SAMPLY_BARNO17 AS SAMPLY_BARNO17, m.SAMPLY_BARNO18 AS SAMPLY_BARNO18, m.SAMPLY_BARNO19 AS SAMPLY_BARNO19, m.SAMPLY_BARNO20 AS SAMPLY_BARNO20, m.SAMPLY_BARNO21 AS SAMPLY_BARNO21, m.SAMPLY_BARNO22 AS SAMPLY_BARNO22, m.SAMPLY_BARNO23 AS SAMPLY_BARNO23, m.SAMPLY_BARNO24 AS SAMPLY_BARNO24, m.SAMPLY_BARNO25 AS SAMPLY_BARNO25, m.SAMPLY_BARNO26 AS SAMPLY_BARNO26, m.INDEXNO AS INDEXNO FROM STATIC_CHKRESULTRPT m  WHERE m.SAMPLY_PLANINFOID = ?) v ON t.MDS_CHKITEMID = v.MDS_CHKITEMID  ORDER BY t.INDEXNO ASC
--> binds: [883AA65510214EF7BA1680251210FCD5]
        at com.justep.report.controller.DataController.a(Unknown Source)
        at com.justep.report.controller.DataController.a(Unknown Source)
        ... 20 more
Caused by: java.lang.Exception
        ... 22 more

请大神帮忙改正
                                       

jishuang

比较复杂的语句推荐直接用sql执行
/BIZ/demo/report/logic/code/dsrc/SimpleReportProcedure.java

bex5chuxuezhe

好的，以用sql解决