Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: External tables: files with a tab delimiter

    What syntax do you use when the incoming file is tab-delimited? "tab" is not a reserved word for the Oracle Loader utility (like "newline" is), so do you use an Ascii equivalent?

    Code:
    CREATE TABLE PTS.NL2301_LOAD_TABLE
    (
      POSITION              CHAR(7 BYTE),
      NAME                  CHAR(50 BYTE),
      TYPE                  CHAR(3 BYTE),
      BEGINNING_BALANCE     NUMBER(7,2),
      ADJUSTMENT            NUMBER(7,2),
      AUTO_ACCRUAL          NUMBER(7,2),
      ENTERED_ACCRUAL       NUMBER(7,2),
      LEAVE_PAID            NUMBER(7,2),
      LEAVE_TAKEN           NUMBER(7,2),
      LOST_LEAVE            NUMBER(7,2),
      ENDING_BALANCE        NUMBER(7,2),
      XFR_FROM_CLR_ACCOUNT  NUMBER(7,2)
    )
    ORGANIZATION EXTERNAL
      (  TYPE ORACLE_LOADER
         DEFAULT DIRECTORY PTS_DATA_DIR
         ACCESS PARAMETERS 
           ( records delimited by newline
       nobadfile nologfile nodiscardfile
       fields terminated by tab OPTIONALLY ENCLOSED BY "(" and ")"
       missing field values are null
       (
      POSITION,
      NAME,
      TYPE,
      BEGINNING_BALANCE,
      ADJUSTMENT,
      AUTO_ACCRUAL,
      ENTERED_ACCRUAL,
      LEAVE_PAID,
      LEAVE_TAKEN,
      LOST_LEAVE,
      ENDING_BALANCE,
      XLR_FROM_CLR_ACCOUNT
       )
      )
         LOCATION (PTS_DATA_DIR:'NL2301')
      )
    REJECT LIMIT 0;
    -cf

  2. #2
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    chuck, try with:
    Code:
    fields terminated by 0X'09'

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    u rock!

    Could you tell me where you found that info? I looked everywhere I could think of.

    -cf

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    So, the following works if there is a tab-delimiter, but fails when there's a comma within the datafile

    Code:
    CREATE TABLE PTS.NL2301
    (
      DCBS_POSITION         VARCHAR2(7 BYTE),
      NAME                  VARCHAR2(14 BYTE),
      TYPE                  VARCHAR2(3 BYTE),
      BEGINNING_BALANCE     NUMBER(7,2),
      ADJUSTMENT            NUMBER(7,2),
      AUTO_ACCRUAL          NUMBER(7,2),
      ENTERED_ACCRUAL       NUMBER(7,2),
      LEAVE_PAID            NUMBER(7,2),
      LEAVE_TAKEN           NUMBER(7,2),
      LOST_LEAVE            NUMBER(7,2),
      ENDING_BALANCE        NUMBER(7,2),
      XLR_FROM_CLR_ACCOUNT  NUMBER(7,2)
    )
    ORGANIZATION EXTERNAL
      (  TYPE ORACLE_LOADER
         DEFAULT DIRECTORY PTS_DATA_DIR
         ACCESS PARAMETERS 
           ( records delimited by newline
       nobadfile 
       nologfile 
       nodiscardfile
       fields terminated by 0X'09' 
       missing field values are null
       (
      DCBS_POSITION,
      NAME,
      TYPE,
      BEGINNING_BALANCE,
      ADJUSTMENT,
      AUTO_ACCRUAL,
      ENTERED_ACCRUAL,
      LEAVE_PAID,
      LEAVE_TAKEN,
      LOST_LEAVE,
      ENDING_BALANCE,
      XLR_FROM_CLR_ACCOUNT
       )
      )
         LOCATION (PTS_DATA_DIR:'NL2301')
      )
    REJECT LIMIT UNLIMITED;
    works with

    Code:
    0000515	JONES NANCY E	CT	12.50							12.50	
    0000515	JONES NANCY E	GL	-8.00							8.00
    fails with

    Code:
    0000515	JONES, NANCY E	CT	12.50							12.50	
    0000515	JONES, NANCY E	GL	-8.00							8.00
    So I tried changing over to using commas and quoting strings with commas in them, but that fails

    Code:
    create table PTS.NL2301_2
    (
      DCBS_POSITION              VARCHAR2(7 BYTE),
      NAME                  VARCHAR2(14 BYTE),
      TYPE                  VARCHAR2(3 BYTE),
      BEGINNING_BALANCE     NUMBER(7,2),
      ADJUSTMENT            NUMBER(7,2),
      AUTO_ACCRUAL          NUMBER(7,2),
      ENTERED_ACCRUAL       NUMBER(7,2),
      LEAVE_PAID            NUMBER(7,2),
      LEAVE_TAKEN           NUMBER(7,2),
      LOST_LEAVE            NUMBER(7,2),
      ENDING_BALANCE        NUMBER(7,2),
      XLR_FROM_CLR_ACCOUNT  NUMBER(7,2)
    )
      organization external
    ( type oracle_loader
      default directory pts_data_dir
      access parameters
    	( records delimited by newline
    	  nobadfile 
    	  nologfile 
    	  nodiscardfile
    	  fields terminated by ',' optionally enclosed by '"' 
    	  missing field values are null
    	  (
      DCBS_POSITION,
      NAME,
      TYPE,
      BEGINNING_BALANCE,
      ADJUSTMENT,
      AUTO_ACCRUAL,
      ENTERED_ACCRUAL,
      LEAVE_PAID,
      LEAVE_TAKEN,
      LOST_LEAVE,
      ENDING_BALANCE,
      XLR_FROM_CLR_ACCOUNT
    	  )
    	)
    	location ('NL2301')
    ) reject limit unlimited;
    works with

    Code:
    0000515,"JONES NANCY E",CT,12.50,,,,,,,12.50,,
    0000515,"JONES NANCY E",GL,-8.00,,,,,,,-8.00,,
    fails with

    Code:
    0000515,"JONES, NANCY E",CT,12.50,,,,,,,12.50,,
    0000515,"JONES, NANCY E",GL,-8.00,,,,,,,-8.00,,
    I can't seem to get any delimiter to work when there's a comma in my NAME field. What's the fix for this?

    -Chuck

  5. #5
    Join Date
    May 2004
    Location
    BA [ARG]
    Posts
    137
    I've just tried the first example you posted (with fields terminated by 0X'09' only) and it works for me (both the table and the data).
    I'm working on a 9.2.0.5 database, what about you?

    Code:
    SQL> select DCBS_POSITION, NAME from NL2301
      2  /
    
    DCBS_POSITION         NAME
    --------------------- ------------------------------------------
    0000515               JONES, NANCY E
    0000515               JONES, NANCY E
    
    SQL>

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    version 10.1.0.3.0
    -cf

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Man. Since I was supressing the logfile during testing, I didn't look in it to see that the message sent was that the field NAME was not defined large enough, so the field was declared at 14 characters, and the "," was adding a fifteenth (I substituted JONES for the real last name of an employee).

    -Chuck

  8. #8
    Join Date
    Aug 2006
    Posts
    1
    Quote Originally Posted by DKG
    chuck, try with:
    Code:
    fields terminated by 0X'09'
    You are a legend mate! But where did you get that piece of intelligence from?!

    -Ahsan

Posting Permissions

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