Databases Reference
In-Depth Information
•
Instead of Trigger method
•
Writing packaged procedures for updating of collections
•
Using On-Demand Process and Ajax for collection updates
The sections to follow describe each of these methods in detail.
Using Instead of Trigger
The ideal way to update the collection would be to create three
instead of
triggers on the view for
updating, deleting, and inserting of rows. Thinking further, imagine writing a package that would
automatically create those triggers for you. You would just need to provide your collection query and the
package would generate all of the code for use. You would just place the resulting code in your
application and it would run with the automatic DML process of Tabular Form. Such an approach is,
sadly, too good to be true. Currently, you can't get the instead of triggers to work with collections. If you
try using instead of triggers, you will receive error messages such as those shown in Figure 3-28.
Figure 3-28.
Tabular Form on APEX Collection—Instead of trigger error
You'll immediately notice that the error in Figure 3-28 has to do with privileges. An APEX Collection
is accessible only from the session context, and the trigger is not running in that session context. You
would need to grant access privileges on
WWV FLOW COLLECTION
to your schema, and this is definitely not
the way to go since you would need to change the APEX source code as provided by Oracle Corporation.
Such a change would also pose a security issue. For these reasons, the instead of trigger approach, while
nice to contemplate, is simply not feasible.
Writing Packaged Update Procedures
Before you start writing packaged procedures, you should delete the automatic DML processes that the
wizard created for you on page 3 of your application. You will need to create two procedures in your
package. These procedures are similar to those you wrote for the manual Tabular Forms. Add the code
shown in Listing 3-10 to the package.
Listing 3-10.
Update and Delete Procedures for Tabular Form Collections
PROCEDURE save emp coll custom (p message OUT VARCHAR2);
PROCEDURE delete emp coll custom (p message OUT VARCHAR2);
PROCEDURE save emp coll custom (p message OUT VARCHAR2)
IS
v ins count INTEGER := 0;
