How to retrieve new row data from INSERT using Oracle DataAccess with Powershell?

Question

I am using Oracle.DataAccess.Client inside Powershell. 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? I am pretty new to SQL and Oracle. Here is some of my code:

$conn = "My Connection String"
$sql = "insert into SCM_APPS.MODULES (PACKAGE_ABBREVIATION, FULL_MODULE_NAME) values ('TES', 'Testing')"

$command = New-Object Oracle.DataAccess.Client.OracleCommand($sql,$conn)
$reader = $command.ExecuteReader()

Thanks for any help you can provide!

Answer 1

1. Modify your SQL insert query as following

$sql = "insert into SCM_APPS.MODULES (PACKAGE_ABBREVIATION, FULL_MODULE_NAME) values ('TES', 'Testing') RETURNING module_id INTO :module_id"

1. Add a bind variable to your OracleCommand named "module_id"

2. Take its value after the command is executed