Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2013
    Posts
    19

    Unanswered: External Table to read § as delimeter [ Help !!! ]

    Hi All,

    Is anyone can guide or know how to using External Table to read data file with § delimeter ?

    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is anyone can guide or know how to using External Table to read data file with § delimeter ?

    Process the same way as you would process a Comma Separated Value file; just replace your delimiter character instead of the comma.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2013
    Posts
    19
    Quote Originally Posted by anacedent View Post
    >Is anyone can guide or know how to using External Table to read data file with § delimeter ?

    Process the same way as you would process a Comma Separated Value file; just replace your delimiter character instead of the comma.
    Hi Ana,

    I tried before I posted, I had encountered this error "value too large for column".
    Seems like external table not able to detect the § delimeter.

    below is my External Table Creation Script.

    CREATE TABLE STD_DTL
    (
    STD_NM VARCHAR2(50),
    STD_ID VARCHAR2(30)
    )
    ORGANIZATION EXTERNAL
    (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY TMP_PATH
    ACCESS PARAMETERS
    (
    RECORDS DELIMITED BY "\n"
    FIELDS TERMINATED BY "§" LRTRIM
    MISSING FIELD VALUES ARE NULL
    )
    LOCATION ('std_dtl.dat')
    );

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Perhaps I need my eyes checked.
    As a general rule, Oracle does not use double quote marks.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2013
    Posts
    19
    Quote Originally Posted by anacedent View Post
    Perhaps I need my eyes checked.
    As a general rule, Oracle does not use double quote marks.
    I just tried with single quote, still not able to work

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Could you post several lines of the input file (std_dtl.dat) as well?

  7. #7
    Join Date
    Jan 2013
    Posts
    19
    Quote Originally Posted by Littlefoot View Post
    Could you post several lines of the input file (std_dtl.dat) as well?
    Below is the data.

    Ali§123
    MengKai§456
    BoonKee§789

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Works OK for me on 10.2.0.3.0 (the only change I did in your code is external directory name. I copy/pasted your sample records into a file).
    Code:
    SQL> create table std_dtl
      2  (
      3     std_nm   varchar2 (50),
      4     std_id   varchar2 (30)
      5  )
      6  organization external
      7  (
      8  type oracle_loader
      9  default directory ext_dir
     10  access parameters
     11  (
     12  records delimited by "\n"
     13  fields terminated by "§" lrtrim
     14  missing field values are null
     15  )
     16  location ('std_dtl.dat')
     17  );
    
    Table created.
    
    SQL> select * from std_dtl;
    
    STD_NM                                             STD_ID
    -------------------------------------------------- ------------------------------
    Ali                                                123
    MengKai                                            456
    BoonKee                                            789
    
    SQL>
    What happens when you try it? Please, copy/paste your SQL*Plus session, just like I did.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What is the charset for your database? If the code page doesn't contain § it will not be able to use it in an external table. Many applications use the pipe | as the delimiter.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jan 2013
    Posts
    19
    Quote Originally Posted by Littlefoot View Post
    Works OK for me on 10.2.0.3.0 (the only change I did in your code is external directory name. I copy/pasted your sample records into a file).
    Code:
    SQL> create table std_dtl
      2  (
      3     std_nm   varchar2 (50),
      4     std_id   varchar2 (30)
      5  )
      6  organization external
      7  (
      8  type oracle_loader
      9  default directory ext_dir
     10  access parameters
     11  (
     12  records delimited by "\n"
     13  fields terminated by "§" lrtrim
     14  missing field values are null
     15  )
     16  location ('std_dtl.dat')
     17  );
    
    Table created.
    
    SQL> select * from std_dtl;
    
    STD_NM                                             STD_ID
    -------------------------------------------------- ------------------------------
    Ali                                                123
    MengKai                                            456
    BoonKee                                            789
    
    SQL>
    What happens when you try it? Please, copy/paste your SQL*Plus session, just like I did.
    I guess is encoding issue. I see the data file is in ANSI encoding.

  11. #11
    Join Date
    Jan 2013
    Posts
    19
    Quote Originally Posted by beilstwh View Post
    What is the charset for your database? If the code page doesn't contain § it will not be able to use it in an external table. Many applications use the pipe | as the delimiter.
    Hi Bil, not sure how to check charset in database. Between I cannot choose pipe as delimeter due to data consist this character. That's why I have to choose those rare character being use.

  12. #12
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    SELECT * FROM NLS_DATABASE_PARAMETERS;

    For example, my NLS_CHARACTERSET is US7ASCII and the
    § character is not available in the database.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  13. #13
    Join Date
    Jan 2013
    Posts
    19
    Quote Originally Posted by beilstwh View Post
    SELECT * FROM NLS_DATABASE_PARAMETERS;

    For example, my NLS_CHARACTERSET is US7ASCII and the
    § character is not available in the database.
    I have been searching Extended ASCII in google. But different website giving different ASCII Hex Code. Currently I am using other character to replace this snake character. And also I cannot find the exact ASCII Hex Code for §.

    Table of Extended ASCII Characters and Their Code Equivalents - National Instruments

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1* select ascii('§') from dual
    SQL> /
    
    ASCII('??')
    -----------
       15712189
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  15. #15
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    again it all depends on your charset. for example on my database

    test>select ascii('§') from dual;

    ASCII('?')
    ----------
    63

    test>select chr(63) from dual;

    C
    -
    ?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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