This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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.
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.
Joined: Sep 01, 2005
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.
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.
That was really helpful.
It would have been great if some other people could give their comments on it.
Thanks in Advance
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.
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com