CODEWITHSUNDEEP
sqloracle-database
Caffeinated
Caffeinated

Reputation: 12484

How to debug this SQL error message, "can't insert NULL "?

OUT_RC = 37 ORA-01400: cannot insert NULL into ("PARTY"."SOURCE_SYSTEM_PARTY_CHARC"."PTY_CHARC_TY_VLU"): ORA-06512: at "PARTY.UP_MANAGE_SRC_SYS_PARTY_CHAR", line 131

I'm getting this error message, but at line 131 is a MERGE statement

MERGE INTO PARTY.SOURCE_SYSTEM_PARTY_CHARC SSPC
  USING (SELECT l_sspc_chgs(index).PTY_CHARC_TY_ID AS PTY_CHARC_TY_ID, 
                l_sspc_chgs(index).SRC_SYS_PTY_ID AS SRC_SYS_PTY_ID, ...

Her eis more code(surrounded the above MERGE line :

OPEN SRC_SYS_PTY_CHARC_STG_CUR;
     LOOP
        FETCH SRC_SYS_PTY_CHARC_STG_CUR
             BULK COLLECT INTO  l_sspc_chgs LIMIT blklimit;

        FOR indx IN 1 .. l_sspc_chgs.COUNT
        LOOP
             IF (l_sspc_chgs(indx).DLTD_IND = 'N')
                THEN 
                     MERGE INTO PARTY.SOURCE_SYSTEM_PARTY_CHARC SSPC
                           USING (SELECT l_sspc_chgs(indx).PTY_CHARC_TY_ID AS PTY_CHARC_TY_ID,
                                                  l_sspc_chgs(indx).SRC_SYS_PTY_ID AS SRC_SYS_PTY_ID,
                                                  l_sspc_chgs(indx).PTY_CHARC_TY_VLU AS PTY_CHARC_TY_VLU,
                                                  SYSTIMESTAMP AS CREATE_TS,
                                                  USER AS CREATE_USER_ID,
                                                  SYSTIMESTAMP AS UPDATE_TS,
                                                  USER AS UPDATE_USER_ID
                                        FROM DUAL) SRC
                                ON (SSPC.PTY_CHARC_TY_ID = SRC.PTY_CHARC_TY_ID
                               AND SSPC.SRC_SYS_PTY_ID = SRC.SRC_SYS_PTY_ID)
                    WHEN MATCHED THEN
                         UPDATE SET PTY_CHARC_TY_VLU =  SRC.PTY_CHARC_TY_VLU
                                           ,UPDATE_TS = SRC.UPDATE_TS
                                           ,UPDATE_USER_ID = SRC.UPDATE_USER_ID               
                    WHEN NOT MATCHED THEN
                        INSERT  ( SRC_SYS_PTY_CHARC_ID, PTY_CHARC_TY_ID, SRC_SYS_PTY_ID,
                                      PTY_CHARC_TY_VLU, CREATE_TS, CREATE_USER_ID, 
                                      UPDATE_TS, UPDATE_USER_ID)
                                   VALUES (
                                        SOURCE_SYSTEM_PARTY_CHARC_SEQ.NEXTVAL,

...

l_sspc_chgs is used for bulk collect (from top of code) :

TYPE sspc_chgs IS TABLE OF SRC_SYS_PTY_CHARC_STG_CUR%ROWTYPE
         INDEX BY PLS_INTEGER; 

l_sspc_chgs sspc_chgs;

Upvotes: 0

Views: 237

Answers (1)

Bulat
Bulat

Reputation: 6979

You column "PARTY"."SOURCE_SYSTEM_PARTY_CHARC"."PTY_CHARC_TY_VLU" must have "NOT NULL" constraint.

Try to replace

l_sspc_chgs(indx).PTY_CHARC_TY_VLU AS PTY_CHARC_TY_VLU

with

NVL(l_sspc_chgs(indx).PTY_CHARC_TY_VLU, 'Default value') AS PTY_CHARC_TY_VLU

Upvotes: 1

Related Questions