Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Sqlldr Problem

  1. #1
    Join Date
    Dec 2009
    Posts
    15

    Unanswered: Sqlldr Problem

    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:
    Code:
    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):
    Code:
    0,3,7,17,20091231,87.5,,,,,,,,,,,,,,,,,,,,,,,,,20100315

    and this is the definition of the table:
    Code:
      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:
    Code:
    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

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    do u really have to use sqlldr ? (loader)

    oraloader - Google Search

  3. #3
    Join Date
    Aug 2009
    Posts
    262

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    or download the software .. and send me ur email .. i will mail the crack

  5. #5
    Join Date
    Dec 2009
    Posts
    15
    Well, i never used the oraloader.
    I want to run the sqlldr in a process that is called automaticaly on a vbscript... and i have it working with the sqlldr, i think that change to oraloader will consume more time and effort that simply solve this problem, but thanx for the tip, if i cant solve this with sqlldr i will use oraloader

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    which OS you are using . ? if that is windows then u can schedule simply for timed job.

    if its an automated process then i suggest go to asktom.com and ask ur question there.

    if it is for just resolving your work need , do the job with oraloader and start studying /researching .

    i can not possibly create a test for ur situation by looking at the results .

    but yes if you can give me couple of lines of your data , i can produce you the control files and relevant .

  7. #7
    Join Date
    Dec 2009
    Posts
    15
    Quote Originally Posted by mishaalsy View Post
    or download the software .. and send me ur email .. i will mail the crack
    mishaalsy, i can't use cracked software, i'm working on a project that the client provide the software, and all the software must be legal... thanks anyway for the tip of oraloader

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I recreated an environment you (partially) provided. I had to create additional sequence and GET_DATA function. Once I did that, SQL*Loader successfully loaded data into the table.

    Therefore, I guess that it was GET_DATA function that returned an invalid number error. What does it do? My "dummy" function was this (i.e. I returned what I got)
    Code:
    create or replace function get_data (par_dat in number) 
      return number is
    begin
      return (par_dat);
    end;

  9. #9
    Join Date
    Aug 2009
    Posts
    262
    *apulse ... *

    great work ..

  10. #10
    Join Date
    Dec 2009
    Posts
    15
    Quote Originally Posted by mishaalsy View Post
    which OS you are using . ? if that is windows then u can schedule simply for timed job.
    I'm using windows, but the loader can only start when all the process finish. and it sould load only the data that was delivered (all that is controled by the vbs script)

    Quote Originally Posted by mishaalsy View Post
    if its an automated process then i suggest go to asktom.com and ask ur question there.
    The problem isn't on the automated process, if i try to load the bad file (the line rejected) the error appears.

    Quote Originally Posted by mishaalsy View Post
    if it is for just resolving your work need , do the job with oraloader and start studying /researching .
    I can't use the oraloader because the client doesn't have the oraloader registered.

    Quote Originally Posted by mishaalsy View Post
    i can not possibly create a test for ur situation by looking at the results .

    but yes if you can give me couple of lines of your data , i can produce you the control files and relevant .
    Thanks for the help, send me your e-mail on PM and i send to you some examples of data.

  11. #11
    Join Date
    Dec 2009
    Posts
    15
    Quote Originally Posted by Littlefoot View Post
    I recreated an environment you (partially) provided. I had to create additional sequence and GET_DATA function. Once I did that, SQL*Loader successfully loaded data into the table.

    Therefore, I guess that it was GET_DATA function that returned an invalid number error. What does it do? My "dummy" function was this (i.e. I returned what I got)
    Code:
    create or replace function get_data (par_dat in number) 
      return number is
    begin
      return (par_dat);
    end;
    Hi Littlefoot, the GET_DATA function only get's the date value and returns de key for the date... and as i reported on error message, the error was
    Code:
    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
    translating: 
    the value used for ROWS parameter changed from 64 to 32
    Record 1: Rejected - Error on table BSC_L_TRANSVERSAL, column VAL_MD_SATISF_CLIENTES_EXT.
    ORA-01722: invalid number
    so, i assume that the problem wasn't on the function get_data. This process was working with all the fields integer, the problems became when i changed the field VAL_MD_SATISF_CLIENTES_EXT to decimal.

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Then your test case was invalid.

    A table:
    Code:
    SQL> desc bsc_l_transversal;
     Name                                      Null?    Type
     ----------------------------------------- -------- -------------------
     ID_TRANSVERSAL                            NOT NULL NUMBER(8)
     OE                                        NOT NULL NUMBER(8)
     OO                                        NOT NULL NUMBER(8)
     DIRECCAO                                  NOT NULL NUMBER(8)
     DATA                                      NOT NULL NUMBER(8)
     VAL_MD_SATISF_CLIENTES_EXT                         NUMBER(5,2)
     VAL_MD_SATISF_COLABORADORES                        NUMBER(5,2)
     N_NC_AUDIT_PROCS_PR                                NUMBER(5)
     N_TT_NC_PROCS_ANO                                  NUMBER(5)
     N_ACCOES_MELH_TRAT_PR                              NUMBER(5)
     N_TT_ACCOES_MELHORIA                               NUMBER(5)
     N_RECLAM_ANAL_TRAT_PR                              NUMBER(5)
     N_TT_RECLAM                                        NUMBER(5)
     N_NOVOS_PROC_CERT                                  NUMBER(5)
     TX_REALIZACAO_PROG_EVOLVERE                        NUMBER(5)
     N_PROJ_EVOLVERE_SEM_DESV                           NUMBER(5)
     N_TT_PROJ_EVOLVERE                                 NUMBER(5)
     N_ACCOES_COL_INST_DES                              NUMBER(5)
     N_EVT_SAT_INT_B_MB_REL_ORG                         NUMBER(5)
     N_EVT_SAT_EXT_B_MB_REL_ORGAN                       NUMBER(5)
     N_EVT_SAT_EXT_B_MB_REL_CONT                        NUMBER(5)
     N_TT_EVENTOS                                       NUMBER(5)
     VAL_RECEITA_COBRADA                                NUMBER(8)
     VAL_RECEITA_LIQUIDADA                              NUMBER(8)
     VAL_DESP_PAGA                                      NUMBER(8)
     VAL_DESP_COMPROMETIDA                              NUMBER(8)
     N_SAIDAS                                           NUMBER(5)
     N_COLABORADORES                                    NUMBER(5)
     N_PEDIDOS_NOTIF_SUP_ELEC                           NUMBER(5)
     N_PEDIDOS                                          NUMBER(5)
     DATA_CTRL                                 NOT NULL NUMBER(8)
    
    SQL>
    Control file (includes sample data):
    Code:
    LOAD DATA
    infile *
    replace
    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)
    )
    
    begindata
    0,3,7,17,20091231,87.5,,,,,,,,,,,,,,,,,,,,,,,,,20100315
    Loading session:
    Code:
    C:\TEMP>sqlldr scott/tiger@ora10 control=test2.ctl log=test2.log
    
    SQL*Loader: Release 10.2.0.1.0 - Production on Pon O×u 15 12:46:19 2010
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    Commit point reached - logical record count 1
    
    C:\TEMP>
    Result:
    Code:
    SQL> set head off
    SQL> select * from bsc_l_transversal;
    
                21          3          7         17   20091231
                          87.5
    
    
    
    
    
    
                                                          20100315
    
    
    SQL>
    So, as you can see, everything is fine on my Oracle 10g 10.2.0.1 database. Which part of the information did you not provide correctly?

  13. #13
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    the problems became when i changed the field VAL_MD_SATISF_CLIENTES_EXT to decimal.
    Decimal? This is not a valid datatype, is it? How did you do that, anyway?

  14. #14
    Join Date
    Dec 2009
    Posts
    15
    Quote Originally Posted by Littlefoot View Post
    Decimal? This is not a valid datatype, is it? How did you do that, anyway?
    Decimal was an expression,
    the columns definition was Number(5,0) and i changed it to Number (5,2)
    on the CTL i changed the definition from INTEGER EXTERNAL to DECIMAL EXTERNAL.

  15. #15
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As far as I can see, "87.5" loaded successfully.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •