Business Intelligence – Oracle

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.

      

      

EVALUATE('WRITEBACK_DELETE_EMP(%1,''No'')',EMP.ENAME)

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

EVALUATE('WRITEBACK_DELETE_EMP(%1,''Yes'')',EMP.ENAME)

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.

3 Responses to “Oracle BI EE 10.1.3.4 – Delete using Writebacks – An alternative approach using EVALUATE”

  1. Ilmari said

    Hi Venkat!
    Great to see you back and writing about core OBI again!

    You can “override” the inserts and updates in the write-back xml-file with deletes, for example:

    DELETE FROM DIM_BONUS WHERE BONUS_ID=@{c0}
    DELETE FROM DIM_BONUS WHERE BONUS_ID=@{c0}


    This is probably what you meant with deletes possible but not supported, right? Hard to believe that it would be recommended, though😉

    Cheers,
    Ilmari
    ClearPeaks

  2. sunita said

    Hi Venkat,

    I am very new to OBIEE. I’ve been reading your blogs. They are really very informative. I am unable to search your previous archive posts. I want to search by year/month of your previous obiee posts. I don’t see a link.

    Thanks,
    Sunita

  3. Steve cheong said

    To whom may concern,

    I have a problem with OBIEE, the file i save at obiee not able to remove or delete it. Do you all have a way to remove the file.

    thanks a lots,

    cheers

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: