I frequently use apex_collections in forms and reports to enjoy there performance and to support special cases. Apex_Collections can be created and accessed using SQL Select statements. This is very nice, but until now I had to use the API calls to perform inserts, updates and deletes. In case where I use an Interactive Grid, i had to provide PL/SQL code to support SAVE operations in the Grid.
My first try to wrap apex_collections with a view, that supports an "instead of update" trigger failed with a runtime error message from oracle. "ORA-20987: APEX - ORA-01031: insufficient privileges"
Recently i listend to Steven Feuerstein lesson about pipelined table functions:
Getting Started with PL/SQL Table Functions: Pipelined Table Functions
Then I saw an opportunity to encapsulate the fact that I want to access these Apex_Collections for update. I wrote the package Pipe_Apex_Collections with the function pipe_rows. Finally, using the function as a source in the view definition, i created a view that supports DML operations.
Here is the source code for the view definition : v_apex_collections.sql
as part of this plugin and demo app : strack-software-upload-to-collection-plugin
and the live demonstration of this app: https://apex.oracle.com/pls/apex/f?p=103003:LOGIN_DESKTOP
Regards,
Dirk
