java调用存储过程报错

Question

存储过程建成功了，我在用java调用的时候又报错了。
首先我的存储过程如下：create or replace procedure query_student   
is
v_name varchar2(20);
begin
 select name into v_name from st_t_student where id=3012;
 dbms_output.put_line(v_name);
end query_student;我的java代码如下：public void test(){
conn = JDBCUtils.getConnection("miniWeb", "miniWeb");
try {
conn.setAutoCommit(false);
//调用存储过程
cstmt = conn.prepareCall("{call query_student}");
rs = cstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString("v_name"));
}
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
JDBCUtils.closeConnection(rs, pstmt, conn);
}
}我的错误信息如下：java.sql.SQLException: ORA-00900: invalid SQL statement at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Odscrarr.receive(T4C8Odscrarr.java:203)
at oracle.jdbc.driver.T4CCallableStatement.do_describe(T4CCallableStatement.java:604)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:3037)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1861)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1559)
at com.sinosoft.javase.ProcedureTest.test(ProcedureTest.java:36)
at com.sinosoft.javase.ProcedureTest.main(ProcedureTest.java:53)

Answer 1

Statement stmt = con.createStatement(); 
ResultSet rs = stmt.executeQuery("{call query_student}"); 
试试

Answer 2

要不就在存储过程中定义一个输出参数，作为返回值
CallableStatement cstmt = con.prepareCall("{call query_student)}"); 
cstmt.execute(); 
System.out.println(cstmt.getString(1));
试试

Answer 3

cstmt = conn.prepareCall("{call query_student}");
------------->这里貌似需要括号，有点忘了
cstmt = conn.prepareCall("{call query_student()}");

Answer 4

 conn = JDBCUtils.getConnection(....."miniWeb", "miniWeb");
少url

Answer 5

如果要返回值得cstmt=conn.preparCall("call query_student（？，？）");
cstmt.setString(1,"aa");//第一个必须是输入的，不能是输出
cstmt=registerOutParameter(2,java.sql.Types.VARCHAR);//输出
stmt.executeUpdate();
String val=cstmt.getString(2);

Answer 6

/*
 * file: TestProcedure.java
 * class: TestProcedure
 *
 * description: 
 *
 * @author:  leisore
 * @version: V1.0.0
 */
package cn.leisore.daily._2010_06_03;import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestProcedure {    public TestProcedure() {
        super();
    }    public static void main(String[] args) throws ClassNotFoundException {
        Connection conn=null;
        ResultSet rs=null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@127.0.0.1:1521:leisore", "leisore",
                    "leisore");
            conn.setAutoCommit(false);
            // 调用存储过程
            CallableStatement cstmt = conn.prepareCall("{call query_student}");
            rs = cstmt.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("v_name"));
            }
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }    }java.sql.SQLException: 无法对 PLSQL 语句执行提取: next
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:192)
at cn.leisore.daily._2010_06_03.TestProcedure.main(TestProcedure.java:36)

Answer 7

public void test(){
conn = JDBCUtils.getConnection("miniWeb", "miniWeb");
try {
conn.setAutoCommit(false);
//调用存储过程
//Statement stmt = conn.createStatement();
//rs = stmt.executeQuery("{call query_student}");
cstmt = conn.prepareCall("{call query_student()}");
cstmt.registerOutParameter(1, Types.VARCHAR); 
cstmt.execute();
System.out.println(cstmt.getString(1));
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
JDBCUtils.closeConnection(rs, pstmt, conn);
}
}java.sql.SQLException: 无效的列索引
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:117)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:253)
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:333)
at com.sinosoft.javase.ProcedureTest.test(ProcedureTest.java:38)
at com.sinosoft.javase.ProcedureTest.main(ProcedureTest.java:56)

Answer 8

 cstmt = conn.prepareCall("{call query_student()}");
            cstmt.execute();

Answer 9

这样不报错了，但是我怎么能在java中获取存储过程查询的结果呢？

Answer 10

create or replace procedure query_student   
is
v_name varchar2(20);
begin
 select name into v_name from st_t_student where id=3012;
-- dbms_output.put_line(v_name); 这句不要呢
end query_student;

Answer 11

下面这样就可以了：public void queryStudent(){
conn = JDBCUtils.getConnection("miniWeb", "miniWeb");
try {
conn.setAutoCommit(false);
//调用存储过程
//Statement stmt = conn.createStatement();
//rs = stmt.executeQuery("{call query_student}");
cstmt = conn.prepareCall("{call query_student()}");
//cstmt.registerOutParameter(1, Types.VARCHAR); 
cstmt.execute();
//System.out.println(cstmt.getString(1));
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
JDBCUtils.closeConnection(rs, cstmt, conn);
}
}

Answer 12

11楼是对的
create or replace procedure p_query(v_i in int,v_name out varchar2)
is
begin
 select ename into v_name from emp where empno=7900 and 1=v_i;
