java中调存储过程和函数时出错!!!怎么解决,谢谢!
java.sql.SQLException: ORA-06550: 第 1 行, 第 13 列:
PLS-00201: 必须说明标识符 'T_PKG.GET_VALUE '
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:656)
at com.inventec.packages.JDBCDemo.test(JDBCDemo.java:45)
at com.inventec.packages.JDBCDemo.main(JDBCDemo.java:32)
我的代码:
--创建表
CREATE TABLE T
(COL1 NUMBER PRIMARY KEY, COL2 VARCHAR2(10));
insert into t values (1, 'Hello ');
insert into t values (2, 'World ');
--创建包和声明函数
CREATE OR REPLACE
PACKAGE T_PKG AS
G_COUNT NUMBER := 0;
FUNCTION GET_VALUE(p_col1 NUMBER) RETURN VARCHAR2;
PROCEDURE GET_VALUE2(p_col1 NUMBER, p_col2 OUT VARCHAR2);
END;
--创建包中函数的实现
create OR REPLACE
PACKAGE BODY T_PKG AS
FUNCTION GET_VALUE(p_col1 NUMBER) RETURN VARCHAR2
IS
v_value VARCHAR2(10);
BEGIN
g_count := g_count + 1;
SELECT col2 INTO v_value FROM t WHERE col1 = p_col1;
RETURN v_value|| ', Execution Count = '||g_count;
END;
PROCEDURE GET_VALUE2(p_col1 NUMBER, p_col2 OUT VARCHAR2)
IS
BEGIN
g_count := g_count + 1;
SELECT col2 INTO p_col2 FROM t WHERE col1 = p_col1;
p_col2 := p_col2|| ', Execution Count = '||g_count;
END;
END;
--在java中的实现------------
import java.sql.*;
import oracle.jdbc.driver.OracleDriver;
public class JDBCDemo {
static Connection conn;
public static void main(String[] args) {
try {
DriverManager.registerDriver( new OracleDriver());
conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:orcl ", "ora15 ", "oracle ");
test( "{? = call t_pkg.get_value(1)} ");
test( "{call t_pkg.get_value2(2, ?)} ");
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void test(String pStmt) throws SQLException {
CallableStatement stmt = conn.prepareCall(pStmt);
stmt.registerOutParameter(1, Types.VARCHAR);
stmt.execute();
String result = stmt.getString(1);
System.out.println( "Result is: " + result);
stmt.close();
}
}
JAVA:
/**
*
*/
package com.inventec.packages;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import oracle.jdbc.driver.OracleDriver;
/**
* @author Ivan Ling
* @2007-4-25 下午01:38:39
* @company
*/
public class JDBCDemo {
static Connection conn;
/**
* @param args
*/
public static void main(String[] args) {
try {
DriverManager.registerDriver( new OracleDriver());
conn = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:ORA9I ", "system ", "manager ");
//execute function
String sqlfunction= "{? = call T_PKG.GET_VALUE(1)} ";
test(sqlfunction);
//execute prodecure
String sqlproducer= "{call T_PKG.GET_VALUE2(2, ?)} ";
test(sqlproducer);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void test(String pStmt) throws SQLException {
//String pstmt= "select * from lhj.t ";
CallableStatement stmt = conn.prepareCall(pStmt);
stmt.registerOutParameter(1, Types.VARCHAR);
stmt.execute();
String result = stmt.getString(1);
System.out.println( "Result is: " + result);
stmt.close();
}
}
什么错误,谢谢!
[解决办法]
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html
[解决办法]
mark!
[解决办法]
如果包的名字没有错误的话，检查一下ORACLE用户是否有对象权限。
[解决办法]
在包名的前面加上用户名user.packageName，因为system用户只能通过用户名去访问不同用户下的对象，其他的用户没有这种权利
[解决办法]
这一看就知道是你的存储过程或者是函数没写对，自己在PLsql或者sqlplus上先运行下试试吧
