8 Replies Latest reply: Mar 5, 2013 7:42 AM by odie_63 RSS

    column type not supported on external organized table

    994523
      Hi
      i crated external table which is like

      create table ext_table_csv (
      xml_data XMLType
      )
      organization external (
      type oracle_loader
      default directory external_dir
      access parameters (
      records delimited by "<DATABASE_OBJECTS>"
      badfile my_ext_tab:'empxt%a_%p.bad'
      logfile my_ext_tab:'empxt%a_%p.log'
      )
      location ('objects.xml')
      )
      reject limit unlimited;

      when i am trying to execute this it gives the following error

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

      database is 11g.but why external table not supported xmltype column

      Please help to resolve this issue..
        • 1. Re: column type not supported on external organized table
          mdrake-Oracle
          The message is quite clear. External tables are not supported for XMLType.

          In theory you could define your XML as a CLOB and apply an XMLType constructor to it when accessing the table, but I've never tried this.

          You can load XMLType directly via SQL*LOADER or the BFILE mechanism
          • 2. Re: column type not supported on external organized table
            odie_63
            Hi,
            Please help to resolve this issue..
            The issue is that you're probably using the wrong tool for the job.
            External tables and SQL*Loader are not XML parsers.

            Explain what you're trying to do, give us some sample data and expected output, as well as the full version number, and we'll be able to suggest something better.
            • 3. Re: column type not supported on external organized table
              994523
              hi,
              i created one external table like following
              create table ext_table_xml (
              owner VARCHAR2(2000),
              object_type VARCHAR2(2000),
              object_name VARCHAR2(2000),
              object_user_name VARCHAR2(2000),
              physical_name VARCHAR2(2000)
              )
              organization external (
              type oracle_loader
              default directory external_dir
              ACCESS PARAMETERS(
              RECORDS DELIMITED BY "</OBJECT_NAME>"
              fields LRTRIM
              Missing field values are null
              (
              owner char(2000) enclosed by "<OWNER>" and "</OWNER>",
              object_type char(2000) enclosed by "<OBJECT_TYPE>" and "</OBJECT_TYPE>",
              object_name char(2000) enclosed by "<OBJECT_NAME>" and "</OBJECT_NAME>",
              object_user_name char(2000) enclosed by "<OBJECT_USER_NAME>" and "</OBJECT_USER_NAME>",
              physical_name char(2000) enclosed by "<PHYSICAL_NAME>" and "</PHYSICAL_NAME>"
              )
              )
              location ('objects.xml')
              )
              PARALLEL
              reject limit unlimited;


              table got created

              while trying to retreive data from this table like
              select * from ext_table_xml
              no data is getting displayed.

              i check the log file
              there i got the error like:

              KUP-04035: beginning enclosing delimiter not found

              below is the xmlfile :

              <DATABASE_OBJECTS><OWNER>APPS</OWNER>
              <OBJECT_TYPE>FUNCTION</OBJECT_TYPE>
              <OBJECT_NAME>PR_CFUNCTION</OBJECT_NAME>
              </DATABASE_OBJECTS>

              Please tell me where is the problem...
              • 4. Re: column type not supported on external organized table
                odie_63
                SQL> SELECT *
                  2  FROM XMLTable(
                  3         '/DATABASE_OBJECTS'
                  4         passing xmltype(bfilename('EXTERNAL_DIR','objects.xml'), nls_charset_id('AL32UTF8'))
                  5         columns
                  6           owner       varchar2(30) path 'OWNER'
                  7         , object_type varchar2(30) path 'OBJECT_TYPE'
                  8         , object_name varchar2(30) path 'OBJECT_NAME'
                  9       )
                 10  ;
                 
                OWNER                          OBJECT_TYPE                    OBJECT_NAME
                ------------------------------ ------------------------------ ------------------------------
                APPS                           FUNCTION                       PR_CFUNCTION
                 
                It's OK from relatively small files. For bigger ones, at least a two-step approach is necessary (loading the entire file in an XMLType column, then applying the above on it).

                Edited by: odie_63 on 4 mars 2013 13:11 - corrected typo on charset name
                • 5. Re: column type not supported on external organized table
                  994523
                  i run same query but i got the following error.

                  Error report:
                  SQL Error: ORA-31011: XML parsing failed
                  ORA-19202: Error occurred in XML processing
                  LPX-00210: expected '=' instead of '>'
                  Error at line 2
                  ORA-06512: at "SYS.XMLTYPE", line 296
                  ORA-06512: at line 1
                  31011. 00000 - "XML parsing failed"
                  *Cause:    XML parser returned an error while trying to parse the document.
                  *Action:   Check if the document to be parsed is valid.

                  sql query is:

                  SELECT *
                  FROM XMLTable(
                  '/DATABASE_OBJECTS'
                  passing xmltype(bfilename('EXTERNAL_DIR','objects.xml'), nls_charset_id('AL2UTF8'))
                  columns
                  owner varchar2(30) path 'OWNER'
                  , object_type varchar2(30) path 'OBJECT_TYPE'
                  , object_name varchar2(30) path 'OBJECT_NAME'
                  );
                  • 6. Re: column type not supported on external organized table
                    Jason_(A_Non)
                    How closely did you read the bottom of the error message you reported?
                    *Cause: XML parser returned an error while trying to parse the document.
                    *Action: Check if the document to be parsed is valid.
                    That would be spot-on in this case as the file you are working with is not valid XML. As you have not shown us what objects.xml really looks like, there is not a lot we can offer for suggestions.

                    So what are lines 1 - 3 of your file?
                    • 7. Re: column type not supported on external organized table
                      994523
                      below is objects.xml file

                      <?xml version="1.0" encoding="UTF-8"?><DATABASE_OBJECTS><OWNER>APPS</OWNER><OBJECT_TYPE>FUNCTION</OBJECT_TYPE>
                      <OBJECT_NAME>PR_CFUNCTION</OBJECT_NAME>
                      <OWNER>APPS</OWNER><OBJECT_TYPE>INDEX</OBJECT_TYPE>
                      <OBJECT_NAME>ADM_ESTIMATE_DATA_U1</OBJECT_NAME>
                      <OWNER>APPS</OWNER><OBJECT_TYPE>INDEX</OBJECT_TYPE>
                      <OBJECT_NAME>XML_DETAILS_PK</OBJECT_NAME>
                      <OWNER>APPS</OWNER><OBJECT_TYPE>LOB</OBJECT_TYPE>
                      <OBJECT_NAME>SYS_LOB0000435327C00002$$</OBJECT_NAME>
                      <OWNER>APPS</OWNER><OBJECT_TYPE>PACKAGE</OBJECT_TYPE>
                      <OBJECT_NAME>ALLOT010T_PROCESS_XML_PKG</OBJECT_NAME>
                      <OWNER>APPS</OWNER><OBJECT_TYPE>PACKAGE</OBJECT_TYPE>
                      <OBJECT_NAME>PR_CPKG</OBJECT_NAME>
                      <OWNER>APPS</OWNER><OBJECT_TYPE>PACKAGE BODY</OBJECT_TYPE>
                      <OBJECT_NAME>ALLOT010T_PROCESS_XML_PKG</OBJECT_NAME></DATABASE_OBJECTS>
                      • 8. Re: column type not supported on external organized table
                        odie_63
                        That's a rather peculiar format. It would make more sense to have each object wrapped in its own "ROW" element.

                        This works for me on 11.2.0.2 with the sample file you gave :
                        SELECT *
                        FROM XMLTable(
                               'for $i in /DATABASE_OBJECTS/OWNER
                                return element r {
                                  $i/following-sibling::OBJECT_TYPE[1]
                                , $i/following-sibling::OBJECT_NAME[1]
                                , $i
                                }'
                               passing xmltype(bfilename('TEST_DIR','objects.xml'), nls_charset_id('AL32UTF8'))
                               columns 
                                 owner       varchar2(30) path 'OWNER'
                               , object_type varchar2(30) path 'OBJECT_TYPE'
                               , object_name varchar2(30) path 'OBJECT_NAME'
                             )
                        ;