Pass array from Java to Oracle: java.sql.SQLException: Fail to convert to internal representation:error

Question

I have the following in DAO and when I execute, I am getting

java.sql.SQLException: Fail to convert to internal representation: test.Project@843

DAO Code

List projectList = new LinkedList();

public void saveRecord(List<Project> project) 
                       throws DatabaseException,SQLException {

    for (Project items: project) {
        insertRecord(items);
    }
}

private void insertRecord(Project project) throws SQLException {
    projectList.add(project);
    try{
        ArrayDescriptor desc = 
                ArrayDescriptor.createDescriptor("MY_ARRAY", dbConn);

        // execpetion in this line
        ARRAY arr = new ARRAY(desc, dbConn, (Object[])projectList.toArray());

How can I resolve this issue?

Edit 1

CREATE OR REPLACE TYPE project_type as object( 
proj_id varchar2 (10),
proj_title varchar2 (10));


create or replace  type my_array as Table of project_type;

Have you defined a type mapping as documented in Working with Oracle Object Types - Creating and Using Custom Object Classes for Oracle Objects? — halfbit
– halfbit, Commented Nov 10, 2013 at 11:39
@halfbit From that document, I couldn't understand much on how to define type mapping. — Jacob
– Jacob, Commented Nov 10, 2013 at 11:59

Przemyslaw Kruglej · Accepted Answer · 2013-11-10 12:29:56Z

16

Unfortunately, this is more complicated than one might expect. You have to use STRUCT objects, descriptors and, finally, ARRAY. Below is a working example.

-- Database code --

CREATE TABLE project_types (
  proj_id VARCHAR2(10),
  proj_title VARCHAR2(10)
);
/

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

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);
    END LOOP;
  END IF;
END;
/

// Java code - main class

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

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class ArrayExampleMain {

  public static void main(String[] args) throws Exception {
    OracleConnection conn = getOracleConnection().unwrap(OracleConnection.class);
    System.out.println("Got Connection.");

    OracleCallableStatement callStmt = null;

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

      // create array holding values for ProjectType object's properties
      Object[] project1 = new Object[] {"1", "Title 1"};
      Object[] project2 = new Object[] {"2", "Title 2"};

      // descriptor for OBJECT type defined in database
      StructDescriptor projectTypeDesc = StructDescriptor.createDescriptor("PROJECT_TYPE", conn);

      // each struct is one ProjectType object
      STRUCT structProject1 = new STRUCT(projectTypeDesc, conn, project1);
      STRUCT structProject2 = new STRUCT(projectTypeDesc, conn, project2);

      STRUCT[] structArrayOfProjects = {structProject1, structProject2};

      // descriptor of TABLE type defined in database
      ArrayDescriptor projectTypeArrayDesc = ArrayDescriptor.createDescriptor("MY_ARRAY", conn);

      // array holding two ProjectType objects
      ARRAY arrayOfProjects = new ARRAY(projectTypeArrayDesc, conn, 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();
     }
  }

  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@YOUR_HOST:orcl";
    String username = "hr";
    String password = "password";

    Class.forName(driver); // load Oracle driver

    Connection conn = DriverManager.getConnection(url, username, password);

    return conn;
  }
}

Checking content of the project_types table after execution of main class:

SELECT * FROM project_types;

Output:

PROJ_ID    PROJ_TITLE
---------- ----------
1          Title 1    
2          Title 2

edited Nov 10, 2013 at 12:29

answered Nov 10, 2013 at 12:04

Przemyslaw Kruglej

8,1412 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Jacob Over a year ago

Thanks for this, I would like to know where does ProjectType class is being referred? is it being called in any other class? So how does it helps PROJECT_TYPE?

Przemyslaw Kruglej Over a year ago

@Polppan Well, it appears that, taking into account the way I have done it, ProjectType class is not really needed for anything... will edit my answer.

Jacob Over a year ago

Yes this works well. I have a question, how can I use a List or ArrayList and map it Object[] or can I directly use List?

Jacob Over a year ago

I have forgotten, +1 for your solution

Przemyslaw Kruglej Over a year ago

@Polppan I don't know how to just pass some object and let Java and JDBC map it to database object type. You have to get each object from your list and create an array which will hold value for each of that object's fields, then create a STRUCT from that array. So, if you have a list of type ArrayList<ProjectType>, you have to loop through it, and create an array: Object[] projectFields = new Object[]{currentElementFromTheList.getId(), currentElementFromTheList.getType()}; then create a STRUCT object on that array and, finally, put all STRUCT objects in STRUCT array (see my code).

|

Acroyear · Accepted Answer · 2015-08-27 18:37:26Z

Thanks, @PrzemyslawKruglej. I took the liberty of cleaning up the deprecated classes.

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

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

public class ArrayExampleMain {

  public static void main(String[] args) throws Exception {
    OracleConnection conn = getOracleConnection().unwrap(OracleConnection.class);
    System.out.println("Got Connection.");

    OracleCallableStatement callStmt = null;

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

      // create array holding values for ProjectType object's properties
      Object[] project1 = new Object[] {"1", "Title 1"};
      Object[] project2 = new Object[] {"2", "Title 2"};

      // 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 = 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();
     }
  }

  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@YOUR_HOST:orcl";
    String username = "hr";
    String password = "password";

    Class.forName(driver); // load Oracle driver

    Connection conn = DriverManager.getConnection(url, username, password);

    return conn;
  }
}

Collectives™ on Stack Overflow

Pass array from Java to Oracle: java.sql.SQLException: Fail to convert to internal representation:error

2 Answers 2

10 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related