Menu de navegação

[João OtávioParticipante]

Boa tarde pessoal !

Fiz um SQL Loader para importar arquivos .csv para meu banco de dados Oracle e ele está retornando o seguinte erro de inserção:

[b][i]Record 10: Rejected – Error on table TBLA51_FRETE_AEREO, column A51_CREATION_DATE.
ORA-01840: input value not long enough for date format
[/i][/b]
Dentro do meu Load estou passando o seguinte:
A51_CREATION_DATE DATE “YYYY-MM-DD”,

Alguém sabe como solucionar esse o erro ?

Dentro do meu log também está me retornando outro erro, que provavelmente tem a ver com o primeiro:
[i]Record 11: Rejected – Error on table TBLA51_FRETE_AEREO, column A51_CREATION_DATE.
ORA-01843: not a valid month[/i]

Obrigado

[rmanParticipante]

@Joao21

Posta o arquivo de controle e a linhas 10 e 11 do arquivo .csv.

Pelo que parece o formato da data do arquivo .csv não corresponde ao que está definido do arquivo de controle.

[João OtávioParticipante]

LINHA 10:
“27b9899025″,”BH”,”CB348″,”CNH AMERICA LLC – RACINE”,”700 STATE ST”,”RACINE”,”WI”,”53404-3343″,”USA”,”0″,”0″,”WEU_BH”,”CNH LATIN AMERICA LTDA – BELO HORIZONTE PLANT”,”AV. GEL DAVID SARNOFF, 2237″,”INCONFIDENTES, CONTAGEM”,”MG”,”32210-110″,”BRA”,”100″,”0″,””,””,””,””,””,””,””,””,””,”2013-02-07″,”8″,”0″,”18″,”0″,”2013-02-07″,”8″,”0″,”18″,”0″,”Vinicius RODRIGUES”,”paulo generoso”,”celio nunes”,”adriano gandra”,”07″,”INVOICE: 00005826/238-6222 1000/84398009 68/87521766 5/87521766 10/87521766 10/191703A1 230/87585167 20/87325760 1/87325760 1/70923002 6/87000146 100/”,”Air Freight”,”EU_5T”,”221.73″,”KG”,”KG”,”49.22″,”CF”,”CUFT”,”INVOICE: 00005826/238-6222 1000/84398009 68/87521766 5/87521766 10/87521766 10/191703A1 230/87585167 20/87325760 1/87325760 1/70923002 6/87000146 100/”,”322821″,”UTI USA INC”,”USD”,”1400″,””,”2013-02-07″,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””

LINHA11:
“e2a5396609″,”BH”,”BJZ89″,”KAB SEATING”,”STONE CIRCLE ROAD”,”ROUND SPINNEY”,”NTH”,”NN3 8RS”,”GBR”,”0″,”0″,”PLT_BLH”,”BELO HORIZONTE PLANT”,”AV. GENERAL DAVID SARNOFF, 2237″,”CONTAGEM”,””,”32210-110″,”BRA”,”100″,”0″,””,””,””,””,””,””,””,””,””,”2013-01-21″,””,””,””,””,”2013-01-28″,””,””,””,””,”Graciela VIEIRA”,”PAULO GENEROSO”,”CELIO NUNES”,”PAOLO BIANCO”,”20-B”,””,”Air Freight”,”EU_5T”,”178″,”KG”,”KG”,”1.85″,”UM”,”CUMTR”,”GOODS ARE AVAILABLE AR SUPPLIER/INV # INV171995″,”14181″,”VENTANA SERRA”,”USD”,”1.46″,”CHEX”,”2013-01-17″,””,””,””,”87515608″,”5″,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””,””

Como pode ver, ambas as datas do arquivo CSV estão no formato YYYY-MM-DD e no Control File abaixo, eu tento converter para DATA (A51_CREATION_DATE DATE “YYYY-MM-DD”,):

