Thursday, July 23, 2015

About using APEX_COLLECTIONS in a tabular form with INSTEAD OF triggers

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
 
In case you dreamed of updating an Apex tabular form built on top of a view based on APEX_COLLECTIONS by means of INSTEAD OF triggers, you must be ready to grant the following rights to the schema user attached to the workspace, otherwise you'll get an ORA-01031: Insufficient privileges run-time error.
Each privilege is only required for the corresponding action, if you don't need to delete from the view you can safely omit GRANT DELETE for instance.
 
GRANT UPDATE,INSERT,DELETE ON APEX_xxxxxx.APEX_COLLECTIONS TO user_schema;

This DDL can only be executed by the DBA or by the APEX_xxxxxx user.
Probably the DBA won't like to do it for the usual security issues.

Note also that this kind of permissions are not carried over during subsequent Apex upgrades, which means that tabular forms will stop working if you forget to re-execute the grants after a major upgrade.


See message translations for ORA-01031 and search additional resources.

No comments:

Post a Comment

I appreciate your comment however bear in mind that I might not have the time to reply soon.
Normally I do not reply to all comments but I am glad if you found something useful or if you learned something new, in that case I strongly encourage you to promote the article with the +1 google button.
Flavio