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:

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

Community · Accepted Answer · 2017-05-23 12:27:29Z

0

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.

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Feb 1, 2012 at 14:07

anon

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding multiple data using a CLOB

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related