Wednesday 30 September 2009

How to retrieve new row data from INSERT using Oracle DataAccess

Answering a question on StackOverflow:

"I am using Oracle database server with Oracle DataAccess client. What I need to do is INSERT a new row of data, then retrieve the auto-generated ID field of the newly-created row for another INSERT command, immediately following. What is the best way to do this?"

Answer:
1. Modify the INSERT query by adding RETURNING keyword
"INSERT INTO table_name (column_name1, column_name2) VALUES ('val1', 'val2')
RETURNING module_id INTO :column_id"


* This source code was highlighted with Source Code Highlighter.

2. Add a bind variable to your OracleCommand named "column_id"
3. Take its value after the command is executed