Pass Array From Java to PLSQL Stored Procedure

Question

I am trying to pass Java array to PLSQL stored procedure, however when I am trying to execute, I am getting the following exception

java.sql.SQLException: Inconsistent java and sql object types

My DAO class code snippet

List projectList = new ArrayList();

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

callablestatement = 
         (OracleCallableStatement)connection.prepareCall("{call my_proc(?)}");

Object[] project1 = projectList.toArray();

StructDescriptor projectTypeDesc = StructDescriptor.createDescriptor("MY_TYPE",
conn);

STRUCT structProject1 = new STRUCT(projectTypeDesc, 
connection, project);

STRUCT[] structArrayOfProjects = {structProject1};

 ArrayDescriptor projectTypeArrayDesc = ArrayDescriptor.createDescriptor
("MY_ARRAY", connection); 

ARRAY arrayOfProjects = new ARRAY(projectTypeArrayDesc, connection,
structArrayOfProjects);// error in this line

callablestatement.setArray(1, arrayOfProjects);

How can I resolve this issue?

Edit 1

If I do as

Object[] project1 = new Object[]{project.getProjectId(), project.getProjectTitle()};

then no errors and records are inserted into table.

However if I would do as

Object[] project1 = projectList.toArray();

then exception is thrown Inconsistent java and sql object types

You are doing something strange here, you didn't get my point in this thread: [Pass Array from Java to Oracle](http://stackoverflow.com/questions/19888520/pass-array-from-java-to-oracle-java-sql-sqlexception-fail-to-convert-to-intern). Is there any particular reason why you are trying to pass an array from Java to Oracle with just a single element in it? Anyway, please provide code of the `Project` class as well as the definition of the `MY_TYPE` and `MY_ARRAY` types. — Przemyslaw Kruglej, Nov 10 '13 at 19:54
@PrzemyslawKruglej If I directly assign value to Object[], then it does insert values to table, however if I do `projectList.toArray()`, I am getting errors. I have updated my question with this. Do I need to manually assign all values to Object[] or I can I loop through `projectList` and add to `Object[]` ? — Jacob, Nov 11 '13 at 05:31

Przemyslaw Kruglej · Accepted Answer · 2013-11-11T10:36:53.030

As I have already said in the other thread, you have to assign fields of Project object to an array of Object, while you are assigning the whole array of Project objects. You can do what you want, but, as I have already said in the other thread, you have to loop through the list of projects and create a STRUCT object for each element in the list, holding each project's fields:

StructDescriptor projectTypeDesc = StructDescriptor.createDescriptor("MY_TYPE",
connection);

// array holding structs, where each struct is based on an array
//   with fields of Project object
STRUCT[] projectsAsStructs = new STRUCT[projectList.size()];

// for each Project object, create a STRUCT object containing its fields
for (int i = 0; i < projectList.size(); ++i) {
  Project project = projectList.get(i);

  Object[] projectFields = new Object[] {project.getProjectId(),
                                         project.getProjectTitle()};

  STRUCT projectStruct = new STRUCT(projectTypeDesc,
                                    connection, projectFields);

  projectsAsStructs[i] = projectStruct;
}

// now you have all your Project objects ready to be saved in one go:
ArrayDescriptor projectTypeArrayDesc = ArrayDescriptor.createDescriptor
("MY_ARRAY", connection); 

ARRAY arrayOfProjects = new ARRAY(projectTypeArrayDesc, connection,
                                  projectsAsStructs);

callablestatement.setArray(1, arrayOfProjects);

Yes this did help. Thanks a lot. One or typo `Objects[]` should be `Object[]` and `getSize()` should be `size()` — Jacob, Nov 11 '13 at 10:27
By the way perhaps if you have not seen this http://stackoverflow.com/questions/19887193/declare-array-of-tabletype I am trying to use associative arrays so that I can use TABLE type instead of manually typing all columns in database table. My question does this approach would work if I am trying to pass array from Java to procedure? — Jacob, Nov 11 '13 at 10:30
@Polppan Thanks for typos, I'll edit my answer - I wrote it from the top of my head. Regarding your second comment, I don't understand what you are trying to achieve. Best if you would edit the question that you provided the link to, and write what you actually want to do, point by point what are your goals, what functionality you want to have, on which side(Java/Oracle), etc., then maybe I'll be able to help you. — Przemyslaw Kruglej, Nov 11 '13 at 10:36

Pass Array From Java to PLSQL Stored Procedure

1 Answers1