Oracle探究_JAVA存储过程_开发指引《中》

HLS HLS/MD120

1.5 返回PLSQL集合变量

有一种业务场景是在主逻辑处理完后需要返回多个数据。

结合JAVA不能类似PLSQL存储过程有OUT参数,只能开发返回函数形式的Method，以下

将介绍JAVA存储过程返回PLSQL集合变量的方式.

1.开发思路

(1)Oracle DB中声明Table集合变量；

(2)JAVA中使用ArrayDescriptor对象描述TABLE集合变量;

(3)创建JAVA数组存储变量;

（4）使用oracle.sql.ARRAY匹配映射PLSQL中的Table集合变量

2.示例

Company Confidential – For BDLL & HAND use only 7

HLS HLS/MD120

} catch (Exception e) {

// System.err.println(e.getMessage());

e.printStackTrace();

l_err_msg = "GetJdbcConnection-" + e.getMessage();

l_rtn_status = "E";

// throw new Exception("GetJdbcConnection-"+l_err_msg);

}

//..............

//省掉中间内容

//处理返回值

ArrayDescriptor l_rtn_array_descriptor =

ArrayDescriptor.createDescriptor("CUX_PER_RTN_JPC_MSG_T", ws_conn); if (l_rtn_status.equals("S")) {

l_rtn_data[0] = l_rtn_status;

l_rtn_data[1] = "";

l_rtn_data[2] =g_esb_batch_no;

l_rtn_array =

new ARRAY(l_rtn_array_descriptor, ws_conn, l_rtn_data);

} else {

l_rtn_data[0] = l_rtn_status;

l_rtn_data[1] = l_err_msg;

l_rtn_data[2] =g_esb_batch_no;

l_rtn_array =

new ARRAY(l_rtn_array_descriptor, ws_conn, l_rtn_data);

}

ws_conn.commit();

ws_conn.close();

return l_rtn_array;

}

Company Confidential – For BDLL & HAND use only 8

HLS HLS/MD120

l_rtn_line_t cux_per_rtn_jpc_msg_t;

l_msg_data VARCHAR2(1000);

BEGIN

x_return_status := hss_api.start_activity(p_pkg_name => g_pkg_name, p_api_name => l_api_name,

p_savepoint_name => l_savepoint_name, p_init_msg_list => p_init_msg_list);

OPEN csr_line; FETCH csr_line

INTO l_esb_base_data,

l_esb_biz_data;

CLOSE csr_line;

l_rtn_line_t := cux_per_rtn_jpc_msg_t();

----调用解析程序解析数据，并插入数据中间表中

l_rtn_line_t.delete;

l_rtn_line_t := PraseMainProcess(p_base_parameter => l_esb_base_data, p_biz_parameter => l_esb_biz_data,

p_batch_id => g_batch_id);

log('l_rtn_line_t(1)=' || l_rtn_line_t(1));

log('l_rtn_line_t(2)=' || l_rtn_line_t(2));

log('l_rtn_line_t(3)=' || l_rtn_line_t(3));

IF g_rtn_message_tbl(1).process_status <> 'S' THEN

--l_err_message := l_err_message || x_msg_data;

x_return_status := fnd_api.g_ret_sts_error;

raise_exception(x_return_status);

END IF;

x_return_status := hss_api.end_activity(p_pkg_name => g_pkg_name, p_api_name => l_api_name,

p_commit => p_commit,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data);

EXCEPTION

WHEN fnd_api.g_exc_error THEN

WHEN fnd_api.g_exc_unexpected_error THEN

WHEN OTHERS THEN

END process_json;

1.6 发布程序工具-LoadJava

对于开发的程序最终是通过LOADJAVA程序发布进数据,后面内容中也会介绍更加

快捷的Jdeveloper发布进数据库,但归根结底仍是使用了LOADJAVA程序。

1.LoadJava说明

Loadjava是加载Java源文件、Java类文件和Java资源文件的实用程序，它可以用来

验证字节码，并将Java类和JAR文件布置到数据库中。它既可以通过命令行调用，也

可以通过包含于DBMS_JAVA类中的loadjava()方法调用。如下为一JAR程序装载进数

据库:

Company Confidential – For BDLL & HAND use only 9

HLS HLS/MD120

username/password[@database]为要导入的目标ORACLE DB,其中格式-option选项如下:

2.DropJava说明 Dropjava主要用于从ORACLE DB中删除由loadjava程序上载的JAVA程序。 10

