APEX Interactive Grip update PL/SQL Code is too long

0
votes

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

2
oracle-apexoracle-apex-19.1
Just tested this, created a table with an identity field and string field, first I tested edit/save to replicate the error and indeed to had the same error. By default the IG was created with ROWID as primary id, most likely you will be happy to use ROWID to manage the table. So I simply right clicked on the IDENTITY column on the left and clicked DELETE. It no longer includes this but the identity works as expected. Problem solved? - Aaron
I recommend trying the above as it means you don't have to manage the update/insert queries at all if it works. If you want to continue managing it yourself - try creating a procedure and you should be able to access the APEX collections inside the procedure. - Aaron

2 Answers

0
votes

Solution 1:

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

Example Delete Column

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.

0
votes

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;
/