wood burning stoves*
The moose likes JDBC and the fly likes Java and SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Java and SQL" Watch "Java and SQL" New topic
Author

Java and SQL

Himanshu Rawat
Ranch Hand

Joined: Nov 27, 2005
Posts: 141
Hi,

i don't know where to ask the problem so I'm asking here.

I need to delete some 5000 entries from oracle database through Java.

First approach was to create prepared statement with delete query and then delete one by one. But this approach is not efficient as entries might got 10K and is not constant.

Is there any other way to do it?? Like having PL/SQL procedure which can accept an array, vector something like that??

Please help

Cheers
Rawat


rawat
SCJP 1.4
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Is there a reason you can't write SQL along the lines of:


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Himanshu Rawat
Ranch Hand

Joined: Nov 27, 2005
Posts: 141

hi,

Thanks for quick reply

right now I am doing like this


value = primary key of table

This is called as many time as there are entires which i think is not good.

Comments please??

Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Swap your equals check to an in clause, e.g.

Be aware Oracle has a limit of 1000 things in a single in clause so you will have to or them together, e.g.:



Himanshu Rawat
Ranch Hand

Joined: Nov 27, 2005
Posts: 141
Hi Paul

Can we do something like this



where ARRAY will have all the entries??


Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Only if your value data type is a SQL array data type.
 
Don't get me started about those stupid light bulbs.
 
subject: Java and SQL