end p_query;
package com.abing.test;import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;public class P_test {    public P_test() {
        super();
    }    public static void main(String[] args) throws ClassNotFoundException {
        Connection conn=null;
        ResultSet rs=null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott",
                    "tiger");
            conn.setAutoCommit(false);
            // 调用存储过程
            CallableStatement cstmt = conn.prepareCall("{call p_query(?,?)}");
            cstmt.setInt(1,1);
            cstmt.registerOutParameter(2, Types.VARCHAR);
            cstmt.execute();
            String name = cstmt.getString(2);
            System.out.println(name);
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }    }}
输出结果:JAMES

Answer 13

返回结果集
create or replace package pa_query as
 type p_cursor is ref cursor;
end pa_query;
/
create or replace procedure p_query(v_cursor out pa_query.p_cursor)
is
begin
open v_cursor for select * from emp;
end p_query;package com.abing.test;import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;import oracle.jdbc.OracleTypes;public class P_test {    public P_test() {
        super();
    }    public static void main(String[] args) throws ClassNotFoundException {
        Connection conn=null;
        ResultSet rs=null;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott",
                    "tiger");
            conn.setAutoCommit(false);
            // 调用存储过程
            CallableStatement cstmt = conn.prepareCall("{call p_query(?)}");
            //cstmt.setInt(1,1);
            cstmt.registerOutParameter(1, OracleTypes.CURSOR);
            cstmt.execute();
            rs = (ResultSet) cstmt.getObject(1);
            while(rs.next()) {
             String name = rs.getString("ename");
             System.out.println(name);
            }
            
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }    }}

Answer 14

呵呵，这个输入参数，也不是必须的
...
create or replace procedure p_query(v_name out varchar2)
...CallableStatement cstmt = conn.prepareCall("{call p_query(?)}");
//cstmt.setInt(1,1);
cstmt.registerOutParameter(1, Types.VARCHAR);
cstmt.execute();
String name = cstmt.getString(1);
System.out.println(name);

Answer 15

public void queryStudent(){
conn = JDBCUtils.getConnection("miniWeb", "miniWeb");
try {
conn.setAutoCommit(false);
//调用存储过程
cstmt = conn.prepareCall("{call query_student(?)}");
cstmt.registerOutParameter(1, Types.VARCHAR); 
cstmt.execute();
System.out.println(cstmt.getString(1));
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
JDBCUtils.closeConnection(rs, cstmt, conn);
}
}

Answer 16

create or replace procedure proc_selOperator(name in varchar2,pwd in varchar2,selType in varchar2)
as
cname varchar2(40);
cpwd varchar2(16);
begin
  if(selType = 'IN_NAME')then
    select Oname,Opwd into cname,cpwd from operator where Oname = name;
  elsif(selType ='IN_NAME_PWD')then
    select Oname,Opwd into cname,cpwd from operator where Oname = name and Opwd = pwd;
  end if;
end;
我也遇到相同的问题了都在么。。这个是我的存储过程，返回多个值。。
java代码调用存储过程
public static Collection selOperator(String name, String pwd,
OperatorSelType selType) {
// 连接数据库
Connection con = ConnectionManager.getConnection();
CallableStatement cst = null;
ResultSet rs = null;
// 建一个操作员列表
Collection<Operator> opc = new ArrayList<Operator>();
try {
String proc = "{call proc_selOperator ('"+name+"','"+pwd
+"','"+ selType+"')}";
// 取得操作员表数据
cst = con.prepareCall(proc);
rs = cst.executeQuery();
while (rs.next()) {
Operator op = new Operator();
op.setName(rs.getString("Oname"));
op.setPwd(rs.getString("Opwd"));
opc.add(op);
}
} catch (SQLException ex) {
ex.printStackTrace();
} finally {
ConnectionManager.closeResultSet(rs);
ConnectionManager.closeStatement(cst);
ConnectionManager.closeConnection(con);
}
return opc;
}
出现java.sql.SQLException: ORA-00900: invalid SQL statement at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
at oracle.jdbc.driver.T4C8Odscrarr.receive(T4C8Odscrarr.java:203)
at oracle.jdbc.driver.T4CCallableStatement.do_describe(T4CCallableStatement.java:604)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:3047)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1861)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1559)
at cn.waycool.contrl.DBWorker.selOperator(DBWorker.java:53)
at cn.waycool.frame.Login.actionPerformed(Login.java:99)
at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1849)
at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2169)
at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:234)
at java.awt.Component.processMouseEvent(Component.java:5488)
at javax.swing.JComponent.processMouseEvent(JComponent.java:3093)
at java.awt.Component.processEvent(Component.java:5253)
at java.awt.Container.processEvent(Container.java:1966)
at java.awt.Component.dispatchEventImpl(Component.java:3955)
at java.awt.Container.dispatchEventImpl(Container.java:2024)
at java.awt.Component.dispatchEvent(Component.java:3803)
at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4212)
at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3892)
at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3822)
at java.awt.Container.dispatchEventImpl(Container.java:2010)
at java.awt.Window.dispatchEventImpl(Window.java:1766)
at java.awt.Component.dispatchEvent(Component.java:3803)
at java.awt.EventQueue.dispatchEvent(EventQueue.java:463)
at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:234)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:163)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:157)
at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:149)
at java.awt.EventDispatchThread.run(EventDispatchThread.java:110)