java 兑现往oracle存储过程中传递二维数组参数

java 实现往oracle存储过程中传递二维数组参数

一、SQL代码
create or replace type t_cableLine_point as object(
ID NUMBER(10),
CABLELINEID NUMBER(10),
ROADPOINTID NUMBER(10),
ORDERNUM NUMBER(10),
REMARK NUMBER(10)
)

CREATE OR REPLACE TYPE ARRAY_cableLine_point AS table OF t_cableLine_point

create table RSC_CABLELINE_POINT
(
ID NUMBER(10) not null,
CABLELINEID NUMBER(10) not null,
ROADPOINTID NUMBER(10) not null,
ORDERNUM NUMBER(10),
REMARK NUMBER(10)
)

create or replace procedure batch_cableline_point(i_object in ARRAY_cableLine_point) is
begin
insert into RSC_CABLELINE_POINT
(ID, CABLELINEID, ROADPOINTID, ORDERNUM, REMARK)
select ID, CABLELINEID, ROADPOINTID, ORDERNUM, REMARK
from the (select cast(i_object as ARRAY_cableLine_point) from dual);
end batch_cableline_point;

?

二、java测试代码

package com.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class Test {
??? public static void main(String[] args) {
??? ??? Connection con = null;
??? ??? PreparedStatement pstmt = null;
??? ??? try {
??? ??? ??? Class.forName("oracle.jdbc.driver.OracleDriver");
??? ??? ??? String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
??? ??? ??? con = DriverManager.getConnection(url, "test", "test");
??? ??? ??? String sql = "{call batch_cableline_point(?)}";
??? ??? ??? pstmt = con.prepareCall(sql);
??? ??? ??? Object[][] objects = new Object[10][5];
??? ??? ??? for (int i = 0; i < 10; i++) {
??? ??? ??? ??? Object[] object = new Object[5];
??? ??? ??? ??? object[0] = i;
??? ??? ??? ??? object[1] = 158870593;
??? ??? ??? ??? object[2] = 333;
??? ??? ??? ??? object[3] = 444;
??? ??? ??? ??? object[4] = 555;
??? ??? ??? ??? objects[i] = object;
??? ??? ??? }
??? ??? ??? oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor
??? ??? ??? .createDescriptor("ARRAY_CABLELINE_POINT", con);
??? ??? ??? oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc, con, objects);
??? ??? ??? pstmt.setArray(1, array);
??? ??? ??? pstmt.executeUpdate();
??? ??? } catch (Exception e) {
??? ??? ??? e.printStackTrace();
??? ??? } finally {
??? ??? ??? try {
??? ??? ??? ??? if (pstmt != null)
??? ??? ??? ??? ??? pstmt.close();
??? ??? ??? ??? if (con != null)
??? ??? ??? ??? ??? con.close();
??? ??? ??? } catch (Exception se) {
??? ??? ??? }
??? ??? }
??? }
}

?

三、遇到的问题

???? 1、java 调用oracle 数组出现??? 的乱码问题。

????????? 原因：缺少nls_charset12.jar包。

???? 2、java如何读取plsql的对象数组,抛出异常：无效的名称模式。

????????? 原因：“ARRAY_CABLELINE_POINT”不能为小写。