How to auto update the null column imported in the table using apex data loader

Question

Do you have any idea on how to auto update the table with null column (person_id) after importing the data using the apex data loader? In my csv file template, I do not include the person id, I only include the employee number. And I don't like to include the person id in the csv file template, I want it to be auto updated in case the imported data has null person id.

Answer 1

Two options from me.

Option one - if your database supports it - is to use an identity column and let Oracle fill ID value:

SQL> CREATE TABLE so_test
  2  (
  3     id      NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  4     empno   NUMBER,
  5     ename   VARCHAR2 (50)
  6  );

Table created.

SQL> INSERT INTO so_test (empno, ename) VALUES (1234, 'Littlefoot');

1 row created.

SQL> INSERT INTO so_test (id, empno, ename) VALUES (100, 4443, 'Bigfoot');

1 row created.

SQL> SELECT * FROM so_test;

        ID      EMPNO ENAME
---------- ---------- --------------------------------------------------
         1       1234 Littlefoot      --> ID created by Oracle
       100       4443 Bigfoot         --> ID inserted manually

SQL> DROP TABLE so_test;

Table dropped.

---

Option two is to use database trigger (with a sequence):

SQL> CREATE TABLE so_test
  2  (
  3     id      NUMBER,
  4     empno   NUMBER,
  5     ename   VARCHAR2 (50)
  6  );

Table created.

SQL> CREATE SEQUENCE seq_so_test;

Sequence created.

SQL> CREATE OR REPLACE TRIGGER trg_bi_so_test
  2     BEFORE INSERT
  3     ON so_test
  4     FOR EACH ROW
  5     WHEN (new.id IS NULL)
  6  BEGIN
  7     :new.id := seq_so_test.NEXTVAL;
  8  END;
  9  /

Trigger created.

SQL> INSERT INTO so_test (empno, ename) VALUES (1234, 'Littlefoot');

1 row created.

SQL> INSERT INTO so_test (id, empno, ename) VALUES (100, 4443, 'Bigfoot');

1 row created.

SQL> SELECT * FROM so_test;

        ID      EMPNO ENAME
---------- ---------- --------------------------------------------------
         1       1234 Littlefoot       --> ID created by a trigger
       100       4443 Bigfoot          --> ID inserted manually

SQL> DROP TABLE so_test;

Table dropped.

SQL>

Answer 2

Do you already have a trigger for your table? If not, create a trigger and adapt the query of the trigger accordingly such as:

IF :NEW.person_id IS NULL THEN
  :NEW.person_id := <sequence_name>.NEXTVAL;
END IF;