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;

[possible duplicate](http://stackoverflow.com/q/8757264/1113392) — A4L, Nov 10 '13 at 10:51
Have you defined a type mapping as documented in [Working with Oracle Object Types - Creating and Using Custom Object Classes for Oracle Objects](http://docs.oracle.com/cd/B10501_01/java.920/a96654/oraoot.htm#1039529)? — halfbit, Nov 10 '13 at 11:39
@halfbit From that document, I couldn't understand much on how to define type mapping. — Jacob, Nov 10 '13 at 11:59

Przemyslaw Kruglej · Accepted Answer · 2013-11-10T12:29:56.133

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

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? — Jacob, Nov 10 '13 at 12:25
@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. — Przemyslaw Kruglej, Nov 10 '13 at 12:29
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, Nov 10 '13 at 12:37
@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`, 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). — Przemyslaw Kruglej, Nov 10 '13 at 12:43
@user2227422 did you import `import oracle.sql.STRUCT;`? What Java and JDBC versions are you using? — Przemyslaw Kruglej, Aug 27 '15 at 18:14
@PrzemyslawKruglej I got it working after starting with your code & adding what I needed. I am not sure what was causing the error. — Acroyear, Aug 27 '15 at 18:41
Hi Przemyslaw Kruglej.. vey nicely explained answer. May i please request you to take a look at my question: http://stackoverflow.com/questions/36808408/jdbc-call-to-stored-procedure-which-take-array-as-parameter i get the same error.java.sql.SQLException: Fail to convert to internal representation: — fiddle, Apr 24 '16 at 08:57

score 12 · Answer 2 · answered Aug 27 '15 at 18:37

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;
  }
}

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

2 Answers2

Linked