SQL*LOADER from a csv field into CLOB column
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL*LOADER from a csv field into CLOB column

Hybrid View

  1. #1
    Join Date
    Jun 2000
    Location
    Bethel CT, USA
    Posts
    18

    SQL*LOADER from a csv field into CLOB column

    Loading from CSV file into CLOB

    Can someone point out what is wrong? I looked in all the other examples and this should be OK...or is it? The CLOB field is not being recognized a one input field?

    -Thanks in advance...
    egavish@snet.net

    ----------------My table to load:
    ARTICLE_ID NUMBER
    ARTICLEDATE VARCHAR2(15)
    ARTICLETITLE VARCHAR2(255)
    SHOWARTICLE VARCHAR2(10)
    ARTICLEBODY CLOB

    --------------My command line:
    sqlldr userid=scott/tiger@ora9 control=load_articles.ctrl log=load_articles.log readsize=10240000

    ---------------My control file:
    load data
    INFILE 'articles.TXT'
    REPLACE
    INTO TABLE articles
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
    (ARTICLE_ID integer external,
    ArticleDate char,
    ArticleTitle char,
    SHOWArticle char,
    ARTICLEBODY char(40000) ENCLOSED BY '' AND '' )



    -------my CSV file with one record(clob field has line breaks):

    1,"06/17/1999","Ford Going To Bahia","TRUE","Ford Motor Co. said it will build a new factory in Bahia, Brazil.
    The $1.3 billion plant will employ 5,000 workers and is scheduled to open in 2001.
    For more information
    onclick=""OpenNavWin('http://dowjones.wsj.com/i/auto/SB929558916336243030-d-industry-c1-auto.html','_blank')"">click here.
    "


    --------My Errors in log file:


    SQL*Loader: Release 9.2.0.1.0 - Production on Tue Mar 18 02:31:51 2003

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

    Control File: load_articles.ctrl
    Data File: articles.TXT
    Bad File: articles.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 ARTICLES, loaded from every logical record.
    Insert option in effect for this table: REPLACE
    TRAILING NULLCOLS option in effect

    Column Name Position Len Term Encl Datatype
    ------------------------------ ---------- ----- ---- ---- ---------------------
    ARTICLE_ID FIRST * , O(") CHARACTER
    ARTICLEDATE NEXT * , O(") CHARACTER
    ARTICLETITLE NEXT * , O(") CHARACTER
    SHOWARTICLE NEXT * , O(") CHARACTER
    ARTICLEBODY NEXT 40000 , CHARACTER
    First enclosure string : ''
    Second enclosure string : ''

    Record 1: Rejected - Error on table ARTICLES, column ARTICLEBODY.
    second enclosure string not present
    Record 4: Rejected - Error on table ARTICLES, column ARTICLE_ID.
    second enclosure string not present
    Record 2: Rejected - Error on table ARTICLES, column ARTICLE_ID.
    ORA-01722: invalid number
    Record 3: Rejected - Error on table ARTICLES, column ARTICLE_ID.
    ORA-01722: invalid number

  2. #2
    Join Date
    Mar 2003
    Location
    Oslo, Norway
    Posts
    1
    SQLLDR will not be able to interpret the data for the CLOB column, because the text you try to load into it contains both ',' and '"'.
    If possible, use another delimiter between the columns, or enclose text columns with another character (make sure that this character will not be present within the text itself...)

    -v

  3. #3
    Join Date
    Jun 2000
    Location
    Bethel CT, USA
    Posts
    18
    I thought the 'ENCLOSED BY <...> AND <...>' set up the bounderies for the CLOB field? but it doesnt seem to matter.

    NOTE: (somehow the "ENCLOSED BY and " did not display correctly in the above message. the 'single quotes display empty. But they are not)
    Last edited by egavish; 03-18-2003 at 08:09 AM.

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