SQL*LOADER issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: SQL*LOADER issue

  1. #1
    Join Date
    Sep 2003
    Posts
    49

    SQL*LOADER issue

    hi
    iam new to SQL*LOADER.
    I got FLAT file (text data) from powerhouse and not able to load into oracle .
    my ctl look like this.


    OPTIONS (BINDSIZE=1024000)
    LOAD DATA
    INFILE *
    INTO TABLE air_miles
    REPLACE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    WORK_ORDER_NO
    ,AIRMILES_SEQ_NO
    ,AIRMILES_CARD_NO
    ,AIRMILES_OFFER_CD
    ,AIRMILES_AMOUNT
    ,AIRMILES_COUNT
    ,TRAN_DATE
    ,AIRMILES_LOCN_CODE
    ,POSTED_FLAG
    ,POSTING_DATE
    ,AIRMILES_ERROR_FLAG
    ,AIRMILES_LOAD_DATE
    ,CREDIT_FLAG
    )
    BeginData
    275636, 63,8006 998 0100,STANDARD , 1225.80, 61,2000/08/29,PTVN,Y,2001/02/14, , ,Y
    275646, 28,8006 998 0100,STANDARD , 141.53, 7,1998/03/24,PTVN,Y,1998/03/25, , ,Y
    275648, 42,8170 563 8750,STANDARD , 30.00, 1,1998/04/07,PTVN,Y,1998/09/04, , ,Y



    I got linefeed in the flat file.How can i get rid of line feed ?

  2. #2
    Join Date
    Oct 2003
    Posts
    38
    Try this -
    OPTIONS (BINDSIZE=1024000)
    LOAD DATA
    INFILE * -----> specify the textfile along with path
    INTO TABLE air_miles
    REPLACE
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    (
    Col1 number(10) nullif col1=blanks,---->specify the datatypes for all columns along with width you have created the table.
    col2 char(20) nullif col2=blanks,
    col3 date(14) "MMDDYYYY" nullif col3=blanks
    )
    NOTE: Do not specify nullif (columnname)=blanks if the column is primary or foreign or not null columns.
    Let me know if this helps.
    Last edited by prince_jr; 10-13-2003 at 02:36 PM.

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    sarovb, did you ever fix this? was it really due to "non-standard" record separators? if so have a look at SRF here: http://www.csee.umbc.edu/help/oracle...ch03.htm#11755

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