我写了个分页的存储过程 单独在plsql里执行没有问题 但是通过java调用时出错ORA-00936: 缺失表达式
ORA-06512: 在 "SYSTEM.TESTTABLE_PAGE", line 76
ORA-06512: 在 line 1
java代码 如下public List<DemoInfo> SelectTestInfo(String tableName, String searchList,
String condition, String orderExpress, int pageIndex, int pageSize,
int totalCount, int pageCount) { try {
List<DemoInfo> testList = new ArrayList<DemoInfo>();
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
} catch (Exception e) {
System.out.println("No Driver!");
} String url = "jdbc:oracle:thin:@localhost:1521:TESTDB"; Connection conn = DriverManager.getConnection(url, "system",
"iflytek");
CallableStatement stmt = conn
.prepareCall("{call testtable_page(?,?,?,?,?,?,?,?,?)}");
/*
* Statement stmt=conn.createStatement(); CallableStatement csmt =
* conn.prepareCall("{call Pro_Page(?,?,?,?,?,?,?,?,?)}");
*/ stmt.setString(1, tableName);
stmt.setString(2, searchList);
stmt.setString(3, condition);
stmt.setString(4, orderExpress);
// stmt.setInt(5, pageIndex);
// stmt.setInt(6, pageSize);
stmt.registerOutParameter(5, OracleTypes.INTEGER, pageIndex);
stmt.registerOutParameter(6, OracleTypes.INTEGER, pageSize);
stmt.registerOutParameter(7, OracleTypes.NUMBER,totalCount);
stmt.registerOutParameter(8, OracleTypes.NUMBER,pageCount);
stmt.registerOutParameter(9, OracleTypes.CURSOR,"SYS_REFCURSOR");
stmt.execute();
ResultSet rs = (ResultSet) stmt.getObject(9);
while (rs.next()) {
DemoInfo d = new DemoInfo();
d.setId(rs.getInt("ID"));
d.setPrice(rs.getFloat("price"));
d.setCompany(rs.getString("company"));
d.setDate(rs.getDate("PRODUCTDATE"));
d.setSize(rs.getString("PRODUCTSIZE"));
d.setVisible(rs.getInt("visible")); testList.add(d);
} return testList; } catch (SQLException sqle) {
System.out.println(sqle.getMessage());
} catch (Exception e) {
System.out.println(e.getMessage());
}
return null; }
ORA-06512: 在 "SYSTEM.TESTTABLE_PAGE", line 76
ORA-06512: 在 line 1
java代码 如下public List<DemoInfo> SelectTestInfo(String tableName, String searchList,
String condition, String orderExpress, int pageIndex, int pageSize,
int totalCount, int pageCount) { try {
List<DemoInfo> testList = new ArrayList<DemoInfo>();
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
} catch (Exception e) {
System.out.println("No Driver!");
} String url = "jdbc:oracle:thin:@localhost:1521:TESTDB"; Connection conn = DriverManager.getConnection(url, "system",
"iflytek");
CallableStatement stmt = conn
.prepareCall("{call testtable_page(?,?,?,?,?,?,?,?,?)}");
/*
* Statement stmt=conn.createStatement(); CallableStatement csmt =
* conn.prepareCall("{call Pro_Page(?,?,?,?,?,?,?,?,?)}");
*/ stmt.setString(1, tableName);
stmt.setString(2, searchList);
stmt.setString(3, condition);
stmt.setString(4, orderExpress);
// stmt.setInt(5, pageIndex);
// stmt.setInt(6, pageSize);
stmt.registerOutParameter(5, OracleTypes.INTEGER, pageIndex);
stmt.registerOutParameter(6, OracleTypes.INTEGER, pageSize);
stmt.registerOutParameter(7, OracleTypes.NUMBER,totalCount);
stmt.registerOutParameter(8, OracleTypes.NUMBER,pageCount);
stmt.registerOutParameter(9, OracleTypes.CURSOR,"SYS_REFCURSOR");
stmt.execute();
ResultSet rs = (ResultSet) stmt.getObject(9);
while (rs.next()) {
DemoInfo d = new DemoInfo();
d.setId(rs.getInt("ID"));
d.setPrice(rs.getFloat("price"));
d.setCompany(rs.getString("company"));
d.setDate(rs.getDate("PRODUCTDATE"));
d.setSize(rs.getString("PRODUCTSIZE"));
d.setVisible(rs.getInt("visible")); testList.add(d);
} return testList; } catch (SQLException sqle) {
System.out.println(sqle.getMessage());
} catch (Exception e) {
System.out.println(e.getMessage());
}
return null; }
解决方案 »
- 求方法 实现用户登录时候 连接数据库 的查询判断
- 请前辈给我推荐一个JAVA开发实例的书
- 请大家帮忙解释下以下代码的意思?
- 获得指定日期 jsp
- 求助:用Eclipse里用Glassfish插件连接Mysql
- jsp连接sql server数据库报错
- 在tomcat部署好的网站,迁移到weblogic还是迁移到WebSphere好?
- jsp能插什么做的控件啊??
- 懒人问题:如何调用SQLSERVER的视图啊
- 各位高手,看一看
- 为什么isELIgnored设置成true才支持el表达式
- Struts has detected an unhandled exception: Stream closed 问题求各位大大指教
(
V_tableName in varchar2,--表名
V_searchList in varchar2,--查询的列
V_condition in varchar2,--查询条件
V_OrederException in varchar2,--排序的列
V_pageIndex in out integer,--页号
V_pageSize in out integer,--页的大小
V_totalRecords out number,--总记录数
V_totalPage out number,--总页数
v_CX out SYS_REFCURSOR --返回的结果集
)
as
V_startIndex int;
V_endIndex int;
V_SqlString VARCHAR2(2000);
begin
V_SqlString:='select to_number(Count(*)) from '||V_tableName;
if(V_condition is not null or V_condition <>'') then
V_SqlString:=V_SqlString || ' where ' || V_condition;
end if;
dbms_output.put_line(V_SqlString);
execute immediate V_SqlString into V_totalRecords;
--验证页码数
if(V_pageSize<=0) then
V_pageSize:=10;
end if;
dbms_output.put_line('V_totalRecords is'||V_totalRecords);
--获取总页数
if mod(V_totalRecords,V_pageSize)=0 then
V_totalPage := trunc(V_totalRecords /V_pageSize);
else
V_totalPage := trunc(V_totalRecords / V_pageSize) + 1;
end if;
if(V_totalPage<0) then
V_totalPage:=0;
end if;
dbms_output.put_line('V_totalPage is'||V_totalPage);
--验证页号
if(V_pageIndex<0) then
V_pageIndex:=0;
elsif(V_pageIndex>V_totalPage-1 and V_totalPage-1>=0)then
V_pageIndex:=V_totalPage-1;
end if; dbms_output.put_line('V_pageIndex is'||V_pageIndex);
dbms_output.put_line(V_totalRecords);
dbms_output.put_line(V_totalPage);
V_startIndex:=V_pageIndex*V_pageSize+1;
V_endIndex:=(V_pageIndex+1)*V_pageSize;
dbms_output.put_line(V_startIndex);
dbms_output.put_line(V_endIndex);
V_SqlString:='select * from (
SELECT A.*, ROWNUM RN
FROM (SELECT '||V_searchList||' FROM '||V_tableName||') A
WHERE ROWNUM <='||V_endIndex||'
)
where RN>='||V_startIndex||' and '||V_condition||'
order by '||V_OrederException;
dbms_output.put_line(V_SqlString);
OPEN v_CX FOR V_SqlString;end TESTTABLE_Page;
个人比较喜欢这种方法
Session session =HibernateSessionFactory.getSession();
SQLQuery query = session.createSQLQuery("{Call proc()}");
List list =query.list();
session.close();
Session session =HibernateSessionFactory.getSession(); SQLQuery query = session.createSQLQuery("{Call proc()}"); List list =query.list(); session.close();
如果没有返回值 直接用execute的方法就可以了