Company Confidential – For BDLL & HAND use only

HLS HLS/MD120

在删除JAVA程序时,文件名必须与loadjava时使用的”文件路径+文件名”必须一致,不然命令不能够找到对应JAVA程序进行删除。如：”
/d01/oracle/testappl/cux/11.5.0/admin/sqljdbc.jar”两着必须一致。

3.可能出现的问题 在loadjava过程中，可能存在这样那样的问题导致不成功。 1) 查错:除命令行抛出的错,数据库中也可以查询对应JAVA的错误;

2) DB服务器环境变量问题； 主要可能是由于ORACLE_HOME,JAVA_HOME,CLASSPATH这些环境变量没有设置,以linux为例可通过export命令修改SESSION层得环境变量。如 3) 出现异常: ClassNotFound或者ORA-29534: referenced object <class name> could not be resolved error错误； 原因说明: 所有装载进数据库的CLASS都引用到了装载到数据库某一SCHEMA的CLASS。已存在于DB中预定义的JAVA应用程序,如java.lang.*(JDK),都被装载到了PUBLIC SCHEMA中,所以所有程序都能访问使用预定义的程序。若要装载的CLASS引用了其他外部CLASS，必须将外部CLASS装载到同一个SCHEMA中。 比如,当前SCHEMA为SCOTT,装载有依赖其他CLASS的CLASS时,loadjava在解析时会先在SCOTT SCHEMA搜索是否存在依赖的CLASS，若不存在会进一步在PUBLIC SCHEMA下搜索,若搜锁不到，则会抛出以上异常. 解决办法2个: 11

Company Confidential – For BDLL & HAND use only

HLS HLS/MD120

a) 找到对应引用类,装载入同一个SCHEMA;

b) 若知道引用的类在哪个SCHEMA存在,比如在APPS,则使用命令:

4) Loadjava: No such file or directory 由于克隆环境导致LOADJAVA环境变量读取错误:
/u01/PROD/db/tech_st/11.2.0/jdk/jre//bin/java: No such file or directory; 但其实正确的目录应为/u01/TEST下的ORACLE_HOME. 此问题的解决办法是检查并修改$ORACLE_HOME\bin\loadjava的程序文件,将其
/u01/PROD/db/tech_st/11.2.0改为$ORACLE_HOME 12

Company Confidential – For BDLL & HAND use only

HLS HLS/MD120

2.开发实例

2.1 实例说明

1.实例逻辑 实例简单逻辑是将ORACLE DB一张TABLE的数据传递到SQL SEVER数据库中;

2.环境 1) DB:ORACLE 11G,9i以上数据库即可支持; 2) IDE开发工具:Jdeveloper 10G,若要用IDE直接发布程序,Jdeveoper版本必须为10G版本以上; 3) SQL SERVER 2005; 3.实例相关数据库对象 此步创建本实例所需的相关数据库对象

13

Company Confidential – For BDLL & HAND use only

LAST_UPDATE_LOGIN NUMBER, ATTRIBUTE_CATEGORY VARCHAR2(30), ATTRIBUTE1 VARCHAR2(240), ATTRIBUTE2 VARCHAR2(240), ATTRIBUTE3 VARCHAR2(240), ATTRIBUTE4 VARCHAR2(240), ATTRIBUTE5 VARCHAR2(240), ATTRIBUTE6 VARCHAR2(240), ATTRIBUTE7 VARCHAR2(240), ATTRIBUTE8 VARCHAR2(240), ATTRIBUTE9 VARCHAR2(240), ATTRIBUTE10 VARCHAR2(240), ATTRIBUTE11 VARCHAR2(240), ATTRIBUTE12 VARCHAR2(240), ATTRIBUTE13 VARCHAR2(240), ATTRIBUTE14 VARCHAR2(240), ATTRIBUTE15 VARCHAR2(240) ); comment on table CUX.CUX_PUB_DB_INFO is 'DB信息维护'; comment on column
CUX.CUX_PUB_DB_INFO.DB_SEQ_ID is '序列'

