oracle - ORA-01400: comportamiento extraño
null date oracle (1)
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 ...
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.