"SQLException:ORA-06550";从Java调用PL/SQL函数时 [英] "SQLException: ORA-06550" when calling PL/SQL function from Java
问题描述
我想使用Java从数据库中检索数据并显示它,所以我创建了这个PL/SQL函数,该函数返回一个游标:
I want to use Java to retrieve data from database and display it, so I created this PL/SQL function, which returns a cursor:
create or replace function std_getInfoFunc return types.cursortype
as
my_cursor types.cursorType;
begin
open my_cursor FOR
SELECT s.FirstName, s.LastName, s.Address, s.City, s.Province
, s.PostalCode, c.CourseName
FROM Students s, Courses c, StudentAndCourses cs
Where s.StudentID = cs.StudentID
AND c.CourseID = cs.CourseID;
Return my_cursor;
end;
在我的Java代码中,我按如下方式调用该函数:
In my Java code, I call the function as follows:
try{
CallableStatement cst=connection.prepareCall("{? = call std_getInfoFunc}");
cst.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cst.execute();
res=(ResultSet) cst.getObject(1);
while(res.next()){
System.out.println(res.getString(1));
}
}
catch(SQLException e){
e.printStackTrace();
}
finally{
res.close();
cst.close();
conn.close();
}
代码产生以下异常:
run:
java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00905: object SAS.STD_GETINFOFUNC is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:202)
at
oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1005)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4710)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
at DBControler.viewStdInfo(DBControler.java:95)
at Test_02.main(Test_02.java:18)
为什么?
推荐答案
错误的可能原因是:(在代码段-函数中)
Possible causes of error are : (in the snippet - function)
1. 包类型"不存在.
1. The package "types" doesn't exists.
create or replace package types
as
type cursorType is ref cursor;
end;
/
2无效的SQL select语句(创建函数后已删除或更改表或列).
2 Invalid SQL select statement (Table or column has been dropped or altered after creating the function).
您需要使用simple
sql语句编写new function
,并在Sql
提示符下并使用Java
代码运行它.
You need to write a new function
with simple
sql statement and run it at Sql
prompt and with Java
code.
这篇关于"SQLException:ORA-06550";从Java调用PL/SQL函数时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!