How to obtain back Primary KEY in Pega for new Object saved in Oracle External Database generated by sequence/trigger on database side.
I saved an object to an external Oracle database with the Activity method Obj-Save, and was wondering if there was a way to get back the primary KEY of that object that was generated from the external DB side via a trigger sequence. I checked and the key is being generated successfully in the external database, but need to obtain it instantly to create and save other objects that are dependent on that primary KEY.
Accepted Solution
Pegasystems Inc.
US
Revature
US
Thank you Jim, this was helpful. At first we were trying to avoid going through the stored procedure approach, but we realized this is probably the best way to obtain the primary key as a returned value. For now we will be doing the first solution of doing an Obj-Browse and trying to pull back the most recent submission for that specific user. We will need to modify the schema just a bit.
Thanks again for the reply,
David
Hi David,
I would think of this in the same way you would were you to do an insert using the DB tool of your choice outside of Pega. (each of the SQL statements are atomic)
1) insert
2) key is generated in row
3) how do you get it back using SQL?
The problem is that the row is uniquely identified by the key, the whole key, and nothing but the key (lol). So you would need an approximation built on the data you have (without knowing the key) , and then get the max createdatetime or some other value outside of the key that would allow you to sort the result set. This is prone to errors though since another transaction may have inserted between the time you committed the insert and the subsequent select - (substitute Obj-Save for the insert and Obj-Browse for the select)
Alternately you could use a stored procedure to accept the data as input and return the value of the unique key (this assumes the database has the ability to return to you the just generated unique key within the scope of your transaction)
Sorry for the long winded response - take these with a grain of salt and consider them when deciding what to do. There may be other solutions that are better.