CONTROL FILE:
LOAD DATA
INFILE ‘aetc.csv’
BADFILE ‘LoadResults_BAD.log’
DISCARDFILE ‘LoadResults_DISCARDED.log’
APPEND
INTO TABLE TBLA51_FRETE_AEREO
Fields Terminated by “,”
Optionally Enclosed by ‘”‘
Trailing Nullcols
(
A51_AETC_CODE,
A51_PLANT_CODE,
A51_SENDER_CODE,
A51_SENDER_NAME,
A51_SENDER_STREET1,
A51_SENDER_CITY,
A51_SENDER_STATE,
A51_SENDER_ZIP,
A51_SENDER_COUNTRY,
A51_RECHARGE_PERCENTAGE_S,
A51_URGENT_COST_RECHARGE_S,
A51_RECEIVER_CODE,
A51_RECEIVER_NAME,
A51_RECEIVER_STREET1,
A51_RECEIVER_CITY,
A51_RECEIVER_STATE,
A51_RECEIVER_ZIP,
A51_RECEIVER_COUNTRY,
A51_RECHARGE_PERCENTAGE_R,
A51_URGENT_COST_RECHARGE_R,
A51_MBU_PROD_LINE_CODE_1,
A51_MBU_PROD_LINE_NAME_1,
A51_MBU_PROD_LINE_PERC_1,
A51_MBU_PROD_LINE_CODE_2,
A51_MBU_PROD_LINE_NAME_2,
A51_MBU_PROD_LINE_PERC_2,
A51_MBU_PROD_LINE_CODE_3,
A51_MBU_PROD_LINE_NAME_3,
A51_MBU_PROD_LINE_PERC_3,
A51_PICKUP_DATE DATE “YYYY-MM-DD”,
A51_PICKUP_DATE_BETWEEN_HOUR,
A51_PICKUP_DATE_BETWEEN_MINUTE,
A51_PICKUP_DATE_AND_HOUR,
A51_PICKUP_DATE_AND_MINUTE,
A51_DELIVERY_DATE DATE “YYYY-MM-DD”,
A51_DELIVERY_DATE_BETWEEN_HOUR,
A51_DELIVERY_DATE_BETWEEN_MIN,
A51_DELIVERY_DATE_AND_HOUR,
A51_DELIVERY_DATE_AND_MINUTE,
A51_CNH_AUTHORIZER,
A51_CNH_AUTHORIZERLEVEL1,
A51_CNH_AUTHORIZERLEVEL2,
A51_CNH_AUTHORIZERLEVEL3,
A51_REASON_CODE,
A51_PREMIUM_CATEGORY CHAR(1000),
A51_EQUIPMENT_TYPE_ID,
A51_UOMDESCGROSSWEIGHT,
A51_UOMGROSSWEIGHT,
A51_TOTAL_VOLUME,
A51_UOMTOTALVOLUME,
A51_UOMDESCTOTALVOLUME,
A51_NOTE CHAR(1000),
A51_CARRIER_ID,
A51_CARRIER_NAME,
A51_CURRENCY,
A51_COST,
A51_COSTCENTER_PROJECTNUMBER,
A51_CREATION_DATE DATE “YYYY-MM-DD”,
A51_SHIPMENT_ID,
A51_PICKUP_REQUEST_ID,
A51_CARRIER_CODE,
A51_PARTNUMBER,
A51_NR_PIECES,
A51_PCONTACT_CSCN_ID,
A51_PCONTACT_CSCN_USERID_S,
A51_PCONTACT_EMAIL,
A51_PCONTACT_FAX,
A51_PCONTACT_FIRST_NAME,
A51_PCONTACT_LAST_NAME,
A51_PCONTACT_PHONE,
A51_PICKUP_EARLY,
A51_PICKUP_LATE,
A51_PICKUP_TYPE,
A51_REMARK CHAR(1000),
A51_SCONTACT_CSCN_ID,
A51_SCONTACT_CSCN_USERID_S,
A51_SCONTACT_EMAIL,
A51_SCONTACT_FAX,
A51_SCONTACT_FIRST_NAME,
A51_SCONTACT_LAST_NAME,
A51_SCONTACT_PHONE,
A51_SHIP_FROM_CITY,
A51_SHIP_FROM_ISO_COUNTRY,
A51_SHIP_FROM_LOCATION_ID,
A51_SHIP_FROM_NAME,
A51_SHIP_FROM_STATE,
A51_SHIP_FROM_STREET1,
A51_SHIP_FROM_STREET2,
A51_SHIP_FROM_ZIP,
A51_SHIP_TO_CITY,
A51_SHIP_TO_ISO_COUNTRY,
A51_SHIP_TO_LOCATION_ID,
A51_SHIP_TO_NAME,
A51_SHIP_TO_STATE,
A51_SHIP_TO_STREET1,
A51_SHIP_TO_STREET2,
A51_SHIP_TO_ZIP,
A51_EXPEDITED_AUTHORIZATION_C,
A51_EXPEDITED_AUTHORIZED_BY,
A51_STATUS,
A51_PUR_DATE_INSERTED DATE “YYYY-MM-DD HH24:MI”,
A51_PUR_DUE_DATE DATE “YYYY-MM-DD HH24:MI”,
A51_PUR_EQUIPMENT_TYPE_ID,
A51_PUR_ER_CODE,
A51_PUR_SML_CODE,
A51_SHIP_CARRIER_NAME,
A51_SHIP_CARRIER_REFERENCE,
A51_SHIP_DATE_INSERTED DATE “YYYY-MM-DD HH24:MI”,
A51_SHIP_DUE_DATE DATE “YYYY-MM-DD HH24:MI”,
A51_SHIP_EQUIPMENT_TYPE_ID,
A51_SHIP_ESTIMATED_DEL_DATE DATE “YYYY-MM-DD HH24:MI”,
A51_SHIP_STATUS_DATE DATE “YYYY-MM-DD HH24:MI”,
A51_SHIP_STATUS_REASON,
A51_GROSSWEIGHT,
A51_REMARKS
)

