oracleplsqloracle11gora-06512ora-01843

SQL Error: ORA-01843: not a valid month


I am receiving ORA-01843 when I run the Insert command below. Is there something wrong with my code? And got the below error messages:

SQL Error:

ORA-01843: not a valid month
ORA-06512: at "MDSYS.SUPERHEROES_AUDIT", line 8
ORA-04088: error during execution of trigger 'MDSYS.SUPERHEROES_AUDIT'
01843. 00000 -  "not a valid month"

What seems to be wrong with my declaration? Thank you.

 /* TABLE CREATED */

    CREATE TABLE EVENTLOGS(
        Eventlog_id   Number(3,0)   NOT NULL,
        User_name     Varchar2(20),
        Date_done     Date,
        Action_done   Varchar2(50),
    CONSTRAINT PK_EVENTLOGS PRIMARY KEY (Eventlog_id));

    /* SEQUENCE CREATED */

    CREATE SEQUENCE EVENTLOG_ID_SEQ
      MINVALUE 1
      START WITH 1
      INCREMENT BY 1
      NOCYCLE
      CACHE 10;

    /* TRIGGER CREATED */

    CREATE OR REPLACE TRIGGER SUPERHEROES_AUDIT
    AFTER INSERT OR DELETE OR UPDATE ON SUPERHEROES
    FOR EACH ROW
    ENABLE
    DECLARE
      V_LOGID NUMBER;
      V_USER  VARCHAR(30);
      V_DATE  VARCHAR(30);
    BEGIN
      SELECT EVENTLOG_ID_SEQ.NEXTVAL, USER, TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') INTO V_LOGID, V_USER, V_DATE FROM DUAL;
      IF INSERTING THEN
        INSERT INTO EVENTLOGS(Eventlog_id, User_name, Date_done, Action_done)
        VALUES (V_LOGID, V_USER, V_DATE, 'INSERT');
    END IF;
    END;
    /


    INSERT INTO SUPERHEROES VALUES ('TEST');

Solution

  • Your column in the table is defined as

    Date_done     Date,
    

    You are storing the date into a VARCHAR2 variable

    V_DATE  VARCHAR(30);
    ....
    SELECT EVENTLOG_ID_SEQ.NEXTVAL, USER, 
    TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') INTO V_LOGID, V_USER, V_DATE
    ...
    

    And you are inserting this variable into the DATE column.

    ...
    VALUES (V_LOGID, V_USER, V_DATE, 'INSERT');
    

    Keep datatype consistent to avoid confusion.