DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: SQL*LOADER Question

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586

    SQL*LOADER Question

    Hello. I have a table that I wanted to load a CSV file into

    1 CREATE TABLE "TMS_DEF_ACTIONS" ("DEF_ACTION_KEY"
    2 VARCHAR2(15) NOT NULL, "CREATION_TS" DATE NOT NULL,
    3 "CREATED_BY" VARCHAR2(30) NOT NULL, "ACTION_TYPE"
    4 VARCHAR2(15) NOT NULL, "DEFAULT_TEXT" VARCHAR2(2000),
    5 "MODIFICATION_TS" DATE, "MODIFIED_BY" VARCHAR2(30),
    6 CONSTRAINT "TMS_DEF_ACTIONS_PK" PRIMARY KEY("DEF_ACTION_KEY")

    and I wanted to load the following csv data that I extracted
    "CLARIFY ","21.04.2004 11:03:04","OPS$KOO ","C ","Please clarify this term. "," "," " "SPLIT ","21.04.2004 11:03:04","OPS$KOO ","N ","There is more than one medical concept. Please split for accurate coding. "

    I put this controlfile together
    LOAD DATA
    INFILE 'TMS_DEF_ACTIONS.lst'
    INTO TABLE TMS_DEF_ACTIONS
    FIELDS TERMINATED BY ","
    TRAILING NULLCOLS
    (

    DEF_ACTION_KEY CHAR NULLIF (DEF_ACTION_KEY=BLANKS)
    , CREATION_TS DATE "MM/DD/YY" NULLIF (CREATION_TS=BLANKS)
    , CREATED_BY CHAR NULLIF (CREATED_BY=BLANKS)
    , ACTION_TYPE CHAR NULLIF (ACTION_TYPE=BLANKS)
    , DEFAULT_TEXT CHAR NULLIF (DEFAULT_TEXT=BLANKS)
    , MODIFICATION_TS DATE "MM/DD/YY" NULLIF (MODIFICATION_TS=BLANKS)
    , MODIFIED_BY CHAR NULLIF (MODIFIED_BY=BLANKS)

    but i am gettign errors..

    QL*Loader: Release 9.2.0.6.0 - Production on Tue Jun 21 15:12:22 2005

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Control File: tms_def_actions.ctl
    Data File: TMS_DEF_ACTIONS.lst
    Bad File: TMS_DEF_ACTIONS.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 TMS_DEF_ACTIONS, loaded from every logical record.
    Insert option in effect for this table: INSERT
    TRAILING NULLCOLS option in effect

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    DEF_ACTION_KEY FIRST * , CHARACTER
    NULL if DEF_ACTION_KEY = BLANKS
    CREATION_TS NEXT * , DATE MM/DD/YY
    NULL if CREATION_TS = BLANKS
    CREATED_BY NEXT * , CHARACTER
    NULL if CREATED_BY = BLANKS
    ACTION_TYPE NEXT * , CHARACTER
    NULL if ACTION_TYPE = BLANKS
    DEFAULT_TEXT NEXT * , CHARACTER
    NULL if DEFAULT_TEXT = BLANKS
    MODIFICATION_TS NEXT * , DATE MM/DD/YY
    NULL if MODIFICATION_TS = BLANKS
    MODIFIED_BY NEXT * , CHARACTER
    NULL if MODIFIED_BY = BLANKS

    Record 1: Rejected - Error on table TMS_DEF_ACTIONS, column DEFAULT_TEXT.
    Field in data file exceeds maximum length
    Record 2: Rejected - Error on table TMS_DEF_ACTIONS, column DEF_ACTION_KEY.
    Field in data file exceeds maximum length
    Record 3: Rejected - Error on table TMS_DEF_ACTIONS, column DEF_ACTION_KEY.
    Field in data file exceeds maximum length
    Record 4: Rejected - Error on table TMS_DEF_ACTIONS, column DEFAULT_TEXT.
    Field in data file exceeds maximum length
    Record 5: Rejected - Error on table TMS_DEF_ACTIONS, column DEF_ACTION_KEY.
    Field in data file exceeds maximum length
    Record 6: Rejected - Error on table TMS_DEF_ACTIONS, column DEF_ACTION_KEY.
    Field in data file exceeds maximum length

    Table TMS_DEF_ACTIONS:
    0 Rows successfully loaded.
    6 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.


    any ideas?

    thanks
    "High Salaries = Happiness = Project Success."

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    I'm not surprised you've had no answers to this post. You really need to read and correct the post if you expect an answer.

    First, your table definition is bad as it has to be edited to run it. Post a real statement in future like:

    Code:
    CREATE TABLE "TMS_DEF_ACTIONS" (
      "DEF_ACTION_KEY" VARCHAR2(15) NOT NULL,
      "CREATION_TS" DATE NOT NULL,
      "CREATED_BY" VARCHAR2(30) NOT NULL,
      "ACTION_TYPE" VARCHAR2(15) NOT NULL,
      "DEFAULT_TEXT" VARCHAR2(2000),
      "MODIFICATION_TS" DATE,
      "MODIFIED_BY" VARCHAR2(30),
      CONSTRAINT "TMS_DEF_ACTIONS_PK" PRIMARY KEY("DEF_ACTION_KEY")
    );
    Next, the controlfile was missing a terminating ")" character. That's just sloppy.

    Next, the data was not displayed correctly as it looked like one big line, rather than two. I assume you meant to post this:

    "CLARIFY ","21.04.2004 11:03:04","OPS$KOO ","C ","Please clarify this term. "," "," "
    "SPLIT ","21.04.2004 11:03:04","OPS$KOO ","N ","There is more than one medical concept. Please split for accurate coding. "

    Finally, you did not include the command you used to run the sqlldr process. I used this:

    sqlldr userid=username/password@service control=c:\control.ctrl log=c:\sqlldr.log bad=c:\bad.log data=c:\TMS_DEF_ACTIONS.lst

    If you had done all this I could have run the example really quickly, but as you did not I ignored the post for some time.

    Anyway, the controlfile you posted had a couple of issues I could see.

    First. You did not tell SQL*Loader that the fields could be enclosed by " characters, which meant the date conversions failed.

    Second. The date formats listed did not match the data.

    I was able to load the data when I used this control file:

    Code:
    LOAD DATA
    INFILE 'TMS_DEF_ACTIONS.lst'
    INTO TABLE TMS_DEF_ACTIONS
    FIELDS TERMINATED BY ","
    OPTIONALLY ENCLOSED BY "\""
    TRAILING NULLCOLS
    (
    DEF_ACTION_KEY CHAR NULLIF (DEF_ACTION_KEY=BLANKS)
    , CREATION_TS DATE "DD.MM.YYYY HH24:MI:SS" NULLIF (CREATION_TS=BLANKS)
    , CREATED_BY CHAR NULLIF (CREATED_BY=BLANKS)
    , ACTION_TYPE CHAR NULLIF (ACTION_TYPE=BLANKS)
    , DEFAULT_TEXT CHAR NULLIF (DEFAULT_TEXT=BLANKS)
    , MODIFICATION_TS DATE "MM/DD/YY" NULLIF (MODIFICATION_TS=BLANKS)
    , MODIFIED_BY CHAR NULLIF (MODIFIED_BY=BLANKS)
    )
    Note, the table contains more columns that the file contains data. I assume this is by design.

    Cheers

    Tim...
    Last edited by TimHall; 06-22-2005 at 07:23 AM.
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    you are a very patient man Mr Hall

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    I am compared to you. I seem to remember you used to call Murdock a "Damn fool!" every week on TV!

    ;-)
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    call a spade a spade and all that

  6. #6
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Bu I call it a shovel
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  7. #7
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    Thankyou Tim, I appreciate your response. I initially was going to post the entire create table command but I thought i leave it out so i dont fill up the unneccesary space but the with the sql*loader, I havent had much experience so i do appreciate your guidance.

    I will go ahead and try the controlfile you gave me and give you feedback on it.

    thanks again.
    "High Salaries = Happiness = Project Success."

  8. #8
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191

    Wink PLSQL Developer

    or you can use the new feature on PLSQL Developer 6 (if you have it),"Text importer". It works like a dream importing from csv format....it generates the controlfile for you and you can edit it manually, however you need to create the table in your desired schema first.

    I recently used it to import info from a synonym (table) linked through db-link.

    I saved the output of a select * statement on the synonym to csv, and used the "new" table for some local testing on our application.

    Able was I ere I saw Elba

Posting Permissions

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


Click Here to Expand Forum to Full Width