Return PK value after Insert - Oracle

[Andrzejek]

I have a table in Oracle DB with a Primary Key that gets populated on Insert by a trigger on the table - the trigger gets the next sequence number from a sequence I created for the table and uses that for the value of the PK on insert. Now I would like to be able to return that PK value from my insert statement and use it as Foreign Key in another (child) table. All of that is done in VB code, not a Stored Procedure.

Is there some combination of Insert statement that returns PK used in that Insert? From the same transaction?

I know I can do: [tt]Select Max(PK_Field) as MyPKValue from SomeTable[/tt], but this way I may get somebody else's PK, not necessary mine (if somebody else issued another Insert statement). The same goes if I ask for current value from my sequence, not very safe bet either.


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

Can't you use an output parameter to do this?

 

[Andrzejek]

I have something like this in my code:

strSQL = "Insert Into SomeTable (FieldA, ...)" & _
    " Values  (123, ...) "

MyConnection.Execute strSQL

What do you mean when you say: "output parameter"?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[MajP]

Sorry. I thought I was in .NET not in the VB forum. But I think you can still define output parameters.

https://msdn.microsoft.com/en-us/library/ms675869(v=vs.85).aspx