6 Replies Latest reply: Feb 8, 2013 9:51 AM by Sid_ Z. RSS

    Sql Loader problem

    Sid_ Z.
      Hi
      Please assist as the data is not getting loadedinto table.

      The control file
      LOAD DATA
      APPEND
      INTO TABLE dw_mc_targets
      FIELDS TERMINATED BY ","
      TRAILING NULLCOLS
      (
      LOC_LOC_ID  ,
      YYYYWW     ,
      BC_TARGET  ,
      OIS_TARGET ,
      HOF_COM_TARGET,
      HOF_COM_STRETCH_TARGET
      )
      Data
      0450,201201,9014.95,2069.21,,,,,
      0450,201202,8000.77,2022.64,,,,,
      0450,201203,7155.62,2003.41,,,,,
      0450,201204,7437.33,2064.81,,,,,
      0450,201205,8282.49,2036.46,,,,,
      Error
      cat load_mc_targets.log
      
      SQL*Loader: Release 11.2.0.1.0 - Production on Fri Feb 8 12:29:03 2013
      
      Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
      
      Control File:   load_mc_targets.ctl
      Data File:      dw_mc_targets.csv
        Bad File:     dw_mc_targets.bad
        Discard File:  none specified
      
       (Allow all discards)
      
      Number to load: ALL
      Number to skip: 0
      Errors allowed: 50
      Bind array:     64 rows, maximum of 256000 bytes
      Continuation:    none specified
      Path used:      Conventional
      
      Table DW_MC_TARGETS, 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
      ------------------------------ ---------- ----- ---- ---- ---------------------
      LOC_LOC_ID                          FIRST     *   ,       CHARACTER
      YYYYWW                               NEXT     *   ,       CHARACTER
      BC_TARGET                            NEXT     *   ,       CHARACTER
      OIS_TARGET                           NEXT     *   ,       CHARACTER
      HOF_COM_TARGET                       NEXT     *   ,       CHARACTER
      HOF_COM_STRETCH_TARGET               NEXT     *   ,       CHARACTER
      
      SQL*Loader-528: OCI return status: error
        • 1. Re: Sql Loader problem
          971895
          Pls post the dw_mc_targets DDL script
          • 2. Re: Sql Loader problem
            Sid_ Z.
            create table DW_MC_TARGETS
            (
              LOC_LOC_ID             VARCHAR2(10),
              YYYYWW                 VARCHAR2(6),
              BC_TARGET              VARCHAR2(20),
              OIS_TARGET             VARCHAR2(20),
              HOF_COM_TARGET         VARCHAR2(20),
              HOF_COM_STRETCH_TARGET VARCHAR2(20)
            )
            tablespace DIM_TAB1
              pctfree 10
              pctused 40
              initrans 1
              maxtrans 255
              storage
              (
                initial 1M
                next 1M
                minextents 1
                maxextents 500
                pctincrease 0
              );
            • 3. Re: Sql Loader problem
              971895
              I tired it's wroking for me
              LOAD DATA
              INFILE *
              APPEND
              INTO TABLE DW_MC_TARGETS
              FIELDS TERMINATED BY ","
              TRAILING NULLCOLS
              (
              LOC_LOC_ID,
              YYYYWW,
              BC_TARGET,
              OIS_TARGET,
              HOF_COM_TARGET,
              HOF_COM_STRETCH_TARGET
              )
              BEGINDATA
              0450,201201,9014.95,2069.21,,,,,
              0450,201202,8000.77,2022.64,,,,,
              0450,201203,7155.62,2003.41,,,,,
              0450,201204,7437.33,2064.81,,,,,
              0450,201205,8282.49,2036.46,,,,,
              • 4. Re: Sql Loader problem
                971895
                may be

                Check the database alert log. Direct path insert writes above the current high water mark for the table, so there may be an issue when it tires to allocate further extents to the table.
                • 5. Re: Sql Loader problem
                  Etbin
                  Guessing:
                  Although it's working for someone (might not be the same version as yours)
                  Eight commas in your data make room for nine columns (trailing nullcols specified) but you're defining only six columns (and there's no IGNORE IN-EXCESS DATA keyword available)

                  Regards

                  Etbin
                  • 6. Re: Sql Loader problem
                    Sid_ Z.
                    Hi,

                    It was High water Mark issue with this table...

                    Now it has done successfully means data has been loaded successfully.