This discussion is archived
6 Replies Latest reply: Feb 8, 2013 7:51 AM by Sid_ Z. RSS

Sql Loader problem

Sid_ Z. Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    Pls post the dw_mc_targets DDL script
  • 2. Re: Sql Loader problem
    Sid_ Z. Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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. Newbie
    Currently Being Moderated
    Hi,

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

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points