3 Replies Latest reply: Jun 19, 2013 1:32 PM by Barbara Boehmer RSS

    Error in creating an external table referring to a XML file

    Mark1970

      I've got an XML file and I've tried to create an external table referring to it in this way:

       

       

      CREATE TABLE mytable

        (

          XML_DATA_COLUMN XMLType

        )

        ORGANIZATION EXTERNAL

        (

          TYPE ORACLE_LOADER DEFAULT DIRECTORY TEST_DIR

          ACCESS PARAMETERS ( records delimited BY newline

          (XML_DATA_COLUMN LOB) ) LOCATION ( 'myfile.xml' )

        ) ;

       

       

      where TEST_DIR is the directory where myfile.xml is stored,

       

       

      but I get this message:

       

       

      Error at Command Line:3 Column:4

      Error report:

      SQL Error: ORA-30656: column type not supported on external organized table

      30656.0000 -  "column type not supported on external organized table"

      *Cause:    Attempt to create an external organized table with a column

                 of type LONG, LOB, BFILE, ADT, or VARRAY.

      *Action:   These column types are not supported, change the DDL.

       

       

      I want to have in the XML_DATA_COLUMN the content of myfile.xml so as to handle it by using extract and extractvalue functions.

      My oracle version is 10gR2 Express Edition

       

       

      Thanks!