使用存储过程将java数组blob数据插入到oracle数据库(无法转换为内部表示:error)

Question

我想通过存储过程将 BLOB 数据数组保存到 Oracle DB。(引用此:Pass array from Java to Oracle: java.sql.SQLException: Fail to convert to internal representation:error)

但我已将 BLOB 列添加到结构中。这是我的数据库脚本和java代码。

--数据库代码-- 创建表项目类型( proj_id VARCHAR2(10), 项目标题 VARCHAR2(10), 项目数据 BLOB )； /

CREATE OR REPLACE TYPE project_type AS OBJECT ( 
proj_id VARCHAR2(10),
proj_title VARCHAR2(10),
proj_data BLOB
);
/

CREATE OR REPLACE TYPE my_array AS TABLE OF project_type;
/

CREATE OR REPLACE PROCEDURE add_projects(p_projects_array IN my_array)
AS
BEGIN
IF p_projects_array IS NOT NULL THEN
FOR v_i IN 1..p_projects_array.LAST
LOOP
INSERT INTO project_types
VALUES (p_projects_array(v_i).proj_id,
p_projects_array(v_i).proj_title,
p_projects_array(v_i).proj_data);
END LOOP;
END IF;
END;
/

我已将 BLOB 数据列添加到project_types 表和project_type TYPE 中，结构化程序。

--Table--
proj_data BLOB

-- TYPE--
proj_data BLOB

--StruedProcedure--
p_projects_array(v_i).proj_data

-- Java 代码 --

import java.sql.Array;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Struct;

import javax.sql.rowset.serial.SerialBlob;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleConnection;

public class calMilisecond {

public static void main(String[] args) throws Exception {
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String user = "testuser";
String password = "testuser123456";
Connection conn = DriverManager.getConnection(url, user, password);;

OracleCallableStatement callStmt = null;
try {
  callStmt = (OracleCallableStatement)conn.prepareCall("{call add_projects(?)}");

  Blob blob1 = new SerialBlob(("test1").getBytes());
  Blob blob2 = new SerialBlob(("test2").getBytes());
  // create array holding values for ProjectType object's properties
  Object[] project1 = new Object[] {"1", "Title 1", blob1};
  Object[] project2 = new Object[] {"2", "Title 2", null};

  // each struct is one ProjectType object
  Struct structProject1 = conn.createStruct("PROJECT_TYPE", project1);
  Struct structProject2 = conn.createStruct("PROJECT_TYPE", project2);

  Struct[] structArrayOfProjects = {structProject1, structProject2};

  // array holding two ProjectType objects
  Array arrayOfProjects = ((OracleConnection) conn).createOracleArray("MY_ARRAY", structArrayOfProjects);

  callStmt.setArray(1, arrayOfProjects); 
  callStmt.execute();
  //conn.commit();

  System.out.println("Committed.");
} catch (Exception e) {
  if (conn != null) try { conn.rollback(); } catch (Exception ex) { System.out.println("Rollback failed."); }
  throw e;
} finally {
  callStmt.close();
  conn.close();
 }    

}

-- 错误代码 --

//尝试将 blob 数据插入数据库时​​发生错误。//

Object[] project1 = new Object[] {"1", "标题 1", blob1};

Object[] project2 = new Object[] {"2", "标题 2", blob2};

java.sql.SQLException: Fail to convert to internal
representation:error at oracle.jdbc.oracore.OracleTypeBLOB.toDatum(OracleTypeBLOB.java:69) at oracle.jdbc.oracore.OracleType.toDatumInternal(OracleType.java:142) at oracle.sql.StructDescriptor.toOracleArray(StructDescriptor.java:741) at oracle.sql.StructDescriptor.toArray(StructDescriptor.java:1322) at oracle.sql.STRUCT.(STRUCT.java:136) at oracle.jdbc.driver.PhysicalConnection.createStruct(PhysicalConnection.java:8733)

-- 当我尝试使用 NULL 数据时没有错误 --

Object[] project1 = new Object[] {"1", "标题 1", null};

Object[] project2 = new Object[] {"2", "标题 2", null};

Please let me know how to solve this problem.

谢谢。

Answer 1

改变

Blob blob1 = new SerialBlob(("test1").getBytes());

到

  BLOB blob1 =  new BLOB((oracle.jdbc.OracleConnection)conn, ("test1").getBytes()); 

其中 BLOB 是 import oracle.sql.BLOB;

其余的代码应该没问题。