Home » RDBMS Server » Server Utilities » SQLLDR decimal field problem
SQLLDR decimal field problem [message #447476] |
Mon, 15 March 2010 07:10 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kari0ca
Messages: 3 Registered: March 2010
|
Junior Member |
|
|
Hi there,
I'm having a problem with sqlldr trying to load some decimal data.
All the process was working without the decimal fields, then when i changed it to load decimal, the problems appeared.
this is my CTL:
LOAD DATA
APPEND
INTO TABLE BSC_L_TRANSVERSAL
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ID_TRANSVERSAL "SEQUENCE_F_TRANSVERSAL.NEXTVAL"
, OE INTEGER EXTERNAL NULLIF (OE=BLANKS)
, OO INTEGER EXTERNAL NULLIF (OO=BLANKS)
, DIRECCAO INTEGER EXTERNAL NULLIF (DIRECCAO=BLANKS)
, DATA "get_data(:data)"
, VAL_MD_SATISF_CLIENTES_EXT DECIMAL EXTERNAL NULLIF (VAL_MD_SATISF_CLIENTES_EXT=BLANKS)
, VAL_MD_SATISF_COLABORADORES DECIMAL EXTERNAL NULLIF (VAL_MD_SATISF_COLABORADORES=BLANKS)
, N_NC_AUDIT_PROCS_PR INTEGER EXTERNAL NULLIF (N_NC_AUDIT_PROCS_PR=BLANKS)
, N_TT_NC_PROCS_ANO INTEGER EXTERNAL NULLIF (N_TT_NC_PROCS_ANO=BLANKS)
, N_ACCOES_MELH_TRAT_PR INTEGER EXTERNAL NULLIF (N_ACCOES_MELH_TRAT_PR=BLANKS)
, N_TT_ACCOES_MELHORIA INTEGER EXTERNAL NULLIF (N_TT_ACCOES_MELHORIA=BLANKS)
, N_RECLAM_ANAL_TRAT_PR INTEGER EXTERNAL NULLIF (N_RECLAM_ANAL_TRAT_PR=BLANKS)
, N_TT_RECLAM INTEGER EXTERNAL NULLIF (N_TT_RECLAM=BLANKS)
, N_NOVOS_PROC_CERT INTEGER EXTERNAL NULLIF (N_NOVOS_PROC_CERT=BLANKS)
, TX_REALIZACAO_PROG_EVOLVERE INTEGER EXTERNAL NULLIF (TX_REALIZACAO_PROG_EVOLVERE=BLANKS)
, N_PROJ_EVOLVERE_SEM_DESV INTEGER EXTERNAL NULLIF (N_PROJ_EVOLVERE_SEM_DESV=BLANKS)
, N_TT_PROJ_EVOLVERE INTEGER EXTERNAL NULLIF (N_TT_PROJ_EVOLVERE=BLANKS)
, N_ACCOES_COL_INST_DES INTEGER EXTERNAL NULLIF (N_ACCOES_COL_INST_DES=BLANKS)
, N_EVT_SAT_INT_B_MB_REL_ORG INTEGER EXTERNAL NULLIF (N_EVT_SAT_INT_B_MB_REL_ORG=BLANKS)
, N_EVT_SAT_EXT_B_MB_REL_ORGAN INTEGER EXTERNAL NULLIF (N_EVT_SAT_EXT_B_MB_REL_ORGAN=BLANKS)
, N_EVT_SAT_EXT_B_MB_REL_CONT INTEGER EXTERNAL NULLIF (N_EVT_SAT_EXT_B_MB_REL_CONT=BLANKS)
, N_TT_EVENTOS INTEGER EXTERNAL NULLIF (N_TT_EVENTOS=BLANKS)
, VAL_RECEITA_COBRADA INTEGER EXTERNAL NULLIF (VAL_RECEITA_COBRADA=BLANKS)
, VAL_RECEITA_LIQUIDADA INTEGER EXTERNAL NULLIF (VAL_RECEITA_LIQUIDADA=BLANKS)
, VAL_DESP_PAGA INTEGER EXTERNAL NULLIF (VAL_DESP_PAGA=BLANKS)
, VAL_DESP_COMPROMETIDA INTEGER EXTERNAL NULLIF (VAL_DESP_COMPROMETIDA=BLANKS)
, N_SAIDAS INTEGER EXTERNAL NULLIF (N_SAIDAS=BLANKS)
, N_COLABORADORES INTEGER EXTERNAL NULLIF (N_COLABORADORES=BLANKS)
, N_PEDIDOS_NOTIF_SUP_ELEC INTEGER EXTERNAL NULLIF (N_PEDIDOS_NOTIF_SUP_ELEC=BLANKS)
, N_PEDIDOS INTEGER EXTERNAL NULLIF (N_PEDIDOS=BLANKS)
, DATA_CTRL INTEGER EXTERNAL NULLIF (DATA_CTRL=BLANKS)
)
this is my bad file (the line that is generating the problem):
0,3,7,17,20091231,87.5,,,,,,,,,,,,,,,,,,,,,,,,,20100315
and this is the definition of the table:
CREATE TABLE "MSTR_DATASOURCE"."BSC_L_TRANSVERSAL"
( "ID_TRANSVERSAL" NUMBER(8,0) NOT NULL ENABLE,
"OE" NUMBER(8,0) NOT NULL ENABLE,
"OO" NUMBER(8,0) NOT NULL ENABLE,
"DIRECCAO" NUMBER(8,0) NOT NULL ENABLE,
"DATA" NUMBER(8,0) NOT NULL ENABLE,
"VAL_MD_SATISF_CLIENTES_EXT" NUMBER(5,2),
"VAL_MD_SATISF_COLABORADORES" NUMBER(5,2),
"N_NC_AUDIT_PROCS_PR" NUMBER(5,0),
"N_TT_NC_PROCS_ANO" NUMBER(5,0),
"N_ACCOES_MELH_TRAT_PR" NUMBER(5,0),
"N_TT_ACCOES_MELHORIA" NUMBER(5,0),
"N_RECLAM_ANAL_TRAT_PR" NUMBER(5,0),
"N_TT_RECLAM" NUMBER(5,0),
"N_NOVOS_PROC_CERT" NUMBER(5,0),
"TX_REALIZACAO_PROG_EVOLVERE" NUMBER(5,0),
"N_PROJ_EVOLVERE_SEM_DESV" NUMBER(5,0),
"N_TT_PROJ_EVOLVERE" NUMBER(5,0),
"N_ACCOES_COL_INST_DES" NUMBER(5,0),
"N_EVT_SAT_INT_B_MB_REL_ORG" NUMBER(5,0),
"N_EVT_SAT_EXT_B_MB_REL_ORGAN" NUMBER(5,0),
"N_EVT_SAT_EXT_B_MB_REL_CONT" NUMBER(5,0),
"N_TT_EVENTOS" NUMBER(5,0),
"VAL_RECEITA_COBRADA" NUMBER(8,0),
"VAL_RECEITA_LIQUIDADA" NUMBER(8,0),
"VAL_DESP_PAGA" NUMBER(8,0),
"VAL_DESP_COMPROMETIDA" NUMBER(8,0),
"N_SAIDAS" NUMBER(5,0),
"N_COLABORADORES" NUMBER(5,0),
"N_PEDIDOS_NOTIF_SUP_ELEC" NUMBER(5,0),
"N_PEDIDOS" NUMBER(5,0),
"DATA_CTRL" NUMBER(8,0) NOT NULL ENABLE
)
this was the log:
SQL*Loader: Release 10.2.0.1.0 - Production on Seg Mar 15 10:17:49 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
...
Tabela BSC_L_TRANSVERSAL, carregada a partir de cada registo lógico.
Inserir opção em vigor para esta tabela: APPEND
Opção TRAILING NULLCOLS em vigor
Nome da Coluna Posição Len Term Encl Tipo de Dados
------------------------------ ---------- ----- ---- ---- ---------------------
ID_TRANSVERSAL FIRST * , CHARACTER
cadeia de caracteres de SQL para a coluna: "SEQUENCE_F_TRANSVERSAL.NEXTVAL"
OE NEXT * , CHARACTER
NULL if OE = BLANKS
OO NEXT * , CHARACTER
NULL if OO = BLANKS
DIRECCAO NEXT * , CHARACTER
NULL if DIRECCAO = BLANKS
DATA NEXT * , CHARACTER
cadeia de caracteres de SQL para a coluna: "get_data(:data)"
VAL_MD_SATISF_CLIENTES_EXT NEXT * , CHARACTER
NULL if VAL_MD_SATISF_CLIENTES_EXT = BLANKS
VAL_MD_SATISF_COLABORADORES NEXT * , CHARACTER
NULL if VAL_MD_SATISF_COLABORADORES = BLANKS
N_NC_AUDIT_PROCS_PR NEXT * , CHARACTER
NULL if N_NC_AUDIT_PROCS_PR = BLANKS
N_TT_NC_PROCS_ANO NEXT * , CHARACTER
NULL if N_TT_NC_PROCS_ANO = BLANKS
N_ACCOES_MELH_TRAT_PR NEXT * , CHARACTER
NULL if N_ACCOES_MELH_TRAT_PR = BLANKS
N_TT_ACCOES_MELHORIA NEXT * , CHARACTER
NULL if N_TT_ACCOES_MELHORIA = BLANKS
N_RECLAM_ANAL_TRAT_PR NEXT * , CHARACTER
NULL if N_RECLAM_ANAL_TRAT_PR = BLANKS
N_TT_RECLAM NEXT * , CHARACTER
NULL if N_TT_RECLAM = BLANKS
N_NOVOS_PROC_CERT NEXT * , CHARACTER
NULL if N_NOVOS_PROC_CERT = BLANKS
TX_REALIZACAO_PROG_EVOLVERE NEXT * , CHARACTER
NULL if TX_REALIZACAO_PROG_EVOLVERE = BLANKS
N_PROJ_EVOLVERE_SEM_DESV NEXT * , CHARACTER
NULL if N_PROJ_EVOLVERE_SEM_DESV = BLANKS
N_TT_PROJ_EVOLVERE NEXT * , CHARACTER
NULL if N_TT_PROJ_EVOLVERE = BLANKS
N_ACCOES_COL_INST_DES NEXT * , CHARACTER
NULL if N_ACCOES_COL_INST_DES = BLANKS
N_EVT_SAT_INT_B_MB_REL_ORG NEXT * , CHARACTER
NULL if N_EVT_SAT_INT_B_MB_REL_ORG = BLANKS
N_EVT_SAT_EXT_B_MB_REL_ORGAN NEXT * , CHARACTER
NULL if N_EVT_SAT_EXT_B_MB_REL_ORGAN = BLANKS
N_EVT_SAT_EXT_B_MB_REL_CONT NEXT * , CHARACTER
NULL if N_EVT_SAT_EXT_B_MB_REL_CONT = BLANKS
N_TT_EVENTOS NEXT * , CHARACTER
NULL if N_TT_EVENTOS = BLANKS
VAL_RECEITA_COBRADA NEXT * , CHARACTER
NULL if VAL_RECEITA_COBRADA = BLANKS
VAL_RECEITA_LIQUIDADA NEXT * , CHARACTER
NULL if VAL_RECEITA_LIQUIDADA = BLANKS
VAL_DESP_PAGA NEXT * , CHARACTER
NULL if VAL_DESP_PAGA = BLANKS
VAL_DESP_COMPROMETIDA NEXT * , CHARACTER
NULL if VAL_DESP_COMPROMETIDA = BLANKS
N_SAIDAS NEXT * , CHARACTER
NULL if N_SAIDAS = BLANKS
N_COLABORADORES NEXT * , CHARACTER
NULL if N_COLABORADORES = BLANKS
N_PEDIDOS_NOTIF_SUP_ELEC NEXT * , CHARACTER
NULL if N_PEDIDOS_NOTIF_SUP_ELEC = BLANKS
N_PEDIDOS NEXT * , CHARACTER
NULL if N_PEDIDOS = BLANKS
DATA_CTRL NEXT * , CHARACTER
NULL if DATA_CTRL = BLANKS
o valor utilizado para o parâmetro ROWS foi alterado de 64 para 32
Registo 1: Rejeitado - Erro na tabela BSC_L_TRANSVERSAL, coluna VAL_MD_SATISF_CLIENTES_EXT.
ORA-01722: invalid number
Tabela BSC_L_TRANSVERSAL:
0 Linhas carregado com êxito.
1 Linha não foi carregada devido a erros de dados.
0 Linhas não foi carregada devido a falha de todas as cláusulas WHEN.
0 Linhas não foi carregada porque todos os campos eram nulos.
Espaço atribuído para matriz de associação: 255936 bytes(32 linhas)
Bytes do buffer de leitura: 1048576
Total de registos lógicos ignorados: 0
Total de registos lógicos lidos: 1
Total de registos lógicos rejeitados: 1
Total de registos lógicos excluídos: 0
Execução começou em Seg Mar 15 10:17:49 2010
Execução terminou em Seg Mar 15 10:17:49 2010
Tempo decorrido foi: 00:00:00.20
Tempo de CPU era: 00:00:00.08
I don't know why the sqlldr is not recognizing the data... (ORA-01722: invalid number), the field that has the problems has the definition number(5,2), the CTL has the decimal clausule...
can anyone help on this?
Thanx
|
|
|
|
Re: SQLLDR decimal field problem [message #447491 is a reply to message #447488] |
Mon, 15 March 2010 07:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kari0ca
Messages: 3 Registered: March 2010
|
Junior Member |
|
|
Michel Cadot wrote on Mon, 15 March 2010 07:46The problem may comes from a difference in decimal sign between what's inside the file and your NLS settings.
In the script that launches SQL*Loader force NLS_NUMERIC_CHARACTERS to be the one that is inside the file.
Regards
Michel
Hi Michel Cadot, thank you for your answer, but how do i change it? i must change it on the CTL file?
btw on the database all the decimal fields have , sign. and the excel exports with .
i'm not a DBA, i'm just an ordinary user
|
|
|
|
Re: SQLLDR decimal field problem [message #447496 is a reply to message #447491] |
Mon, 15 March 2010 08:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68676 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:btw on the database all the decimal fields have , sign
No in the database there is no sign, this is your NLS settings that put it when you query the database.
Quote:but how do i change it?
export NLS_NUMERIC_CHARACTERS='.'
sqlldr ...
Regards
Michel
[Updated on: Mon, 15 March 2010 08:54] Report message to a moderator
|
|
|
Re: SQLLDR decimal field problem [message #447497 is a reply to message #447493] |
Mon, 15 March 2010 08:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kari0ca
Messages: 3 Registered: March 2010
|
Junior Member |
|
|
Littlefoot wrote on Mon, 15 March 2010 07:59What is your database version, anyway?
As I've already tested here, it seems that I managed to load data you provided with no errors.
indeed, you tried and worked with you, but the problem persisted here...
i know what is, the problem is the format of the field.
Here on the database, all the decimal fields are formated as xxx,yy
not with a dot, a comma is used.
here goes my new CTL:
LOAD DATA
APPEND
INTO TABLE BSC_L_TRANSVERSAL
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ID_TRANSVERSAL "SEQUENCE_F_TRANSVERSAL.NEXTVAL"
, OE INTEGER EXTERNAL NULLIF (OE=BLANKS)
, OO INTEGER EXTERNAL NULLIF (OO=BLANKS)
, DIRECCAO INTEGER EXTERNAL NULLIF (DIRECCAO=BLANKS)
, DATA "get_data(:data)"
, VAL_MD_SATISF_CLIENTES_EXT "replace (:VAL_MD_SATISF_CLIENTES_EXT, '.', ',')"
, VAL_MD_SATISF_COLABORADORES "replace (:VAL_MD_SATISF_COLABORADORES, '.', ',')"
, N_NC_AUDIT_PROCS_PR INTEGER EXTERNAL NULLIF (N_NC_AUDIT_PROCS_PR=BLANKS)
, N_TT_NC_PROCS_ANO INTEGER EXTERNAL NULLIF (N_TT_NC_PROCS_ANO=BLANKS)
, N_ACCOES_MELH_TRAT_PR INTEGER EXTERNAL NULLIF (N_ACCOES_MELH_TRAT_PR=BLANKS)
, N_TT_ACCOES_MELHORIA INTEGER EXTERNAL NULLIF (N_TT_ACCOES_MELHORIA=BLANKS)
, N_RECLAM_ANAL_TRAT_PR INTEGER EXTERNAL NULLIF (N_RECLAM_ANAL_TRAT_PR=BLANKS)
, N_TT_RECLAM INTEGER EXTERNAL NULLIF (N_TT_RECLAM=BLANKS)
, N_NOVOS_PROC_CERT INTEGER EXTERNAL NULLIF (N_NOVOS_PROC_CERT=BLANKS)
, TX_REALIZACAO_PROG_EVOLVERE INTEGER EXTERNAL NULLIF (TX_REALIZACAO_PROG_EVOLVERE=BLANKS)
, N_PROJ_EVOLVERE_SEM_DESV INTEGER EXTERNAL NULLIF (N_PROJ_EVOLVERE_SEM_DESV=BLANKS)
, N_TT_PROJ_EVOLVERE INTEGER EXTERNAL NULLIF (N_TT_PROJ_EVOLVERE=BLANKS)
, N_ACCOES_COL_INST_DES INTEGER EXTERNAL NULLIF (N_ACCOES_COL_INST_DES=BLANKS)
, N_EVT_SAT_INT_B_MB_REL_ORG INTEGER EXTERNAL NULLIF (N_EVT_SAT_INT_B_MB_REL_ORG=BLANKS)
, N_EVT_SAT_EXT_B_MB_REL_ORGAN INTEGER EXTERNAL NULLIF (N_EVT_SAT_EXT_B_MB_REL_ORGAN=BLANKS)
, N_EVT_SAT_EXT_B_MB_REL_CONT INTEGER EXTERNAL NULLIF (N_EVT_SAT_EXT_B_MB_REL_CONT=BLANKS)
, N_TT_EVENTOS INTEGER EXTERNAL NULLIF (N_TT_EVENTOS=BLANKS)
, VAL_RECEITA_COBRADA INTEGER EXTERNAL NULLIF (VAL_RECEITA_COBRADA=BLANKS)
, VAL_RECEITA_LIQUIDADA INTEGER EXTERNAL NULLIF (VAL_RECEITA_LIQUIDADA=BLANKS)
, VAL_DESP_PAGA INTEGER EXTERNAL NULLIF (VAL_DESP_PAGA=BLANKS)
, VAL_DESP_COMPROMETIDA INTEGER EXTERNAL NULLIF (VAL_DESP_COMPROMETIDA=BLANKS)
, N_SAIDAS INTEGER EXTERNAL NULLIF (N_SAIDAS=BLANKS)
, N_COLABORADORES INTEGER EXTERNAL NULLIF (N_COLABORADORES=BLANKS)
, N_PEDIDOS_NOTIF_SUP_ELEC INTEGER EXTERNAL NULLIF (N_PEDIDOS_NOTIF_SUP_ELEC=BLANKS)
, N_PEDIDOS INTEGER EXTERNAL NULLIF (N_PEDIDOS=BLANKS)
, DATA_CTRL INTEGER EXTERNAL NULLIF (DATA_CTRL=BLANKS)
)
|
|
|
|
Re: SQLLDR decimal field problem [message #447619 is a reply to message #447497] |
Tue, 16 March 2010 13:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
If you have no control over what format the data is exported in, then you can use the replace command on each numeric field, for example:
VAL_MD_SATISF_COLABORADORES DECIMAL EXTERNAL NULLIF(VAL_MD_SATISF_COLABORADORES=BLANKS) "REPLACE(:val_md_satisf_colaboradores, '.', ',')"
|
|
|
Goto Forum:
Current Time: Mon Sep 09 22:24:21 CDT 2024
|