Spring StoredProcedure调用ORACLE存储过程或函数

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

废话不说，直接上代码：

应用实例

//引用方式package.function或package.procedure
StoredProcedure sp = new org.springframework.jdbc.object.StoredProcedure(ds, "PACKAGE_NAME.PROCEDURE_NAME");
//调用函数时必须，调用存储过程不要
sp.setFunction(true);
//设置返回参数名(将来通过此名称获取输出的返回结果),返回参数需在IN参数前定义
//返回类型Types.ARRAY，自定义类型名称（自定义oracle的TYPE必须在package之上定义，可以在schema层次定义，package内不支持，参见下段“无效的名称模式”）
//sp.declareParameter(new SqlOutParameter("result", Types.INTEGER)); //输出INTEGER类型
sp.declareParameter(new SqlOutParameter("result", Types.ARRAY, "NUMBER_ARRAY")); //返回table类型

//设置输入参数
sp.addParameter("p_name", "广州");
//定义返回对象
Map<String, Object> result = null;
//执行。存在ORA04068异常的可能，若发生则再试执行一次。
try{
	result = sp.execute();
}catch(UncategorizedSQLException e){				
	String state = e.getSQLException().getSQLState();
	int code = e.getSQLException().getErrorCode();
	if("72000".equals(state) && code == 4068){ //ORA-04068 detected
		result = sp.execute();
	}else{
		throw e;
	}
}finally{
	
}

//强制转换返回结果，对应oracle.sql.ARRAY
//Integer returnCode = (Integer) result.get("result");
ARRAY r = (ARRAY)result.get("result");
//结果处理
long[] la = null;
try{
	la = r.getLongArray();   //简单获取，复杂的使用Datum获取
	if(la!=null && la.length>0){
		System.out.println(la[0]);
	}else{
		System.out.println("no data");
	}
}catch(Exception e){
	System.out.println("ERROR");
}

关于包失效

当前session处在活动状态，如果此时ORACLE的package在另外一个session中被重新编译，那么当前session再执行其中的procedure就会报包失效，异常STACK见下。

报错后oracle会自动更新此session中的包状态，所以再次执行则会成功，如前文代码所示。

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call PACKAGE_NAME.FUNCTION_NAME(?)}]; SQL state [72000]; error code [4068]; ORA-04068: 已丢弃程序包  的当前状态
ORA-04061:  的当前状态失效
ORA-04061: package body "SCHEMA_NAME.PACKAGE_NAME" 的当前状态失效
ORA-04065: 未执行, 已变更或删除 package body "SCHEMA_NAME.PACKAGE_NAME"
ORA-06508: PL/SQL: 无法找到正在调用 : "SCHEMA_NAME.PACKAGE_NAME" 的程序单元
ORA-06512: 在 "SCHEMA_NAME.PACKAGE_NAME", line 432
ORA-06512: 在 "SCHEMA_NAME.PACKAGE_NAME", line 192
ORA-06512: 在 line 1
; nested exception is java.sql.SQLException: ORA-04068: 已丢弃程序包  的当前状态
ORA-04061:  的当前状态失效
ORA-04061: package body "SCHEMA_NAME.PACKAGE_NAME" 的当前状态失效
ORA-04065: 未执行, 已变更或删除 package body "SCHEMA_NAME.PACKAGE_NAME"
ORA-06508: PL/SQL: 无法找到正在调用 : "SCHEMA_NAME.PACKAGE_NAME" 的程序单元
ORA-06512: 在 "SCHEMA_NAME.PACKAGE_NAME", line 432
ORA-06512: 在 "SCHEMA_NAME.PACKAGE_NAME", line 192
ORA-06512: 在 line 1
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:124)
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:322)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:952)
	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:985)
	at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:117)

无效的名称模式

当package中使用了自定义array类型时，jdbc调用会出现”无效的名称模式”错误提示。

原因摘自网络：

To my knowledge, the Oracle JDBC driver does not support using the ArrayDescriptor for array data types (varray or nested table) that are defined inside of a package. The same is true for StructDescriptor as well. If you want to use array and object data types, you must define them outside of a package. Then you’ll be able to use the descriptors in your JDBC programs.

As far as I know, you can only create an “ArrayDescriptor” and a “StructDescriptor” for database types.
In other words, types that were created using the CREATE TYPE (DDL) statement.
You cannot create an “ArrayDescriptor” or a “StructDescriptor” for types created in PL/SQL packages

oracle jdbc代码中

ArrayDescriptor.createDescriptor(type, conn);

其中的type只是单独的名字，默认前面是schema，但是不能加package。只有schema级别对象类型是可以的。
解决的办法供参考（1）尝试对package.type建立一个同义词了（2）定义schema级别的对象(自定义类型放在包外)