viernes, 27 de julio de 2007

SQL*loader: Field in data file exceeds maximum length

Recientemente tuve la oportunidad de ver un problema en un aplicativo que utilizaba SQL*loader 9.2.0.6 para cargar datos de un archivo plano en una tabla, originalmente separados por pipes y eventualmente conteniendo espacios en uno de sus campos.

Un ejemplo del contenido del archivo de datos (los pipes van en color para identificar mejor los campos):

A1|3|02/12/98|NN|A Suspender |34
A2|2|02/12/98|NS|No se presentó, fue enviado nuevo aviso         (**)      |27
A3|3|02/14/98|NS||9

(**) La segunda línea de datos contiene luego del texto y antes del siguiente pipe, más de 300 espacios.

El control file utilizado para cargar ese archivo tenía esta forma:

LOAD DATA
APPEND
INTO TABLE TMP_SUSPENSION
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
SEGMENTO,
SUBSEGMENTO,
FECHA_VENCIMIENTO DATE "MM/DD/RRRR",
ESTADO_FINANCIERO,
DESCRIPCION "TRIM(:DESCRIPCION)",
CODIGO_CATEGORIA
)

Al campo descripcion se le había agregado la función TRIM para eliminar los espacios sobrantes, sin embargo, cuando se ejecutaba SQL*loader la carga fallaba y parecía ignorar la función:

Record 2: Rejected - Error on table TMP_SUSPENSION, column DESCRIPCION.
Field in data file exceeds maximum length

Table TMP_SUSPENSION:
2 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

La causa del problema no era que la función TRIM no estaba cortando los caracteres ni que el campo en la tabla no tuviera el largo suficiente, sino la mala práctica de no especificar tipos en los campos del control file. Recordemos que los campos que se definen allí no corresponden a la tabla destino sino a lo que se espera encontrar en el archivo fuente. Cuando no se especifica tipo y largo en un campo del control file, Oracle asume el largo por defecto de 255 caracteres. Por lo tanto, al encontrar mas de 255 caracteres entre dos pipes, se rechaza el mismo antes siquiera de aplicar la función TRIM. De la forma que opera SQL*loader, primero se validan los tipos en el archivo, y luego se aplican las funciones sobre los campos identificados.

Como solución, se arregló el control file para que tuviera el tipo especificado de un largo mayor al previsto. Esta simple buena práctica es muy recomendada para SQL*loader.

LOAD DATA
APPEND
INTO TABLE TMP_SUSPENSION
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
SEGMENTO,
SUBSEGMENTO,
FECHA_VENCIMIENTO DATE "MM/DD/RRRR",
ESTADO_FINANCIERO,
DESCRIPCION CHAR(600) "TRIM(:DESCRIPCION)",
CODIGO_CATEGORIA
)

10 comentarios:

jaitrum dijo...

Gracias, me pasaba algo parecido y gracias a tu entrada lo he solucionado en un plis.. un saludo.

mario dijo...

Muchas gracias, a mí también me ha servido, tenía el mismo problema.

Anónimo dijo...

Gracias, me resolvio un problema que tuve en migracion de datos.

Anónimo dijo...

genial, gracias!! me ha sacado de un apuro!!!

Anónimo dijo...

Gracias. No podía entender por qué no cargaban ciertos registros. Realizando el ajuste funcionó perfectamente.

Anónimo dijo...

Gracias, también me ayudó.

Anónimo dijo...

Gracias, ya tenia un buen rato tratando de resolver con otras sugerencias y nomas nada que se podia, gracias a tu Publicacion logre Resolver.

Atte.
K.C.

Anónimo dijo...

Hola... muchas gracias...

Anónimo dijo...

Muchas gracias!! me ayudo mucho tu entrada, no entendia que estaba pasando! :)

Alexandra Renee Tomairo Zevallos dijo...

una consulta tengo este dato |12052015 13:05:41| que sera insertada en un campo date de la siguiente forma 12/05/2015 13:05:41 ,como deberia ir en el batch. gracias