Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Passing list of objects to a PL/SQL stored procedure

 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a Java class called, "Sales", and it has two variables: salesPerson and numberOfSales. Suppose I have several objects of this class. How would I pass a list of Sales objects to a PL/SQL stored procedure?

In the PL/SQL code, how would I loop through this list of objects and display the contents of the variables of all the objects?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you don't know the number of items, it's probably a hard thing to do and probably very error-prone, similar to this article about splitting a list of integers.

I think maybe you could create a table with 3 columns: session_id, sales_person and number_of_sales.

So you generate a new session id, insert the list of Sales objects into this table with the generated session id and then invoke the stored procedure with the generated session id.
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:If you don't know the number of items, it's probably a hard thing to do and probably very error-prone, similar to this article about splitting a list of integers.

I think maybe you could create a table with 3 columns: session_id, sales_person and number_of_sales.

So you generate a new session id, insert the list of Sales objects into this table with the generated session id and then invoke the stored procedure with the generated session id.


Suppose I had an array consisting of tables with those three columns. How would I invoke the stored procedure with that array?
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fred Victa wrote:Suppose I had an array consisting of tables with those three columns.

An array consisting of tables
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Fred Victa wrote:Suppose I had an array consisting of tables with those three columns.

An array consisting of tables


I meant an array consisting of objects.

Each object has three variables.

When the array is passed to the stored procedure, how would the stored procedure access the contents of each object?

 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fred Victa wrote:When the array is passed to the stored procedure, how would the stored procedure access the contents of each object?

I have never passed an array of objects to a stored procedure. I don't even know if this is possible using JDBC. One of the things you could obviously do, is serialize this array and create a String. Then you could easily pass that String to your stored procedure and in the stored procedure you deserialize this string. But I would not recommend this approach, because it's really error prone.
That's why I suggested inserting all objects in a seperate table using a unique identifier and then pass this unique identifier to the stored procedure. Then the stored procedure can easily query this table using the unique identifier.
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:
Fred Victa wrote:When the array is passed to the stored procedure, how would the stored procedure access the contents of each object?

I have never passed an array of objects to a stored procedure. I don't even know if this is possible using JDBC. One of the things you could obviously do, is serialize this array and create a String. Then you could easily pass that String to your stored procedure and in the stored procedure you deserialize this string. But I would not recommend this approach, because it's really error prone.
That's why I suggested inserting all objects in a seperate table using a unique identifier and then pass this unique identifier to the stored procedure. Then the stored procedure can easily query this table using the unique identifier.


I changed my code so that it would convert an array of objects into an xml file and then I passed that xml file to the stored procedure. In the Ibatis SQL map, I'm using CLOB for the jdbcType and String for the javaType. The IN parameter which receives the xml file has the datatype, CLOB.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And how do you process this xml in the stored procedure to get the values you want? I expect it to be not that easy and could be error-prone as well. But that probably depends on the database support for xml handling.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry I'm chiming in late, but we have a wiki page that concerns this scenario.

And in this older, but valuable thread someone went through the entire process.

It shouldn't be particularly error-prone in my opinion, as long as the size of the list being passed between Java and PL/SQL remains reasonably low. Passing thousands of objects this way probably wouldn't be a good idea. Of course, this would be highly database-specific and nonportable.
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Roel De Nijs wrote:And how do you process this xml in the stored procedure to get the values you want? I expect it to be not that easy and could be error-prone as well. But that probably depends on the database support for xml handling.


Here is my code that processes the xml:

 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fred Victa wrote:Here is my code that processes the xml:

That looks pretty neat and concise!

Have a cow for coming back and sharing your solution! That might be helpful for other ranchers as well.
 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is some sample XML that my code will parse:

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic