Getting a not valid month and need to insert records in incremental manner

CREATE TABLE deligate_details_main (
    e_id NUMBER(10),completed_date timestamp,
    CONSTRAINT pk_deligate_details_main PRIMARY KEY ( e_id )
);

INSERT INTO deligate_details_main VALUES(1,current_timestamp);
INSERT INTO deligate_details_main VALUES(2,current_timestamp);
INSERT INTO deligate_details_main VALUES(3,current_timestamp);

CREATE SEQUENCE deligate_details_trans_sq;

CREATE TABLE deligate_details_trans (
    d_id  NUMBER(10),
    e_id  NUMBER(10),
    completed_date_trans date,
    CONSTRAINT pk_deligate_details_trans PRIMARY KEY ( d_id ),
    CONSTRAINT fk_e_id FOREIGN KEY ( e_id )
        REFERENCES deligate_details_main ( e_id )
);

insert into deligate_details_trans(d_id,e_id,completed_date_trans)
select deligate_details_trans_sq.nextval,
e_id,
to_date(completed_date,'DD-MON-YY') from deligate_details_main;

The problem which I am facing:

1. I need to convert timestamp to date and then insert it into the deligate_details_trans for the completed_date column of the deligate_details_main table. When I am converting it is giving ORA-01843: not a valid month error.

2. Currently, deligate_details_main has 3 rows that are going to be inserted into the deligate_details_trans table but if any rows got inserted into the deligate_details_main table say e_id 4 got inserted after doing insertion into the deligate_details_trans table. Then when I will run the INSERT query then it should append e_id 4 into the deligate_details_trans table

Expected Output if there are 3 records into the deligate_details_main table:

+------+------+----------------------+
| D_ID | E_ID | completed_date_trans |
+------+------+----------------------+
|    1 |    1 | 13-Dec-21            |
|    2 |    2 | 13-Dec-21            |
|    3 |    3 | 13-Dec-21            |
+------+------+----------------------+

When one more e_id gets added into the deligate_details_main table

INSERT INTO deligate_details_main VALUES(4,current_timestamp);

Then the output should be:

+------+------+----------------------+
| D_ID | E_ID | completed_date_trans |
+------+------+----------------------+
|    1 |    1 | 13-Dec-21            |
|    2 |    2 | 13-Dec-21            |
|    3 |    3 | 13-Dec-21            |
|    4 |    4 | 13-Dec-21            |
+------+------+----------------------+

Answer

All you need is TRUNC on completed_date, I’d say.

SQL> INSERT INTO deligate_details_trans (d_id, e_id, completed_date_trans)
  2     SELECT deligate_details_trans_sq.NEXTVAL, e_id, TRUNC (completed_date)
  3       FROM deligate_details_main;

3 rows created.

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> SELECT * FROM deligate_details_trans;

      D_ID       E_ID COMPLETED_
---------- ---------- ----------
         4          1 13.12.2021
         5          2 13.12.2021
         6          3 13.12.2021

SQL>

---

To make inserts into deligate_details_trans automatic, you’ll need a database trigger:

SQL> CREATE OR REPLACE TRIGGER trg_ai_ddm
  2     AFTER INSERT
  3     ON deligate_details_main
  4     FOR EACH ROW
  5  BEGIN
  6     INSERT INTO deligate_details_trans (d_id, e_id, completed_date_trans)
  7             VALUES (deligate_details_trans_sq.NEXTVAL,
  8                     :new.e_id,
  9                     TRUNC (:new.completed_date));
 10  END trg_ai_ddm;
 11  /

Trigger created.

Let’s test it:

SQL> INSERT INTO deligate_details_main VALUES(4,current_timestamp);

1 row created.

SQL> SELECT * FROM deligate_details_trans;

      D_ID       E_ID COMPLETED_
---------- ---------- ----------
         4          1 13.12.2021
         5          2 13.12.2021
         6          3 13.12.2021
         7          4 13.12.2021         --> here it is!

SQL>