java调用存储过程报错

liuyyuns 2010-06-03 05:17:33
存储过程建成功了，我在用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)
...全文
884 26 打赏 收藏 转发到动态 举报
写回复
用AI写文章
26 条回复
切换为时间正序
请发表友善的回复…
发表回复
gaojie886 2011-10-08
  • 打赏
  • 举报
 回复
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)
liuyyuns 2010-06-04
  • 打赏
  • 举报
 回复
[Quote=引用 24 楼 abing0214 的回复:]
引用 21 楼 liuyyuns 的回复:
引用 20 楼 abing0214 的回复:
11楼是对的


SQL code

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=790……
[/Quote]


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);

		}

	}
超超王贱贱 2010-06-04
  • 打赏
  • 举报
 回复
[Quote=引用 21 楼 liuyyuns 的回复:]
引用 20 楼 abing0214 的回复:
11楼是对的


SQL code

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_que……
[/Quote]

呵呵，这个输入参数，也不是必须的



...

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);
liuyyuns 2010-06-04
  • 打赏
  • 举报
 回复
[Quote=引用 22 楼 abing0214 的回复:]
返回结果集


SQL code

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_cu……
[/Quote]
代码太帅了，连结果集都返回了，这下什么都不缺了。
超超王贱贱 2010-06-04
  • 打赏
  • 举报
 回复

返回结果集



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();

            }

        }



    }}
liuyyuns 2010-06-04
  • 打赏
  • 举报
 回复
[Quote=引用 20 楼 abing0214 的回复:]
11楼是对的


SQL code

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;





Java cod……
[/Quote]
非常感谢，使用你的这种方法果然是可以了。
超超王贱贱 2010-06-04
  • 打赏
  • 举报
 回复
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
liuyyuns 2010-06-04
  • 打赏
  • 举报
 回复
[Quote=引用 18 楼 abing0214 的回复:]
SQL code

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_……
[/Quote]

下面这样就可以了：


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);

		}

	}
超超王贱贱 2010-06-04
  • 打赏
  • 举报
 回复 


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;
liuyyuns 2010-06-04
  • 打赏
  • 举报
 回复
[Quote=引用 16 楼 aa644509120 的回复:]
cstmt = conn.prepareCall("{call query_student()}");
cstmt.execute();
[/Quote]
这样不报错了，但是我怎么能在java中获取存储过程查询的结果呢？
whlcy 2010-06-04
  • 打赏
  • 举报
 回复
cstmt = conn.prepareCall("{call query_student()}");
cstmt.execute();
liuyyuns 2010-06-04
  • 打赏
  • 举报
 回复
[Quote=引用 14 楼 justlearn 的回复:]
stmt.execute();
这样呢
[/Quote]


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)
justlearn 2010-06-03
  • 打赏
  • 举报
 回复
stmt.execute();
这样呢
leisore 2010-06-03
  • 打赏
  • 举报
 回复 


/*

 * 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)
liuyyuns 2010-06-03
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 cjnba 的回复:]
如果要返回值得

cstmt=conn.preparCall("call query_student（？，？）");
cstmt.setString(1,"aa");//第一个必须是输入的，不能是输出
cstmt=registerOutParameter(2,java.sql.Types.VARCHAR);//输出
stmt.executeUpdate();
String val=cs……
[/Quote]
我没有需要输入的参数，怎么能有输入参数呢？
mmkkuoi 2010-06-03
  • 打赏
  • 举报
 回复
如果要返回值得

cstmt=conn.preparCall("call query_student（？，？）");
cstmt.setString(1,"aa");//第一个必须是输入的，不能是输出
cstmt=registerOutParameter(2,java.sql.Types.VARCHAR);//输出
stmt.executeUpdate();
String val=cstmt.getString(2);
mmkkuoi 2010-06-03
  • 打赏
  • 举报
 回复
conn = JDBCUtils.getConnection(....."miniWeb", "miniWeb");
少url
liuyyuns 2010-06-03
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 goldenfish1919 的回复:]
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{call query_student}");
试试
[/Quote]
这样也不行。
liuyyuns 2010-06-03
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 goldenfish1919 的回复:]
要不就在存储过程中定义一个输出参数，作为返回值
CallableStatement cstmt = con.prepareCall("{call query_student)}");
cstmt.execute();
System.out.println(cstmt.getString(1));
试试
[/Quote]
在存储过程里面写一个返回参数也不行。
liuyyuns 2010-06-03
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 crazylaa 的回复:]
cstmt = conn.prepareCall("{call query_student}");
------------->这里貌似需要括号，有点忘了
cstmt = conn.prepareCall("{call query_student()}");
[/Quote]
加括号也不行。
加载更多回复（6）
java调用sql函数_JAVA调用SQL存储过程报错
博客讲述Java程序中调用SQL存储过程时出现问题，之前正常，现在一调用就报错，提示'{'附近有语法错误。无论是新写的简单存储过程，还是以前的存储过程，在查询分析器里都能执行，但在程序中执行executeQuery时就出错，求解原因。
t4cttioer.java_在JAVA程序中调用一个存储过程报错！
博客展示了一段Java代码，该代码尝试调用Oracle存储过程。代码中使用了Java的JDBC相关类，如CallableStatement、Connection等。运行代码时出现了java.sql.SQLException报错，错误信息显示PLS - 00103，提示符号使用问题。
java调用oracle过程,JAVA调用ORACLE存储过程报错
本文详细记录了在JAVA中调用ORACLE存储过程时遇到的错误，包括`StringIndexOutOfBoundsException`异常的堆栈信息。错误发生在执行CallableStatement时，可能与参数设置或存储过程本身有关。解决思路包括检查存储过程参数的传递方式和顺序，确保从0开始计数。
java的absolute如何用,java调用存储过程返回rs.absolute(1)报错,该如何解决
本文介绍了如何解决Java通过CallableStatement调用Oracle存储过程时，使用rs.absolute(1)方法报错的问题，包括异常处理、数据库连接管理及结果集操作技巧。
java调用SQLserver 存储过程无返回数据无报错信息
博客指出在调用数据时，存在实际有数据但调用后无报错信息的情况，解决办法是在存储过程第一行添加set nocount on。
Java SE

62,621

社区成员

307,251

社区内容

发帖
与我相关
我的任务
社区描述
Java 2 Standard Edition
社区管理员
  • Java SE
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章