Oracle Apex: Interactive Grid - ORA-01400

Question

I have a table / IG where the primary key is populated via sequence in "before insert" trigger. When I want to create a new row, I get ORA-01400 that null value can't be populated to the field.

To be honest I checked with that sample application and all the settings / attributes seem to be set in the same way; ID field is marked as primary column and is hidden, process seems fine too. When I insert data manually to the table and then update/delete process is processing rows correctly.

Type: Interactive Grid - Automatic Row Processing (DML (Settings) -> Target Type = Region Source Prevent Lost Updates, Lock Row and Return Primary Key after Insert are set to Yes Editable Region is my Interactive Grid Region Apex version is 5.1

I tried to manually process the Interactive Grid data using PL/SQL. Unfortuantely the update with the rowid doesn't seem to work.

Given Help by APEX.

Any suggestions what I might be doing wrong?

Any help is appreciated. Thank you.

Answer 1

Check whether there's a validation which says that the primary key column must not be NULL. If so, disable (by setting a condition to "Never") or remove it and then try again.

Apparently, validations fire before database triggers and raise an error.

Answer 2

You mentioned that primary key for your table is ID, if you use the example process from apex help, there is update and delete bounded to :ROWID - Here can be the problem with no data found.

I solved once saving and other row actions on grid, by debugging it with simple error message, that showed me actual values that i pass into the process.

Use this code into very beginning of your code, and you will see what you pass into process. It may help you to find where is the problem.

apex_error.add_error ( p_message => 'ENAME: ' ||:ENAME ||
                                  ' ,DEPTNO: '||:DEPTNO||
                                    'etc...'
                     , p_display_location => apex_error.c_inline_in_notification );

As well do not forget to check columns if you did not set them to QUERY ONLY = YES - this columns cannot be used in process. I hope it will be useful, good luck.

Answer 3

Might I make a recommendation In my experience you have two options that ought to work that I use. Make a process that goes before your save, so set its sequence to a lower number. And that process checks if :ID is null and if it is, it populates it. or you could just populate the ID during the create process. In the CASE C insert into ID value ID_SEQ.NEXTVAL if you are populating the ID using a sequence, or you could just define its value before you start the CASE stuff.

I hope this helps