CODEWITHSUNDEEP
javaoracle-databasejdbcplsqloracle10g
Jacob
Jacob

Reputation: 14741

Pass Array From Java to PLSQL Stored Procedure

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

Upvotes: 1

Views: 10673

Answers (1)

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8123

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

Upvotes: 4

Related Questions