OraFAQ Forum: Server Utilities » SQLLDR decimal field problem

Home » RDBMS Server » Server Utilities » SQLLDR decimal field problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQLLDR decimal field problem [message #447476]

Mon, 15 March 2010 07:10

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

Report message to a moderator

Re: SQLLDR decimal field problem [message #447488 is a reply to message #447476]

Mon, 15 March 2010 07:46

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SQLLDR decimal field problem [message #447491 is a reply to message #447488]

Mon, 15 March 2010 07:54

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

Report message to a moderator

Re: SQLLDR decimal field problem [message #447493 is a reply to message #447491]

Mon, 15 March 2010 07:59

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: SQLLDR decimal field problem [message #447496 is a reply to message #447491]

Mon, 15 March 2010 08:09

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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

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)
)

Report message to a moderator

Re: SQLLDR decimal field problem [message #447500 is a reply to message #447497]

Mon, 15 March 2010 08:40

Littlefoot
Messages: 21823
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>

Report message to a moderator

Re: SQLLDR decimal field problem [message #447619 is a reply to message #447497]

Tue, 16 March 2010 13:22

Barbara Boehmer
Messages: 9101
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, '.', ',')"

Report message to a moderator

Previous Topic:	using decode in SQL*Loader
Next Topic:	What is the parameters of exp and imp command?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 14:20:49 CST 2024