Pass Array From Java to PLSQL Stored Procedure
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);
Jacob
Downvote, at no time in the past or hitherto; not ever.
Updated on November 25, 2022Comments
-
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 almost 10 yearsYou 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 theMY_TYPE
andMY_ARRAY
types. -
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 throughprojectList
and add toObject[]
?
-
-
Jacob almost 10 yearsYes this did help. Thanks a lot. One or typo
Objects[]
should beObject[]
andgetSize()
should besize()
-
Jacob almost 10 yearsBy the way perhaps if you have not seen this 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?
-
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.