2 Replies Latest reply: Dec 7, 2012 7:02 AM by Arul Kumar Singaravelu RSS

    Reading XML file in External Table

    Arul Kumar Singaravelu
      Hi,

      The below query is working fine and desired output coming in Oracle 11g 32 bit Windows environment but not in Oracle 11g 64 bit environment.

      Appreciate your response on this..

      CREATE TABLE addressbook_ext(
      contact_name VARCHAR2(2000),
      address VARCHAR2(2000)
      )
      ORGANIZATION EXTERNAL(
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY TEMP
      ACCESS PARAMETERS(
      RECORDS DELIMITED BY "</contact>"
      FIELDS MISSING FIELD VALUES ARE NULL
      (
      dummy1 CHAR(2000) TERMINATED BY "<contact>",
      contact_name CHAR(2000) ENCLOSED BY "<contact_name>" AND "</contact_name>",
      address CHAR(2000) ENCLOSED BY "<address>" AND "</address>",
      dummy2 CHAR(2000) TERMINATED BY "</start>"
      )
      )
      LOCATION ('contact.xml')
      )
      PARALLEL
      REJECT LIMIT UNLIMITED
      /

      File contact.xml:

      <?xml version="1.0" encoding="UTF-8"?>
      <start>
      <contact><contact_name>Sam Disuza</contact_name><address>11</address></contact>
      <contact><contact_name>Sam Disuza3</contact_name><address>22</address></contact>
      </start>

      Log file:

      LOG file opened at 12/07/12 12:05:43

      Field Definitions for table ADDRESSBOOK_EXT
      Record format DELIMITED, delimited by </contact>
      Data in file has same endianness as the platform
      Rows with all null fields are accepted

      Fields in Data Source:

      DUMMY1 CHAR (2000)
      Terminated by "<contact>"
      Trim whitespace same as SQL Loader
      CONTACT_NAME CHAR (2000)
      Enclosed by "<contact_name>" and "</contact_name>"
      Trim whitespace same as SQL Loader
      ADDRESS CHAR (2000)
      Enclosed by "<address>" and "</address>"
      Trim whitespace same as SQL Loader
      DUMMY2 CHAR (2000)
      Terminated by "</start>"
      Trim whitespace same as SQL Loader
      KUP-04021: field formatting error for field ADDRESS
      KUP-04035: beginning enclosing delimiter not found
      KUP-04101: record 1 rejected in file F:\EXTERNAL\contact.xml
      KUP-04021: field formatting error for field ADDRESS
      KUP-04035: beginning enclosing delimiter not found
      KUP-04101: record 2 rejected in file F:\EXTERNAL\contact.xml

      Thanks..