• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Using Sequences With JDBC in Oracle

 
Sid Singh
Greenhorn
Posts: 20
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

My question is related to usage of sequences in insert queries.
Following are the two methods that i have used for sequences

1. To get the sequence in a seperate query and then pass it to the insert query using PreparedStatement
2. Directly pass the SequenceName.NEXTVAL in the insert query.

I have seen in the projects that i have worked, Method 1 is used mostly. As far as i visualize getting sequence in a seperate query will increase
a round of I/O which would be 1 in second method.

Please help in identifying the pro's and cons of both the methods.

Awaiting your reply.
 
Fatih Keles
Ranch Hand
Posts: 182
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you need to use the generated key from sequence then you should either select seq.nextval before executing insert or use insert into returning clause.

Regards,
Fatih.
 
Sid Singh
Greenhorn
Posts: 20
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fatih Keles wrote:If you need to use the generated key from sequence then you should either select seq.nextval before executing insert or use insert into returning clause.

Regards,
Fatih.


Hi Fatih,

No i dont need to use that key. Since it is a Primary Key for the table therefore i need to fetch the sequence while inserting the record.

One more point i would like to add in my above mentioned question.
I work in a tightly integrated system where several applications fire queries on a common database,
so what effect would each of the methods have on performance and execution time of the query.

Regards
 
Fatih Keles
Ranch Hand
Posts: 182
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Executing a query from dual to fetch a value from a sequence is almost costless for Oracle database, but for the senario you have execute 2 queries consequently and total time is effected from many things like network, AS utilization etc. I would prefer to execute one query where possible.
 
Sid Singh
Greenhorn
Posts: 20
Eclipse IDE Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Fatih Keles wrote:Executing a query from dual to fetch a value from a sequence is almost costless for Oracle database, but for the senario you have execute 2 queries consequently and total time is effected from many things like network, AS utilization etc. I would prefer to execute one query where possible.


Thanks Fatih,
That was really helpful.

It would have been great if some other people could give their comments on it.
Thanks in Advance
 
Sudheer Bhat
Ranch Hand
Posts: 75
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I prefer to execute SQL as less as possible (without compromising the functionality!) no matter whether its via JDBC or PLSQL as long as that single SQL becomes too complex and convoluted (in that case probably breaking the single SQL into parts is beneficial). Every call to a SQL in PLSQL routine results in a context switch from PLSQL engine to SQL engine in the DB. In case of JDBC, every SQL call would result in a round network trip. My personal preference would be to reduce number of SQL calls, and make SQL call only when its absolutely necessary.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic