OBIEE write back, solution for insert and update in database

Here is my solution for write back (insert and update). I'm just trying to do some table data maintenance, and that is, insert and update one after the other. This solution includes two reports (one for insert and one for update, and two XML files). It's very fast to insert data, or update.

1. At first, we create table in the database:

create table wb_test(col1 number, col2 varchar2(50), col3 varchar2(255))

alter table wb_test add constraint wb_test_pk primary key (col1)

create sequence wb_test_seq start with 1 increment by 1

2. In Administrator, we need to disable cacheable option:





3. In Answers the user must have write back privilegies



4. In this write back insert/update solution user inserts new data through the one report and through the other report is doing update. Both reports are for the same database table. Write back option is taking XML file that we put in ..\OracleBI\web\msgdb\customMessages folder. Inside this XML there are SQL's for insert and/or update.

5. Insert report



Here is Id=0 in the filter. What does it mean? This insert report is taking only rows that have Id=0. So, for 'insert new row' we'll update this Id from 0 to some sequence value. You'll see later that we put update statement in insert XML. This dummy row is a necessary to be in the table, before (when the table is empty) and after we update (this is resolved by a after update trigger) because the table has the primary key (id), otherwise we have problems.



OBIEE has some problems with null values in the database. In this case we have 0 – null – null and we need to put in the col2 and col3 columns expression ifnull(WB_TEST.COL2,'n/a') and ifnull(WB_TEST.COL3,'n/a') to get null value from database and represents it as n/a.

Without this workaround if we try to insert something (test) we would have:



and we get an error:



We can ignore/fix this error, with ifnull(WB_TEST.COL2,'n/a') in the col2 and ifnull(WB_TEST.COL3,'n/a') in the col3. Try it.

Then we get:





N/a is null, so if we delete n/a from both columns we are saving null in the database with new inserted Id, and this works fine, like in case if we put some value:



After successful insert the next thing that we see is again dummy Id=0 row. This means that OBIEE saved our insert data in the database (mmarkic) i automatically give us dummy Id=o row. If we don't want to insert we close the insert report. After the user push insert button, XML code is generated.

Insert XML:



After insert the state in the database is:



We see new inserted row with Id 34, and a new dummy row appeared.

6. Update report





In this update report we show only inserted rows, with Id that is greater then zero (Id>0). We can update them. In case we don't have inserted rows this report would return no results.

In our case we have one row to update..



We have changed value from mmarkic u mmarkic2:



After insert we see in the report new data and the state in the database is:



Update XML:



XML files location:



We set writeback template here:



7. Database trigger:

create or replace trigger wb_test_af_upd_tr
after update
ON wb_test
declare
cursor c1
is select 1 as num
from wb_test
where col1=0;
v1 number;
begin
open c1;
fetch c1 into v1;
if v1=1 then
null;
else
insert into wb_test values (0,null,null);
end if;
end loop;
close c1;
exception
when others then
null;
end;

*Note
We use dummy row insert method.
After we insert row (we update dummy row) this new row we don't see in the update report because of Id=0 filter.
After update of dummy row we activate trigger in wb_test table which inserts then again this dummy Id=0 row if the one does not exist.
The trigger will be activated during regular update.
Update is only for rows that have Id greater then zero