IBM Support

Oracle Error ORA-01400 and ORA-00001

Technical Blog Post


Abstract

Oracle Error ORA-01400 and ORA-00001

Body

Once every couple of months I will hear about our client getting the following Oracle error messages. 

If you get one of these errors after restoring from backup or migrating to another server, please rebuild the sequence or Recreate Triggers:


1.  ORA-01400: cannot insert NULL into ("MAXIMO"."WORKPERIOD"."ROWSTAMP")

RECREATE and ENABLE the TRIGGER

 

   For Example:

   create or replace TRIGGER WORKPERIOD_T
   BEFORE INSERT OR UPDATE ON WORKPERIOD
   FOR EACH ROW DECLARE NEXTVAL INTEGER;
   BEGIN SELECT MAXSEQ.NEXTVAL INTO NEXTVAL FROM DUAL;
   :NEW.ROWSTAMP := NEXTVAL; END;   
                                                                        
   COMMIT;    
   


2. ORA-00001: unique constraint (MAXIMO.LOGINTRACKING_NDX1) violated

REBUILD THE ORACLE SEQUENCE

 

   a) Run query:  select MAX(LOGINTRACKINGID) from LOGINTRACKING

   OUTPUT: 110

 

   b) Drop the sequence in question:

   DROP SEQUENCE LOGINTRACKINGSEQ;

 

   c) Recreate the sequence as the recorded value+1.

   create sequence LOGINTRACKINGSEQ start with 111;

   commit;

[{"Business Unit":{"code":"BU005","label":"IoT"}, "Product":{"code":"SSLKT6","label":"Maximo Asset Management"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11131447