Oracle BI EE 10.1.3.4 – Delete using Writebacks – An alternative approach using EVALUATE
Posted by Venkatakrishnan J on January 15, 2009
I saw this question floating around in one of our internal forums wherein a user wanted to know whether it is possible to Delete records using Writebacks. Unfortunately, Writebacks in BI EE support only inserts and updates(though deletes can be done as well, but is not supported). In this blog entry i would walk through an approach where in one can delete records using the EVALUATE function in BI EE. Be warned that this approach uses a potentially lethal command PRAGMA AUTONOMOUS_TRANSACTION which can lead to deadlocks if multiple users are using this at the same time. But if this is something that is needed by an admin user and is a non-negotiable requirement, this approach can be used. To demonstrate this, we shall use the simple EMP table from scott schema. Our parent report is shown below. Our intention is to have an ability that can delete any employee from the report & from the database.
So, each record should basically have an URL, clicking on which should delete that record from the database. To do this, lets first create a database function as shown below
create or replace FUNCTION WRITEBACK_DELETE_EMP(P_ENAME IN VARCHAR2,TO_DELETE IN VARCHAR2) RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION; V_ENAME VARCHAR2(100); BEGIN IF TO_DELETE = 'Yes' THEN DELETE EMP WHERE ENAME = P_ENAME; COMMIT; RETURN 'Success'; ELSE RETURN 'No Delete'; END IF; EXCEPTION WHEN OTHERS THEN RETURN 'Failure'; END;
As you see, the function basically accepts 2 parameters. One parameter is the Employee Name and the other is more of a boolean indicator to ensure that we are not deleting the records while creating the report itself. To enable deletion of the records, lets create another report which basically would display the Employee name and a Return flag indicator from the above function. While creating this report, ensure that you are passing only No to the second parameter. Else it will delete all the records.
This report should also have a filter on the Employee name to is prompted.
Now, create a narrative view on the report as shown below
Ensure that your compound view only has this narrative view. Also, before saving the report, change the evaluate function to the one shown below
Once this is done, go back to the parent report, and in the delete column just add a navigate link to the child report that we created above.
Now, if you click on the delete link, this would automatically delete that particular record from the database.
We get the Success message (typically after a delete we should not even get the record in the child report) with that particular record because of the AUTONOMOUS_TRANSACTION. Thats because, by default we are using a SERIALIZABLE option of AUTONOMOUS_TRANSACTION. So, even if the record got deleted, the parent transaction does not know about it and hence we would see it. The other drawback is that one would have to manually refresh the report. This can be overcome by using GO URL instead of Navigate and adding a custom refresh function after the click on the delete url.