; comment on column
CUX.CUX_PUB_DB_INFO.DB_KEY is 'DB连接简称'; comment on column
CUX.CUX_PUB_DB_INFO.DB_TYPE is 'DB类型:SQLSERVER;DB2;ORACLE;SYBASE;MYSQL'; comment on column
CUX.CUX_PUB_DB_INFO.JDBC_DRIVER is 'JDBC 驱动'; comment on column
CUX.CUX_PUB_DB_INFO.DB_URL is 'JDBC 连接'; comment on column
CUX.CUX_PUB_DB_INFO.USER_NAME is '用户'; comment on column CUX.CUX_PUB_DB_INFO.PWD is '密码'; comment on column
CUX.CUX_PUB_DB_INFO.DB_DESC is 'DB连接描述'; comment on column
CUX.CUX_PUB_DB_INFO.OBJECT_VERSION_NUMBER is '行版本号，用来处理锁'; alter table CUX.CUX_PUB_DB_INFO add constraint CUX_PUB_DB_INFO_PK primary key (DB_KEY); Create Unique Index CUX.CUX_PUB_DB_INFO_U1 On CUX.CUX_PUB_DB_INFO(DB_SEQ_ID) tablespace APPS_TS_TX_IDX; Create Sequence CUX.CUX_PUB_DB_INFO_S Start With 10001; Create Synonym APPS.CUX_PUB_DB_INFO_S For CUX.CUX_PUB_DB_INFO_S; Create Synonym APPS.CUX_PUB_DB_INFO For CUX.CUX_PUB_DB_INFO; 14 Company Confidential – For BDLL & HAND use only

2.2 创建JAVA程序

1.创建JAVA PROJECT

进入JDEV 10G->NEW PROJECT:CUXEMPJPC

2.设置JDEV工具的JDK与管理JAR

JAVA PROCEDURE基本工作环境为ORACLE数据库本身的JVM，因而

开发的要装载进DB的JAVA程序编译所用的JDK最好与ORACLE DB所

使用JDK版本一致或者相兼容;

(1)本实例使用11G数据库,如下为数据库JDK版本1.5

Company Confidential – For BDLL & HAND use only 15

(2)添加

JDK PATH

(3)更改

JDK

Company Confidential – For BDLL & HAND use only 16

3.创建JAVA程序

（1）.使用NEW..

.

（2）.编写JAVA程序

Company Confidential – For BDLL & HAND use only 17

// conn=DriverManager.getConnection(url,userName,password);

conn = DriverManager.getConnection(remote_url);

System.out.println("REMOTE Connection Successful!");

} catch (Exception e) {

errmsg = e.getMessage();

e.printStackTrace();

}

return conn;

}

public Connection getlocalConnection() throws Exception {

Connection local_conn = null;

try {

// Class.forName("oracle.jdbc.driver.OracleDriver");

// local_conn

=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","scott","orcl");

local_conn =

DriverManager.getConnection("jdbc:default:connection:");

System.out.println("Local DB Connection Successful!");

} catch (Exception e) {

errmsg = errmsg + e.getMessage();

e.printStackTrace();

}

return local_conn;

}

public String geterr() {

return this.errmsg;

}

public static void emp_transfer(String rt_url,

String rt_driver) throws SQLException {

cuxemptransfer cef = new cuxemptransfer();

cef.remote_driver = rt_driver;

cef.remote_url = rt_url;

Connection remote_conn = null;

Connection localconn = null;

String rt_sql =

"INSERT INTO cux_hr_people_import_temp(employee_number, last_name, pos_date, sex, sfz_num) " +

"VALUES(?, ?, ?, ?, ?)";

String local_sql =

"select employee_number, last_name, pos_date, sex, sfz_num from

cux_hr_people_import_temp";

try {

remote_conn = cef.getConnection();

localconn = cef.getlocalConnection();

Statement local_stmt = localconn.createStatement();

ResultSet res = local_stmt.executeQuery(local_sql);

//传递数据到异构数据库

PreparedStatement rt_pstmt = remote_conn.prepareStatement(rt_sql); while (res.next()) {

rt_pstmt.setString(1, res.getString("EMPLOYEE_NUMBER"));

rt_pstmt.setString(2, res.getString("LAST_NAME"));

rt_pstmt.setDate(3, res.getDate("POS_DATE"));

rt_pstmt.setString(4, res.getString("SEX"));

rt_pstmt.setString(5, res.getString("SFZ_NUM"));

//批语句

rt_pstmt.addBatch();

}

// rt_pstmt.execute();

int[] updateCounts = rt_pstmt.executeBatch();

Company Confidential – For BDLL & HAND use only 18

对于学习有困难不知道如何提升自己可以加扣：578024144进行交流得到帮助，获取学习资料