oracle - ORA-01400: comportamiento extraño

null date oracle

Question

Oracle Database 11g Enterprise Edition versión 11.2.0.3.0 - Producción de 64 bits

A, B, C y D son VARCHAR2

E es una FECHA

(A, B, C, D, E) es la clave principal de TABLEDESTINATION

truncate table TABLEDESTINATION;

INSERT  /*+ parallel(10) */  INTO TABLEDESTINATION (A,B,C,D, E)
  SELECT
    TABLE1.DATA1,
    TABLE2.DATA2,
    TABLE2.DATA3,
    NVL(TABLE3.DATA4, ''-'') DATA4,
    TRUNC(TABLE1.DATA_DATE ,''MONTH'') DATA_DATE
  FROM TABLE1 
    INNER JOIN TABLE2 ON TABLE1.DATA1 = TABLE2.DATA1 AND TABLE1.Z = TABLE2.Z
    LEFT JOIN TABLE3 ON TABLE1.X=TABLE3.X
  GROUP BY 
    TABLE1.DATA1,  TABLE2.DATA2, TABLE2.DATA3, 
    NVL(TABLE3.DATA4, ''-''),  TRUNC(TABLE1.DATA_DATE ,''MONTH'');

Se traducirá en "ORA-01400: no se puede insertar NULL en (" USUARIO "." TABLEDESTINATION "." E ")"

Ahora si uso exactamente la misma consulta, pero usando una tabla temporal:

truncate table TABLEDESTINATION;

CREATE TABLE TEST_TABLE AS
  SELECT
    TABLE1.DATA1,
    TABLE2.DATA2,
    TABLE2.DATA3,
    NVL(TABLE3.DATA4, ''-'') DATA4,
    TRUNC(TABLE1.DATA_DATE ,''MONTH'') DATA_DATE
  FROM TABLE1 
    INNER JOIN TABLE2 ON TABLE1.DATA1 = TABLE2.DATA1 AND TABLE1.Z = TABLE2.Z
    LEFT JOIN TABLE3 ON TABLE1.X=TABLE3.X
  GROUP BY 
    TABLE1.DATA1, TABLE2.DATA2, TABLE2.DATA3, 
    NVL(TABLE3.DATA4, ''-''),  
    TRUNC(TABLE1.DATA_DATE ,''MONTH'');

INSERT  /*+ parallel(10) */  INTO TABLEDESTINATION (A,B,C,D, E)
  select DATA1,DATA2,DATA3, DATA4, DATA_DATE
  from TEST_TABLE;

Las líneas están insertadas correctamente, sin ningún error.

Ahora intento la misma consulta, pero con un NVL para la fecha:

INSERT  /*+ parallel(10) */  INTO TABLEDESTINATION (A,B,C,D, E)
  SELECT
    TABLE1.DATA1,
    TABLE2.DATA2,
    TABLE2.DATA3,
    NVL(TABLE3.DATA4, ''-'') DATA4,
    /* -> */ NVL(TRUNC(TABLE1.DATA_DATE ,''MONTH''), SYSDATE) /* <- */ DATA_DATE
  FROM TABLE1 
    INNER JOIN TABLE2 ON TABLE1.DATA1 = TABLE2.DATA1 AND TABLE1.Z = TABLE2.Z
    LEFT JOIN TABLE3 ON TABLE1.X=TABLE3.X
  GROUP BY 
    TABLE1.DATA1,  TABLE2.DATA2, TABLE2.DATA3, 
    NVL(TABLE3.DATA4, ''-''),  
    /* -> */ NVL(TRUNC(TABLE1.DATA_DATE ,''MONTH''), SYSDATE) /* <- */
  ;

Las líneas están insertadas correctamente, sin ningún error.

Lógicamente, significaría que tengo valores NULL en DATA_DATE:

SELECT
  TABLE1.DATA1,
  TABLE2.DATA2,
  TABLE2.DATA3,
  NVL(TABLE3.DATA4, ''-'') DATA4,
  NVL(TRUNC(TABLE1.DATA_DATE ,''MONTH''), SYSDATE) DATA_DATE
FROM TABLE1 
  INNER JOIN TABLE2 ON TABLE1.DATA1 = TABLE2.DATA1 AND TABLE1.Z = TABLE2.Z
  LEFT JOIN TABLE3 ON TABLE1.X=TABLE3.X
WHERE 
  TABLE1.DATA_DATE IS NULL
;

Devuelve la línea 0

SELECT
  TABLE1.DATA1,
  TABLE2.DATA2,
  TABLE2.DATA3,
  NVL(TABLE3.DATA4, ''-'') DATA4,
  NVL(TRUNC(TABLE1.DATA_DATE ,''MONTH''), SYSDATE) DATA_DATE
FROM TABLE1 
  INNER JOIN TABLE2 ON TABLE1.DATA1 = TABLE2.DATA1 AND TABLE1.Z = TABLE2.Z
  LEFT JOIN TABLE3 ON TABLE1.X=TABLE3.X
WHERE 
  TRUNC(TABLE1.DATA_DATE) IS NULL
;

Devuelve la línea 0

¿Cómo es posible que no tenga valores NULL en DATA_DATE pero Oracle me arroja un ORA-01400?

¿Cómo es posible que usando una tabla temporal, con la misma consulta exacta, no obtenga el mismo error?

EDITAR: Como sugerido, también intenté usar DISTINCT:

INSERT  /*+ parallel(10) */  INTO TABLEDESTINATION (A,B,C,D, E)
  SELECT DISTINCT
    TABLE1.DATA1,
    TABLE2.DATA2,
    TABLE2.DATA3,
    NVL(TABLE3.DATA4, ''-'') DATA4,
    TRUNC(TABLE1.DATA_DATE ,''MONTH'') DATA_DATE
  FROM TABLE1 
    INNER JOIN TABLE2 ON TABLE1.DATA1 = TABLE2.DATA1 AND TABLE1.Z = TABLE2.Z
    LEFT JOIN TABLE3 ON TABLE1.X=TABLE3.X

Devuelve el siguiente error: ORA-12805: el servidor de consultas paralelas murió inesperadamente

EDIT2: Si elimino la pista paralela, obtengo este error: " ORA-00001: restricción única (ZRA.ZRAQX_VENTE_DOPR_MOIS_PK2) violada " Que todavía no entiendo, ya que estoy usando un GROUP BY en la clave principal, y no tengo cualquier valor NULL ...

EDIT3: Intenté usar un cursor implícito:

FOR CURTEST IN (/* same select as above */) LOOP
INSERT INTO TABLEDESTINATION (A,B,C,D,E) values (CURTEST.DATA1,CURTEST.DATA2,CURTEST.DATA3,CURTEST.DATA4, CURTEST.DATA_DATE);
END LOOP;

Y sorpresa: ¡Funciona! Sin errores.

Nada tiene sentido más ...

Answer 1

Encontré el origen del error:

TRUNC(TABLE1.DATA_DATE ,''MONTH'') DATA_DATE

Si uso esto en su lugar:

TRUNC(TABLE1.DATA_DATE ,''MM'') DATA_DATE

Funciona !

Pude reproducir el error con MES después de.

Entonces realmente fue un error de Oracle con TRUNC y ''MONTH''

Gracias a todos por sus consejos.