Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55

    Unanswered: SQL Loader problem

    Hi,

    i've got a csv file and i want to load it with SQL Loader .

    I load the first line of my file, but not the other.
    I tried to delete the first line, and so begin since the second one. He load the new first line, but not the others.....

    PHP Code:
    OPTIONS (SILENT=ALLDIRECT=true)
    LOAD DATA

    APPEND
    -----------------------------------------------
    -- 
    CHARGEMENT Table dqaqt22                  --
    -----------------------------------------------
    into table dqaqt22
    -- REENABLE DISABLED_CONSTRAINTS
    FIELDS TERMINATED BY 
    ";"
    TRAILING NULLCOLS
    NO_DOSSIER_DDE_CREDIT                    POSITION (1CHAR,
      
    VIN                                      CHAR,
      
    DATE_INTERVENTION                        DATE "DD/MM/YY HH24:MI:SS",
      
    CODE_CAUSE                               CHAR,
      
    CODE_PRESTATAIRE_INTERVENTION            CHAR,
      
    CODE_PRESTATAIRE_LOCAL                   CHAR,
      
    CODE_PERSONNEL_PSA_VALIDEUR              CHAR,
      
    CODE_PANNE                               CHAR,
      
    CODE_IMPUTATION_GARANTIE                 CHAR,
      
    CODE_DEFAUT_GARANTIE                     CHAR,
      
    CODE_FONCTION_PSA                        CHAR,
      
    NO_DOSSIER_INCIDENT                      CHAR,
      
    NO_DEMANDE_TRAVAUX                       CHAR,
      
    STATUT                                   CHAR,
      
    DDE_HISTORISEE_LOC                       CHAR,
      
    INDICATEUR_ECHANGE_PIECE                 CHAR,
      
    INDICATEUR_COMTEC                        CHAR,
      
    INDICATEUR_TYPE_DEMANDE                  CHAR,
      
    INDICATEUR_ACCEPTATION                   CHAR,
      
    INDICATEUR_VALORISATION                  CHAR,
      
    INDICATEUR_CREATION_FLUX_VALO            CHAR,
      
    INDICATEUR_REVELATEUR_PANNE              CHAR,
      
    COUT_PR_PRINCIPAL                        FLOAT EXTERNAL,
      
    COUT_PR_TPP                              FLOAT EXTERNAL,
      
    COUT_PR_COMPLEMENTAIRE                   FLOAT EXTERNAL,
      
    SOMME_TPS_MO                             FLOAT EXTERNAL,
      
    COUT_GLOBAL_MO                           FLOAT EXTERNAL,
      
    COUT_GLOBAL_AUTRES_DEPENSES              FLOAT EXTERNAL,
      
    COUT_TOTAL                               FLOAT EXTERNAL,
      
    FRAIS_APPROCHE                           FLOAT EXTERNAL,
      
    FRAIS_GESTION                            FLOAT EXTERNAL,
      
    KILOMETRAGE_VEHICULE                     INTEGER EXTERNAL,
      
    COMMENTAIRE_CONSTAT_CLIENT               CHAR,
      
    COMMENTAIRE_DIAGNOSTIC_ATELIER           CHAR,
      
    COMMENTAIRE_REMEDE                       CHAR,
      
    NB_MOIS_ROULAGE_INTERVENTION             INTEGER EXTERNAL,
      
    NB_DDE_CREDIT                            INTEGER EXTERNAL,
      
    DATE_MAJ                                 DATE "DD/MM/YY HH24:MI:SS"

    Anyone can help me ???

    Here is a piece af my data file :
    Code:
    BACF55001;935CHRHYP3J512644;01/10/2003 00:00:00;C0717;029116Y01;003756B01;;0;C000;550;1D11;SO;00180033;3;CH;N;;C;A;O;N;N;0;0;0;,4;5,45;0;5,45;0;0;1430;;;;1;1;29/10/2003 00:31:27;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
    BACF55101;935CHRHYP3J512644;01/10/2003 00:00:00;C6030;029116Y01;003756B01;;0;C000;320;2K3C;SO;00180034;3;CH;N;;C;A;O;N;N;0;0;0;,3;4,09;0;4,09;0;0;1430;;;;1;1;29/10/2003 00:31:27;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
    BACF55201;935CHRHYP3J512644;01/10/2003 00:00:00;C5455;029116Y01;003756B01;;0;C000;441;3Z7B;SO;00180035;3;CH;N;;C;A;O;N;N;0;0;0;,4;5,45;0;5,45;0;0;1430;;;;1;1;29/10/2003 00:31:27;

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    What does your log file tell you ???

  3. #3
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    Nothing special....
    It just sais that there are errors in the data file.

    I loaded my data file successfully by changing direct mode (direct=false).
    But it seems to take more time....
    I don't know how to perform my code...

  4. #4
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    I modified any lines :
    Here is mu sql query :

    PHP Code:
    spool /users12/dqa00/tmp/extract.dat;
    set pages 0;
    set lines 600;
    set timing on;
    select qt22.NO_DOSSIER_DDE_CREDIT || ';' || 
           
    qt22.VIN || ';' || 
           
    TO_DATE(qt22.DATE_INTERVENTION'DD/MM/YYYY HH24:MI:SS') || ';' || 
           
    qt22.CODE_CAUSE || ';' || 
           
    qt22.CODE_PRESTATAIRE_INTERVENTION || ';' || 
           
    qt22.CODE_PRESTATAIRE_LOCAL || ';' || 
           
    qt22.CODE_PERSONNEL_PSA_VALIDEUR || ';' || 
           
    qt22.CODE_PANNE || ';' || 
           
    qt22.CODE_IMPUTATION_GARANTIE || ';' || 
           
    qt22.CODE_DEFAUT_GARANTIE || ';' || 
           
    qt22.CODE_FONCTION_PSA || ';' || 
           
    qt22.NO_DOSSIER_INCIDENT || ';' || 
           
    qt22.NO_DEMANDE_TRAVAUX || ';' || 
           
    qt22.STATUT || ';' || 
           
    qt22.DDE_HISTORISEE_LOC || ';' || 
           
    qt22.INDICATEUR_ECHANGE_PIECE || ';' || 
           
    qt22.INDICATEUR_COMTEC || ';' || 
           
    qt22.INDICATEUR_TYPE_DEMANDE || ';' || 
           
    qt22.INDICATEUR_ACCEPTATION || ';' || 
           
    qt22.INDICATEUR_VALORISATION || ';' || 
           
    qt22.INDICATEUR_CREATION_FLUX_VALO || ';' || 
           
    qt22.INDICATEUR_REVELATEUR_PANNE || ';' || 
           
    qt22.COUT_PR_PRINCIPAL || ';' || 
           
    qt22.COUT_PR_TPP || ';' || 
           
    qt22.COUT_PR_COMPLEMENTAIRE || ';' || 
           
    qt22.SOMME_TPS_MO || ';' || 
           
    qt22.COUT_GLOBAL_MO || ';' || 
           
    qt22.COUT_GLOBAL_AUTRES_DEPENSES || ';' || 
           
    qt22.COUT_TOTAL || ';' || 
           
    qt22.FRAIS_APPROCHE || ';' || 
           
    qt22.FRAIS_GESTION || ';' || 
           
    qt22.KILOMETRAGE_VEHICULE || ';' || 
           
    qt22.COMMENTAIRE_CONSTAT_CLIENT || ';' || 
           
    qt22.COMMENTAIRE_DIAGNOSTIC_ATELIER || ';' || 
           
    qt22.COMMENTAIRE_REMEDE || ';' || 
           
    qt22.NB_MOIS_ROULAGE_INTERVENTION || ';' || 
           
    qt22.NB_DDE_CREDIT || ';' || 
           
    TO_DATE(qt22.DATE_MAJ'DD/MM/YYYY HH24:MI:SS') || ';'
      
    from dqaqt22 qt22;
    spool off;
    exit; 
    And so, my loader :

    PHP Code:
    OPTIONS (SILENT=ALLDIRECT=TRUE)
    LOAD DATA

    APPEND
    -----------------------------------------------
    -- 
    CHARGEMENT Table dqaqt22                  --
    -----------------------------------------------
    into table dqaqt22
    -- REENABLE DISABLED_CONSTRAINTS
    FIELDS TERMINATED BY 
    ";"
    TRAILING NULLCOLS
    NO_DOSSIER_DDE_CREDIT                    POSITION (1CHAR,
      
    VIN                                      CHAR,
      
    DATE_INTERVENTION                        DATE "DD/MM/YYYY HH24:MI:SS",
      
    CODE_CAUSE                               CHAR,
      
    CODE_PRESTATAIRE_INTERVENTION            CHAR,
      
    CODE_PRESTATAIRE_LOCAL                   CHAR,
      
    CODE_PERSONNEL_PSA_VALIDEUR              CHAR,
      
    CODE_PANNE                               CHAR,
      
    CODE_IMPUTATION_GARANTIE                 CHAR,
      
    CODE_DEFAUT_GARANTIE                     CHAR,
      
    CODE_FONCTION_PSA                        CHAR,
      
    NO_DOSSIER_INCIDENT                      CHAR,
      
    NO_DEMANDE_TRAVAUX                       CHAR,
      
    STATUT                                   CHAR,
      
    DDE_HISTORISEE_LOC                       CHAR,
      
    INDICATEUR_ECHANGE_PIECE                 CHAR,
      
    INDICATEUR_COMTEC                        CHAR,
      
    INDICATEUR_TYPE_DEMANDE                  CHAR,
      
    INDICATEUR_ACCEPTATION                   CHAR,
      
    INDICATEUR_VALORISATION                  CHAR,
      
    INDICATEUR_CREATION_FLUX_VALO            CHAR,
      
    INDICATEUR_REVELATEUR_PANNE              CHAR,
      
    COUT_PR_PRINCIPAL                        FLOAT EXTERNAL,
      
    COUT_PR_TPP                              FLOAT EXTERNAL,
      
    COUT_PR_COMPLEMENTAIRE                   FLOAT EXTERNAL,
      
    SOMME_TPS_MO                             FLOAT EXTERNAL,
      
    COUT_GLOBAL_MO                           FLOAT EXTERNAL,
      
    COUT_GLOBAL_AUTRES_DEPENSES              FLOAT EXTERNAL,
      
    COUT_TOTAL                               FLOAT EXTERNAL,
      
    FRAIS_APPROCHE                           FLOAT EXTERNAL,
      
    FRAIS_GESTION                            FLOAT EXTERNAL,
      
    KILOMETRAGE_VEHICULE                     INTEGER EXTERNAL,
      
    COMMENTAIRE_CONSTAT_CLIENT               CHAR,
      
    COMMENTAIRE_DIAGNOSTIC_ATELIER           CHAR,
      
    COMMENTAIRE_REMEDE                       CHAR,
      
    NB_MOIS_ROULAGE_INTERVENTION             INTEGER EXTERNAL,
      
    NB_DDE_CREDIT                            INTEGER EXTERNAL,
      
    DATE_MAJ                                 DATE "DD/MM/YYYY HH24:MI:SS"

    I disabled any constraint on my table.
    But it doesn't work.

    Here is a part of my log file :

    Code:
    SQL*Loader: Release 8.1.7.1.0 - Production on Fri Apr 2 10:15:55 2004
    
    (c) Copyright 2000 Oracle Corporation.  All rights reserved.
    
    Control File:   dqalo22.ctl
    Data File:      extract_dqaqt22.dat
      Bad File:     dqalo22.bad
      Discard File: extract_dqaqt22.discard
     (Allow all discards)
    
    Number to load: ALL
    Number to skip: 0
    Errors allowed: 50
    Continuation:    none specified
    Path used:      Direct
    Silent options: FEEDBACK, ERRORS and DISCARDS
    
    Table DQAQT22, loaded from every logical record.
    Insert option in effect for this table: APPEND
    TRAILING NULLCOLS option in effect
    
       Column Name                  Position   Len  Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    NO_DOSSIER_DDE_CREDIT                   1     *   ;       CHARACTER
    VIN                                  NEXT     *   ;       CHARACTER
    DATE_INTERVENTION                    NEXT     *   ;       DATE DD/MM/YYYY HH24:MI:SS
    CODE_CAUSE                           NEXT     *   ;       CHARACTER
    CODE_PRESTATAIRE_INTERVENTION        NEXT     *   ;       CHARACTER
    CODE_PRESTATAIRE_LOCAL               NEXT     *   ;       CHARACTER
    CODE_PERSONNEL_PSA_VALIDEUR          NEXT     *   ;       CHARACTER
    CODE_PANNE                           NEXT     *   ;       CHARACTER
    CODE_IMPUTATION_GARANTIE             NEXT     *   ;       CHARACTER
    CODE_DEFAUT_GARANTIE                 NEXT     *   ;       CHARACTER
    CODE_FONCTION_PSA                    NEXT     *   ;       CHARACTER
    NO_DOSSIER_INCIDENT                  NEXT     *   ;       CHARACTER
    NO_DEMANDE_TRAVAUX                   NEXT     *   ;       CHARACTER
    STATUT                               NEXT     *   ;       CHARACTER
    DDE_HISTORISEE_LOC                   NEXT     *   ;       CHARACTER
    INDICATEUR_ECHANGE_PIECE             NEXT     *   ;       CHARACTER
    INDICATEUR_COMTEC                    NEXT     *   ;       CHARACTER
    INDICATEUR_TYPE_DEMANDE              NEXT     *   ;       CHARACTER
    INDICATEUR_ACCEPTATION               NEXT     *   ;       CHARACTER
    INDICATEUR_VALORISATION              NEXT     *   ;       CHARACTER
    INDICATEUR_CREATION_FLUX_VALO        NEXT     *   ;       CHARACTER
    INDICATEUR_REVELATEUR_PANNE          NEXT     *   ;       CHARACTER
    COUT_PR_PRINCIPAL                    NEXT     *   ;       CHARACTER
    COUT_PR_TPP                          NEXT     *   ;       CHARACTER
    COUT_PR_COMPLEMENTAIRE               NEXT     *   ;       CHARACTER
    SOMME_TPS_MO                         NEXT     *   ;       CHARACTER
    COUT_GLOBAL_MO                       NEXT     *   ;       CHARACTER
    COUT_GLOBAL_AUTRES_DEPENSES          NEXT     *   ;       CHARACTER
    COUT_TOTAL                           NEXT     *   ;       CHARACTER
    FRAIS_APPROCHE                       NEXT     *   ;       CHARACTER
    FRAIS_GESTION                        NEXT     *   ;       CHARACTER
    KILOMETRAGE_VEHICULE                 NEXT     *   ;       CHARACTER
    COMMENTAIRE_CONSTAT_CLIENT           NEXT     *   ;       CHARACTER
    COMMENTAIRE_DIAGNOSTIC_ATELIER       NEXT     *   ;       CHARACTER
    COMMENTAIRE_REMEDE                   NEXT     *   ;       CHARACTER
    NB_MOIS_ROULAGE_INTERVENTION         NEXT     *   ;       CHARACTER
    NB_DDE_CREDIT                        NEXT     *   ;       CHARACTER
    DATE_MAJ                             NEXT     *   ;       DATE DD/MM/YYYY HH24:MI:SS
    
    
    Index(es) suivant(s) sur la table DQAQT22 traite(s) :
    index DQA.DQAQX22A charge correctement avec 24 cles
    index DQA.DQAQX22C charge correctement avec 24 cles
    index DQA.DQAQX22E charge correctement avec 24 cles
    index DQA.DQAQX22I charge correctement avec 24 cles
    index DQA.DQAQX22J charge correctement avec 24 cles
    index DQA.DQAQX22H charge correctement avec 24 cles
    index DQA.DQAQX22D charge correctement avec 24 cles
    index DQA.DQAQX22B charge correctement avec 24 cles
    
    MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
    
    Table DQAQT22:
      24 Rows successfully loaded.
      51 Rows not loaded due to data errors.
      0 Rows not loaded because all WHEN clauses were failed.
      0 Rows not loaded because all fields were null.
    
    Bind array size not used in direct path.
    Space allocated for memory besides bind array:        0 bytes
    
    Total logical records skipped:          0
    Total logical records read:            75
    Total logical records rejected:        51
    Total logical records discarded:        0
    
    Run began on Fri Apr 02 10:15:55 2004
    Run ended on Fri Apr 02 10:15:57 2004
    
    Elapsed time was:     00:00:01.82
    CPU time was:         00:00:00.10
    I don't understand why it doesn't work. My data file is well organized and i extract data directly from the same table i would to load them.
    I truncated my table, in order to have no 'UNIQUE CONSTRAINT' errors, but i still got a problem...

    Anyone can help me ?

  5. #5
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    It loads only lines wher all fields are not null.
    I thought that TRAILING NULLCOLS permit null values....
    But it doesn't work !!!

  6. #6
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    Two possibilities, but i think taht the second one is the right.

    - the loader doesn't want to load null fiels, but in the 24 rows loaded, one field is null.
    - the loader doesn't want to load float... I've got fields like this : ;,456;
    And this fileds are not loaded.
    The rows it loads contains only 0 in the same fields....

    My database is an Oracle 8.17

  7. #7
    Join Date
    Jan 2004
    Posts
    370
    What is returned from:

    select nls_numeric_characters from nls_session_parameters;

  8. #8
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    Originally posted by SkyWriter
    What is returned from:

    select nls_numeric_characters from nls_session_parameters;
    ???
    I don't have this column :

    Code:
    select nls_numeric_characters from nls_session_parameters
           *
    ERREUR a la ligne 1 :
    ORA-00904: Nom de colonne non valide
    
    
    SQL> desc nls_session_parameters
     Nom                                       NULL ?   Type
     ----------------------------------------- -------- ----------------------------
     PARAMETER                                          VARCHAR2(30)
     VALUE                                              VARCHAR2(40)

  9. #9
    Join Date
    Jan 2004
    Posts
    370
    Oops

    select * from nls_session_parameters;

    What is nls_numeric_characters?

  10. #10
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    Originally posted by SkyWriter
    Oops

    select * from nls_session_parameters;

    What is nls_numeric_characters?
    PHP Code:
    NLS_NUMERIC_CHARACTERS         ,. 
    There's no reason i've got a rpoblem with my loader...
    Last edited by venderic; 04-02-04 at 09:35.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    try it without the float external stuff. or the external stuff.

    also, what is with the comma in your data
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  12. #12
    Join Date
    Jan 2004
    Posts
    370
    Conventional path load works.
    Direct path load doesn't.

    Data conversion is done on the client, not the server, when using direct path load.

    You are using a comma as a decimal character.

    What are your nls settings in the database?
    Could it be the columns are expecting a "." instead of "," as a decimal character?

  13. #13
    Join Date
    Jan 2004
    Location
    Sochaux (France)
    Posts
    55
    You are using a comma as a decimal character
    yes, it's basic in France...

    What are your nls settings in the database?
    i answered it just before : ".," for the NLS_NUMERIC_CHARACTERS

    Data conversion is done on the client, not the server, when using direct path load.
    That's what i want.

    According to me, all is right... I don't see why it doesn't work...

    I resolved my problem by replacing "," by "." in my data file and so it works without any problem.
    BUt it's not the real solution.

    If you think about something else, let's post it here.

    Very thx for your help and time spent on my problem.

Posting Permissions

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