Pass Array From Java to PLSQL Stored Procedure

10,242

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);
Share:
10,242
Jacob
Author by

Jacob

Downvote, at no time in the past or hitherto; not ever.

Updated on November 25, 2022

Comments

  • Jacob
    Jacob 12 months

    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

    • Przemyslaw Kruglej
      Przemyslaw Kruglej almost 10 years
      You are doing something strange here, you didn't get my point in this thread: Pass Array from Java to Oracle. 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.
    • Jacob
      Jacob almost 10 years
      @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
    Jacob almost 10 years
    Yes this did help. Thanks a lot. One or typo Objects[] should be Object[] and getSize() should be size()
  • Jacob
    Jacob almost 10 years
    By the way perhaps if you have not seen this stackoverflow.com/questions/19887193/declare-array-of-tablet‌​ype 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?
  • Przemyslaw Kruglej
    Przemyslaw Kruglej almost 10 years
    @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.