CODEWITHSUNDEEP
.netsqloracle-databasepowershell
JimDaniel
JimDaniel

Reputation: 12713

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

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!

Upvotes: 0

Views: 1333

Answers (1)

Boris Modylevsky
Boris Modylevsky

Reputation: 3099

  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

Upvotes: 1

Related Questions