Part II : Create a page with multiple forms linked to multiple tables

September 12, 2013

Previously I has written about creating a page with multiple forms linked to multiple database. You can read it here. But, what I am going to post now is about trigger upon insert action, and how to pass the value into another page item.

Simple example is like this:

You are created 2 separate form in one page in APEX environment. One Submit button operation, all the data will be insert accordingly into their respective table, let say Table1 and Table2. Table1 has an auto generate ID by trigger on insert operation. And this Table1.ID was require to be pass to Table2. This scenario can be said as one to many relational table inside database.

I have tried to use the DML operation to pass the primary key into another page item unfortunately has failed. The workaround of it was to use 'RETURNING' clause in PL/SQL.

Check-out SQL below:

insert into Table1 ( column1, column2, column3) values
( :P1_Data1, :P1_Data2, :P1_Data3)
returning YourPrimaryKeyColumnName into :P1_YourTargetPageItem;

I took a lot of time searching how to do it. If you need more details on this, just let me know here.