[rmanParticipante]

@Joao21

Poste o resultado da consulta, utilizando o mesmo usuário do sql loader.


SELECT *
FROM NLS_SESSION_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT'


Poste também a primeira linha do arquivo .csv

[João OtávioParticipante]

RESULTADO CONSULTA
NLS_DATE_FORMAT DD/MM/RRRR HH:MI:SS

PRIMEIRA LINHA
“AETC_CODE”,”PLANT_CODE”,”SENDER_CODE”,”SENDER_NAME”,”SENDER_STREET1″,”SENDER_CITY”,”SENDER_STATE”,”SENDER_ZIP”,”SENDER_COUNTRY”,
“RECHARGE_PERCENTAGE_SENDER”,”URGENT_COST_RECHARGE_SENDER”,”RECEIVER_CODE”,”RECEIVER_NAME”,”RECEIVER_STREET1″,”RECEIVER_CITY”,
“RECEIVER_STATE”,”RECEIVER_ZIP”,”RECEIVER_COUNTRY”,”RECHARGE_PERCENTAGE_RECEIVER”,”URGENT_COST_RECHARGE_RECEIVER”,”MBU_Prod_Line_Code_1″,
“MBU_Prod_Line_Name_1″,”MBU_Prod_Line_Perc_1″,”MBU_Prod_Line_Code_2″,”MBU_Prod_Line_Name_2″,”MBU_Prod_Line_Perc_2″,”MBU_Prod_Line_Code_3”,
“MBU_Prod_Line_Name_3″,”MBU_Prod_Line_Perc_3″,”PICKUP_DATE”,”PICKUP_DATE_BETWEEN_HOUR”,”PICKUP_DATE_BETWEEN_MINUTE”,
“PICKUP_DATE_AND_HOUR”,”PICKUP_DATE_AND_MINUTE”,”DELIVERY_DATE”,”DELIVERY_DATE_BETWEEN_HOUR”,”DELIVERY_DATE_BETWEEN_MINUTE”,
“DELIVERY_DATE_AND_HOUR”,”DELIVERY_DATE_AND_MINUTE”,”CNH_AUTHORIZER”,”CNH_AUTHORIZERLEVEL1″,”CNH_AUTHORIZERLEVEL2″,”CNH_AUTHORIZERLEVEL3″,
“REASON_CODE”,”REMARKS”,”PREMIUM_CATEGORY”,”EQUIPMENT_TYPE_ID”,”GROSSWEIGHT”,”UOMDESCGROSSWEIGHT”,”UOMGROSSWEIGHT”,
“TOTAL_VOLUME”,”UOMTOTALVOLUME”,”UOMDESCTOTALVOLUME”,”NOTE”,”CARRIER_ID”,”CARRIER_NAME”,”CURRENCY”,”COST”,”Cost Center/ Project Number”,”CREATION_DATE”,”SHIPMENT_ID”,
“PICKUP_REQUEST_ID”,”CARRIER_CODE”,”PARTNUMBER”,”NR_PIECES”,”PCONTACT_CSCN_ID”,”PCONTACT_CSCN_USERID_SUFFIX”,”PCONTACT_EMAIL”,
“PCONTACT_FAX”,”PCONTACT_FIRST_NAME”,”PCONTACT_LAST_NAME”,”PCONTACT_PHONE”,”PICKUP_EARLY”,”PICKUP_LATE”,”PICKUP_TYPE”,”REMARK”,
“SCONTACT_CSCN_ID”,”SCONTACT_CSCN_USERID_SUFFIX”,”SCONTACT_EMAIL”,”SCONTACT_FAX”,”SCONTACT_FIRST_NAME”,”SCONTACT_LAST_NAME”,
“SCONTACT_PHONE”,”SHIP_FROM_CITY”,”SHIP_FROM_ISO_COUNTRY”,”SHIP_FROM_LOCATION_ID”,”SHIP_FROM_NAME”,”SHIP_FROM_STATE”,
“SHIP_FROM_STREET1″,”SHIP_FROM_STREET2″,”SHIP_FROM_ZIP”,”SHIP_TO_CITY”,”SHIP_TO_ISO_COUNTRY”,”SHIP_TO_LOCATION_ID”,”SHIP_TO_NAME”,
“SHIP_TO_STATE”,”SHIP_TO_STREET1″,”SHIP_TO_STREET2″,”SHIP_TO_ZIP”,”EXPEDITED_AUTHORIZATION_CODE”,”EXPEDITED_AUTHORIZED_BY”,
“STATUS”,”PUR_DATE_INSERTED”,”PUR_DUE_DATE”,”PUR_EQUIPMENT_TYPE_ID”,”PUR_ER_CODE”,”PUR_SML_CODE”,”SHIP_CARRIER_NAME”,
“SHIP_CARRIER_REFERENCE”,”SHIP_DATE_INSERTED”,”SHIP_DUE_DATE”,”SHIP_EQUIPMENT_TYPE_ID”,
“SHIP_ESTIMATED_DEL_DATE”,”SHIP_STATUS_DATE”,”SHIP_STATUS_REASON”

São muitas colunas no banco, mas as que usam o campo no formato DATE são:
– PICKUP_DATE
– DELIVERY_DATE
– CREATION_DATE
– PUR_DATE_INSERTED
– PUR_DUE_DATE
– SHIP_DATE_INSERTED
– SHIP_DUE_DATE
– SHIP_ESTIMATED_DEL_DATE
– SHIP_STATUS_DATE

[rmanParticipante]

@Joao21

Creio que existem 2 erros:

Primeiro o formato da data esperado é DD/MM/RRRR. Logo terá que gerar o arquivo .csv com esse formato. E no arquivo de controle também utilize esse formato.

Segundo o arquivo .csv me parece estar errado, o campo A51_CREATION_DATE corresponde a posição 58, e pelos registros a posição 58 tem o custo e não a data.

[João OtávioParticipante]

Isto mesmo @rman !

Na hora que você me pediu a primeira linha do arquivo .csv que caiu minha ficha..

O problema foi resolvido e foi um erro meu ! Completa distração !

Tive de inserir novas colunas na tabela e esqueci de alterar a sequencia no Control File.
Ele acabava inserindo os dados em colunas erradas.

Me desculpem pelo transtorno, e muito obrigado @rman pela atenção.

Abraço

[Autor]

Posts