APEX 5.1 How to manually process Interactive Grid data using PL/SQL

if you want to execute your own insert/update/delete or PL/SQL api calls for all the modified rows of an Interactive Grid you can do this with the “Save Interactive Grid Data / Interactive Grid Automatic Row Processing (DML)” process which gets automatically created when you make an Interactive Grid editable.

Here is an example if your Interactive Grid is based on EMP.

Settings for your Region:

Source > SQL Query =
  1. select empno, ename, deptno  
  2.   from emp  

Attributes > Edit > Enabled = Yes

Settings for your Save Interactive Grid Data process:

Settings > Set Target Type = PL/SQL Code
Settings > PL/SQL Code to Insert/Update/Delete =
  1. begin  
  2.      case :APEX$ROW_STATUS  
  3.      when ‘I’ then — Note: In EA2 this has been changed from I to C for consistency with Tabular Forms  
  4.          insert into emp ( empno, ename, deptno )  
  5.          values ( :EMPNO, :ENAME, :DEPTNO )  
  6.          returning rowid into :ROWID;  
  7.      when ‘U’ then  
  8.          update emp  
  9.             set ename  = :ENAME,  
  10.                 deptno = :DEPTNO  
  11.           where rowid  = :ROWID;  
  12.      when ‘D’ then  
  13.          delete emp  
  14.          where rowid = :ROWID;  
  15.      end case;  
  16. end;  
The PL/SQL code is very similar to what you can use for Tabular Forms if you have defined a PL/SQL process which fires for a Tabular Form region. It will be executed for each modified row, the bind variable APEX$ROW_STATUS can be used to find out if the row has been inserted, updated or deleted and bind variables can be used to reference the value of all columns of the Interactive Grid. Please be aware, that the bind variables will still return a string, so be careful in case of numbers and dates.
Note: If your INSERT statement or your PL/SQL API call generates a primary key as it’s the case with the ROWID, you have to assign the result to the primary key column again. In the above case I’m using a RETURNING clause to do so. This is important, because that primary key value is used to query the newly inserted data. Only in the case if the end user enters a primary key value then this is not necessary.
This is Post by Patrick on oracle community: https://community.oracle.com/message/13870402#13870402

Leave a Reply

Your email address will not be published. Required fields are marked *