We have a requirement to update profile for bulk users (worst case 1k operator id) in single shot, so we have designed Stored Procedure to update the list of operator by passing as parameter and insert using insert query. While doing insert, the Blob column (pzpvstream) is updating as Null. So when the instance is handle using Obj-open-By-handle/ Obj-Open, it throws error as Java.ArrayOutOfbound exception because the blob column (pzpvstream) is Null and both of the methods fetches records from pzpvstream (blob column). So, can someone tell any possible ways to update the Blob column in insert query (Outside Pega - In Stored Procedure) or from Pega (without Obj-Open/ Obj-Open-By-Handle method)? Advance thanks for your valuable response.
trying to understand your requirement. If you are updating some profiles (That means where the records are already exists) why you are using an Insert ,not an update? Also why you mentioned without using Obj methods.
We have a data table (Operator_Group) which contains the list of workgroups associated to every operators. We got a requirement to add selected workgroup for bulk operators. For eg., if the user selected a workgroup in the dropdown and 1000 operators, need to insert 1000 rows with the selected WG & Operators. And if we use Obj methods it'll trigger 1000 DB calls, it may get performance impact. To avoid this, we chose to implement via Stored procedure.
My personal thought is ...1000 records shouldn't be a big impact. Specially if you can take the logic or performing the db operation out from the dropdown onchange. You can capture workgroup on dropdown change and perform the DB operation on a submit or somewhere else if possible. Pega best practice is to tolerate using Obj methods when you are transacting with Pega internal DB.
If you are to use the stored procedure approach I didn't come across a specific way of inserting the BLOB stream since this is always handled by Pega.
Found a way to update blob - Copy the record in a temporary page, using RDB method delete the existing record and insert it again using Obj method from Temp page where the instance is handling. It fixed our problem