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)
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(?,?,?))");