DML operations with Evaluate

DML operations with Evaluate

Is it possible to do a DML operations, Insert/Update/Delete with Evaluate function?

Yes, We can do it. 


If we issue an Evaluate function in the report, the BI query will be something like :

 Select Evaluate('f1(%1)',Table_Name.Column_Name ) from "Subject Area" 

In Oracle, its not possible to do an Insert/Update/Delete from a select clause.

So in order to achieve this We need to have a Clause 'Pragma Autonomous_transaction;' in the underlying function called in the Evaluate.

Pragma Autonomous Transaction: 

Definition : 

An autonomous transaction is an independent transaction that is initiated by another transaction, and
executes without interfering with the parent transaction. When an autonomous transaction is called, the
originating transaction gets suspended. Control is returned when the autonomous
transaction does a COMMIT or ROLLBACK.

Example: 

Create or replace function test_proc( param IN VARCHAR2 )
return INTEGER as
PRAGMA AUTONOMOUS_TRANSACTION;
Begin
  update <table> set <column1> = param ;
    commit;
 end test_Proc;
-------------------------------------

As per the definition, we can make this Insert/Update/Delete as an independent transaction from the main report and can perform DML operation.