Field Delimiter in External Table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Field Delimiter in External Table

  1. #1
    Join Date
    Jan 2002
    Posts
    148

    Field Delimiter in External Table

    Hi ,

    I got a text file with delimiter description as x'6a' .
    What would be the exact syntax in the External Table Configuration parameter.
    i tired with
    .... Fields terminated by "x'6a'"
    .... Fields terminated by "x''6a''"
    .... Fields terminated by "0x'6a'"
    .... Fields terminated by "0x''6a''"

    Table gets created with no errors, but errors generate during data selection,cause, field terminator is not matched with above defined.

    Following is the external table defination
    ------------------------ def start here -----------
    drop table mci
    /
    create table mci ( allcol varchar2(13),
    col1 varchar2(100),
    col2 varchar2(100),
    col3 varchar2(100),
    col4 varchar2(100),
    col5 varchar2(100),
    col6 varchar2(100),
    col7 varchar2(100),
    col8 varchar2(100),
    col9 varchar2(100),
    col10 varchar2(100),
    col11 varchar2(100),
    col12 varchar2(100),
    col13 varchar2(100),
    col14 varchar2(100),
    col15 varchar2(100),
    col16 varchar2(100)
    )
    organization external
    ( type oracle_loader
    default directory F_test
    access parameters
    (
    RECORDS DELIMITED BY NEWLINE fields terminated by "0x''6a''" (
    allcol char(13),
    col1 char(100),
    col2 char(100),
    col3 char(100),
    col4 char(100),
    col5 char(100),
    col6 char(100),
    col7 char(100),
    col8 char(100),
    col9 char(100),
    col10 char(100),
    col11 char(100),
    col12 char(100),
    col13 char(100),
    col14 char(100),
    col15 char(100),
    col16 char(100)
    )
    )
    location ( 'ia03.txt' )
    )
    /

    ------------------------ def end here --------------
    I appreciate your time and suggestion .

    Thanks
    Jr.
    Last edited by Jr; 07-13-2004 at 06:45 PM.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Have you thought about doing a find replace on the file and changing the delimeter to ~ (tilde)? If you are on a unix system sed and or awk might work, they aren't my strong suit. You may want to change to a simpler delimeter going forward.

  3. #3
    Join Date
    Jan 2002
    Posts
    148

    Thumbs up

    Thanks for the reply Gandolf, unfortunately the datafile is 20+GB,so i cant do that.

    Guess what,
    simple copy and paste from the datafile to control file worked.
    Jr.
    Last edited by Jr; 07-14-2004 at 04:02 PM.

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