DML Operations using Query based View Object

Recently there is a request in Oracle Forums/Spaces on how to save data into db tables using View Object(which is not based on Entity Object(EO)).

We already know that only EO based VOs can handle DML operations. If the query is complex, it may not be feasible to create EOs(and associations if any) and then base your VO on the EOs. In this case, it's better to create VO directly based on your complex query and then implement the DML operations by calling PL/SQL APIs from the VO. This way you can even do DML operations on more than one table. For this to achive Oracle has already provided a class oracle.apps.fnd.framework.server.OAPlsqlViewObjectImpl. By default, when we create any view object, it always extends oracle.apps.fnd.framework.server.OAViewObjectImpl which doesn't support any DML operations. In our case we need to create our View Object by extending OAPlsqlViewObjectImpl

Note: The class oracle.apps.fnd.framework.server.OAPlsqlViewObjectImpl has been deprecated. So implement this method only if you don't have alternative.

Below are the steps to achieve the desired result

1. Create VO(MyVO) based on query as usual and generate the MyVOImpl and MyVORowImpl.
2. Now go to MyVOImpl.java and change the extends to OAPlsqlViewObjectImpl. So your VOImpl should look like below

public class MyVOVOImpl extends OAPlsqlViewObjectImpl

3. Now go to MyVORowImpl.java and change the extends to OAPlsqlViewRowImpl

public class MyVORowImpl extends OAPlsqlViewRowImpl

4. MyVORowImpl has three methods to carry DML operations insertRow, updateRow, and deleteRow(inherited from OAPlsqlViewRowImpl).
5. Override insertRow(byte)/insertRow and call respective PL/SQL procedure to insert row into the table. As this is RowImpl class you will get all values using their getter methods. Check javadoc for information about these two methods.
6. Override updateRow(byte)/updateRow and call respective PL/SQL procedure to update row in table. As this is RowImpl class you will get all values using their getter methods.Check javadoc for information about these two methods.
7. Override deleteRow(byte)/deleteRow and call respective PL/SQL procedure to delete row in table. As this is RowImpl class you will get all values using their getter methods. Check javadoc for information about these two methods.

Please let me know if I miss any points. I am happy to add here if it's relevant.