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!

Boris Modylevsky · Accepted Answer · 2009-09-16 15:39:45Z

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"

Add a bind variable to your OracleCommand named "module_id"
Take its value after the command is executed

answered Sep 16, 2009 at 15:39

Boris Modylevsky

3,1291 gold badge27 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Boris Modylevsky Over a year ago

You are welcome. Thanks you for your question I've added a post on my blog with this question and answer: borismod.blogspot.com/2009/09/…

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related