APEX Interactive Grip update PL/SQL Code is too long

Question

I created a APEX application for a table to give user access to update records in the table. I used Interactive grid and added edit option in that. I have a Identity column SEQ_ID which is default to get next value in Database. So when i edit any column in APEX i am getting below error.

ORA-32796: cannot update a generated always identity column.

So instead of Region source in Interactive Grid's Processing option I changed that to PL/SQL Code and added an update statement so that i can exclude SEQ_ID column update from APEX. This resolved the above ORA error.

But one of my table has 220 column that should be made editable in APEX(i know this sounds weird but Yes) so when i wrote a Update Statement it is giving me an error as below.

Value too long by 4000 Characters.

Can any one help me how to resolve this issue.

Update statement is a simple as below

UPDATE TABLE

SET Col1 = :Col1

,Col2 = :Col2 .... ... ...

WHERE SEQ_ID = :SEQ_ID

Answer 1

Solution 1:

Revert back to using default IG Update/Inserts and right click on the IDENTITY column and click Delete:

Solution 2:

Create a Procedure and move your update/insert code into the procedure. You should be able to access the APEX_COLLECTIONS inside the procedure as they are a part of your session.

Answer 2

The solution for the ORA-32796 is to change the settings for the column SEQ_ID. Set "Query Only" and "Primary Key" to ON in the "Source" section for that column. That will ensure that "Interactive Grid - Automatic Row Processing" process does not include that column in the DML statement and the error will not happen. Then you will be able to use the Interactive Grid process instead of writing your own code.

The interactive grid (any apex form region for that matter) is great of updating the data entered by the user. Columns like sequences, audit columns (updated_by, created_by, ...) are better handled by the database. Best practice is to do that via a trigger like this one on the sample table DEPARTMENTS:

create or replace trigger departments_biu
    before insert or update 
    on departments
    for each row
begin
    if inserting then
        :new.created := sysdate;
        :new.created_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
    end if;
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end departments_biu;
/