存储过程-------
create or replace procedure proc8(v_in_table in varchar,v_in_pagesize in number,v_in_pagenow in number,v_out_result out pack2.my_cursor) is
2 v_begin number(10);
3 v_end number(10);
4 v_sql varchar2(3000);
5 begin
6 v_begin:=v_in_pagesize*(v_in_pagenow-1)+1;
7 v_end:=v_in_pagesize*v_in_pagenow;
8 v_sql:='select name into v_name from (select a1.*,rownum rn from (select * from '||v_in_table||') a1 where rownum<='||v_end||' where rn>'||v_begin;
9 open v_out_result for v_sql;
10 end;
11 /
java程序--------
Class.forName("oracle.jdbc.driver.OracleDriver");
ct=DriverManager.getConnection("jdbc:oracle:thin:@10.1.12.184:1521:orcl","system","manager");
cs=ct.prepareCall("{call proc8(?,?,?,?)}");
cs.setString(1, "student");
cs.setInt(2,2);
cs.setInt(3,1);
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.CURSOR);
cs.execute();
rs=(ResultSet)cs.getObject(4);
while(rs.next()){
System.out.println("name="+rs.getString("name")+" no="+rs.getInt("no"));
}
错误------
java.sql.SQLException: ORA-00907: 缺失右括号
ORA-06512: 在 "SYSTEM.PROC8", line 9
ORA-06512: 在 line 1
------解决方案--------------------
1 本来就缺右括号。两个左一个右看不出来？
2 into是错的。
------解决方案--------------------
你那在 v_sql:='select name into v_name from (select a1.*,rownum rn from (select * from '||v_in_table||') a1 where rownum<='||v_end||' where rn>'||v_begin);
缺个括号啊。自己没有看出来吗
------解决方案--------------------
sql语句最好都分清楚 每个对应的括号放到一组 层次理清 报错也好清楚错在哪
------解决方案--------------------
写完后，最好是格式话一下，美观，看起来清楚