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
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 #447488 is a reply to message #447476] Mon, 15 March 2010 07:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The 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
Re: SQLLDR decimal field problem [message #447491 is a reply to message #447488] Mon, 15 March 2010 07:54 Go to previous messageGo to next message
kari0ca
Messages: 3
Registered: March 2010
Junior Member
Michel Cadot wrote on Mon, 15 March 2010 07:46
The 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 #447493 is a reply to message #447491] Mon, 15 March 2010 07:59 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is your database version, anyway?

As I've already tested here, it seems that I managed to load data you provided with no errors.
Re: SQLLDR decimal field problem [message #447496 is a reply to message #447491] Mon, 15 March 2010 08:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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 messageGo to next message
kari0ca
Messages: 3
Registered: March 2010
Junior Member
Littlefoot wrote on Mon, 15 March 2010 07:59
What 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 #447500 is a reply to message #447497] Mon, 15 March 2010 08:40 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
But, this is the way you chose to represent decimal numbers - Michel told you so (but I'm not sure you understood).
SQL> select VAL_MD_SATISF_CLIENTES_EXT from bsc_l_transversal;

                      87.5

SQL> alter session set nls_numeric_characters = ',.';

Session altered.

SQL> select VAL_MD_SATISF_CLIENTES_EXT from bsc_l_transversal;

                      87,5

SQL>
Re: SQLLDR decimal field problem [message #447619 is a reply to message #447497] Tue, 16 March 2010 13:22 Go to previous message
Barbara Boehmer
Messages: 8625
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, '.', ',')"
Previous Topic: using decode in SQL*Loader
Next Topic: What is the parameters of exp and imp command?
Goto Forum:
  


Current Time: Sun Dec 04 08:24:56 CST 2016

Total time taken to generate the page: 0.09418 seconds