CREATE OR REPLACE TYPE SMPL_METADATA AS OBJECT
(
  ALLIANCE_NAME             VARCHAR2(100), 
  SITE_ID                    VARCHAR2(50),
  GAME_NAME                  VARCHAR2(50),
  GAME_SERVE                 VARCHAR2(50),
  MATERIAL_ID_NAME          VARCHAR2(100),
  FEE                       VARCHAR2(100),
  CLICK_NUM                           INT, 
  REACH_NUM                           INT,
  TRIGGER_NUM                         INT,
)CREATE OR REPLACE TYPE SMPL_METADATA_TABLE AS TABLE OF SMPL_METADATAstat= conn.prepareCall("{? = call FN_WM_QUERY_DETAIL(?,?,?)}");
stat.registerOutParameter(1, OracleTypes.ARRAY, "SMPL_METADATA_TABLE");
stat.setString(2, fromDate);
stat.setString(3, toDate);
stat.setString(4, date);
stat.execute();
Array array = stat.getArray(1);
ResultSet res = (ResultSet) array.getResultSet();[/align]
报这个错
java.sql.SQLException: ORA-06550: line 1, column 10:
PLS-00653: aggregate/table functions are not allowed in PL/SQL scope at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:191)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:944)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3482)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:3856)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1373)
at com.wm.service.impl.ExcelService$GenThread.queryDetail(ExcelService.java:237)
at com.wm.service.impl.ExcelService$GenThread.run(ExcelService.java:86)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

解决方案 »

  1.   

    SQL code    CREATE OR REPLACE FUNCTION someFunction (VALUE IN NUMBER, anotherValue IN NUMBER) RETURN NUMBER IS BEGIN RETURN VALUE + anotherValue; END;Java code    CallableStatement callableStatement = connection.prepareCall("{? = call somefunction(?,?)}");贴是AARON7744 发的,看看有用没,没有找他!
      

  2.   

    AARON7744 没解决,下次散分不给他分了!lz转告他,哈哈
      

  3.   

    try calling the function from DUAL in your PL/SQL codetell me please whether it helps or not.
      

  4.   

    唉~真不容易啊,问题我自己已经解决了,现把答案补上。谢谢大家啊。
    psts=conn.prepareStatement("select * from table(FN_WM_QUERY_DETAIL(?,?,?))");
    psts.setString(1, fromDate);
    psts.setString(2, toDate);
    psts.setString(3, date);
    ResultSet res = (ResultSet) psts.executeQuery();
    其实就是这块的问题psts=conn.prepareStatement("select * from table(FN_WM_QUERY_DETAIL(?,?,?))");