APEX

Hi,

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 =

select empno, ename, deptno
  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 =

begin
     case :APEX$ROW_STATUS
     when 'I' then -- Note: In EA2 this has been changed from I to C for consistency with Tabular Forms
         insert into emp ( empno, ename, deptno )
         values ( :EMPNO, :ENAME, :DEPTNO )
         returning rowid into :ROWID;
     when 'U' then
         update emp
            set ename  = :ENAME,
                deptno = :DEPTNO
          where rowid  = :ROWID;
     when 'D' then
         delete emp
         where rowid = :ROWID;
     end case;
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.

I will update the "PL/SQL Code to Insert/Update/Delete" attribute to contain an example.

Hope that gives an idea

Patrick