Adding multiple data using a CLOB

Question

I have this external oracle Stored Procedure which takes INTEGER,CLOB,VARCHAR as parameters, and which inserts a record to table upon executing. This will be called using a dao layer which consists of JAVA + Spring.

I have been asked to insert multiple records (1000s ) using the same procedure. so I am thinking of writing a pl/sql block which accepts either String or Clob and substrings the values in a loop which calls the procedure. For that I have to either append a String with delemeters for each record and pass it as a parameter or I could create a CLOB from that String and pass it as a parameter. Eg:String param ="value1,value2,value3 | value1,value2,value3 | value1,value2,value3 ..etc"

My questions are:

1. Is there a better solution than what I am thinking (because I think it is better to loop it inside the DB server rather than looping in DAO layer and making 1000s of DB calls)?

2. If I go ahead with my solution will there be limitations which prevents my effort, such as size of the data that I can pass to the pl/sql block?

Answer 1

I would refer you to this SO question:

Bulk insert from Java into Oracle

Basically, you should be doing a few bulk operations rather than thousands of individual ones.