Home » Developer & Programmer » JDeveloper, Java & XML » any way where we can insert all the 20,000 ename in table from java (oracle 10g)
any way where we can insert all the 20,000 ename in table from java [message #500714] Wed, 23 March 2011 01:44 Go to next message
forroughuse
Messages: 26
Registered: November 2010
Location: us
Junior Member
Hi Friends,
I need help reagrding inserting data in the table.

We are getting more than 20,000 rows (example: ename) from java and they want to insert all the 20,000 enames together in a table
having single column ename.
For ex:

create table emp(ename varchar2(10));

Currently they are doing
insert into emp values('raj');
insert into emp values('raja');
.
.
.
so on for 20,000.

They are saying that it hits the performance as we are doing insert for 20,000.

Do we have any way where we can insert all the 20,000 ename in one go.

Thanks in advance.
Re: any way where we can insert all the 20,000 ename in table from java [message #500716 is a reply to message #500714] Wed, 23 March 2011 01:47 Go to previous messageGo to next message
Littlefoot
Messages: 21648
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SQL*Loader, perhaps?
Re: any way where we can insert all the 20,000 ename in table from java [message #500718 is a reply to message #500716] Wed, 23 March 2011 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
... or external table.

Regards
Michel
Re: any way where we can insert all the 20,000 ename in table from java [message #500726 is a reply to message #500718] Wed, 23 March 2011 02:56 Go to previous messageGo to next message
John Watson
Messages: 8458
Registered: January 2010
Location: Global Village
Senior Member
A bind variable might help, you could set cursor_sharing=force and then you won't have to change the software at all.
Re: any way where we can insert all the 20,000 ename in table from java [message #500734 is a reply to message #500726] Wed, 23 March 2011 05:30 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
You sure force will help in the case of an insert John?
Can't test at the moment but I'd be kind of surprised.
Re: any way where we can insert all the 20,000 ename in table from java [message #500735 is a reply to message #500734] Wed, 23 March 2011 05:37 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
Thinking about it force would help avoid ageing other statements out of the sga prematurely.
Re: any way where we can insert all the 20,000 ename in table from java [message #500736 is a reply to message #500735] Wed, 23 March 2011 05:39 Go to previous messageGo to next message
John Watson
Messages: 8458
Registered: January 2010
Location: Global Village
Senior Member
I was thinking of one parse, instread of 20000. Definitely works!
Re: any way where we can insert all the 20,000 ename in table from java [message #500752 is a reply to message #500736] Wed, 23 March 2011 07:34 Go to previous messageGo to next message
Paules
Messages: 128
Registered: March 2011
Location: United Arab Emirates
Senior Member
Hai,

Place all the 20,000 names in an Excel Sheet. Import in a Table using Toad . Use a single Insert statement.

Regards,
Paules
Re: any way where we can insert all the 20,000 ename in table from java [message #500766 is a reply to message #500752] Wed, 23 March 2011 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not directly use an external table and simple SQL*Plus?
No need of external products.

Regards
Michel
Re: any way where we can insert all the 20,000 ename in table from java [message #500792 is a reply to message #500766] Wed, 23 March 2011 10:26 Go to previous message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
As I think that your problem is about how to speed up a java program which inserts 20000 records in a table you should use batch (array or bulk) processing in your java program.
Previous Topic: How to convert a String to XML
Next Topic: Please help me fix this error! (4 threads merged by bb)
Goto Forum:
  


Current Time: Sun Jan 24 10:31:55 CST 2021