14 Replies Latest reply: Dec 23, 2013 4:08 AM by Pierre Techoueyres RSS

    4.0 EA1 - Cannot select from table with xmltype

    Johannes M

      Hi,

       

      I have the following table. When doing a select * from horus_owner.horus_objects nothing is shown. This could be reproduced with both 10g and 11g on the database-side.

       

       

      Regards,

      Johannes

       

      -- Wiedergabe von TABLE DDL für Objekt HORUS_OWNER.HORUS_OBJECTS nicht möglich, da DBMS_METADATA internen Generator versucht.

      CREATE TABLE HORUS_OWNER.HORUS_OBJECTS

      (

        ID NUMBER(32, 0) NOT NULL

      , TRE_ID NUMBER(32, 0) NOT NULL

      , WRK_ID NUMBER(32, 0) NOT NULL

      , NAME VARCHAR2(200 BYTE) NOT NULL

      , MOD_TYPE VARCHAR2(10 BYTE) NOT NULL

      , LOCKED_BY VARCHAR2(30 BYTE)

      , PETRI_XML SYS.XMLTYPE

      , AOM_XML SYS.XMLTYPE

      , SHM_XML SYS.XMLTYPE

      , ORG_XML SYS.XMLTYPE

      , ROLE_XML SYS.XMLTYPE

      , MIT_XML SYS.XMLTYPE

      , RUL_XML SYS.XMLTYPE

      , TXT_DATA CLOB

      , CREATED DATE NOT NULL

      , CREATED_BY VARCHAR2(30 BYTE) NOT NULL

      , UPDATED_BY VARCHAR2(30 BYTE)

      , UPDATED DATE

      , SIM_XML SYS.XMLTYPE

      , GLOSSARY_XML SYS.XMLTYPE

      , CONSTRAINT OBJ_PK PRIMARY KEY

        (

          ID

        )

        ENABLE

      )

      LOGGING

      TABLESPACE HORUS_OWNER_DATA

      PCTFREE 10

      INITRANS 1

      STORAGE

      (

        INITIAL 65536

        NEXT 1048576

        MINEXTENTS 1

        MAXEXTENTS UNLIMITED

        BUFFER_POOL DEFAULT

      ) NOCOMPRESS

      XMLTYPE PETRI_XML STORE AS CLOB

      XMLTYPE AOM_XML STORE AS CLOB

      XMLTYPE SHM_XML STORE AS CLOB

      XMLTYPE ORG_XML STORE AS CLOB

      XMLTYPE ROLE_XML STORE AS CLOB

      XMLTYPE MIT_XML STORE AS CLOB

      XMLTYPE RUL_XML STORE AS CLOB

      LOB (TXT_DATA) STORE AS SYS_LOB0000024538C00021$$

      (

        ENABLE STORAGE IN ROW

        CHUNK 8192

        RETENTION

        NOCACHE

        LOGGING 

      )

      XMLTYPE SIM_XML STORE AS CLOB

      XMLTYPE GLOSSARY_XML STORE AS CLOBALTER TABLE HORUS_OWNER.HORUS_OBJECTS

      ADD CONSTRAINT OBJ_UK UNIQUE

      (

        WRK_ID

      , NAME

      , TRE_ID

      )

      ENABLEALTER TABLE HORUS_OWNER.HORUS_OBJECTS

      ADD CONSTRAINT OBJ_TRE_FK FOREIGN KEY

      (

        TRE_ID

      )

      REFERENCES HORUS_OWNER.HORUS_TREE_NODES

      (

        ID

      )

      ENABLEALTER TABLE HORUS_OWNER.HORUS_OBJECTS

      ADD CONSTRAINT AVCON_1243352806_MOD_T_000 CHECK

      (MOD_TYPE

      IN ('EMP', 'ROL', 'ENT', 'OCH', 'XML', 'SHM', 'OSM', 'BUM', 'BOM',

      'BEH', 'KPI', 'RIS', 'SER', 'STR', 'SWO', 'AOM', 'CON', 'GOA', 'BUR'

      , 'BPA', 'RES', 'SIM', 'GLO','TEM', 'SAR'))

      ENABLECREATE UNIQUE INDEX HORUS_OWNER.OBJ_PK ON HORUS_OWNER.HORUS_OBJECTS (ID ASC)

      LOGGING

      TABLESPACE HORUS_OWNER_IDX

      PCTFREE 10

      INITRANS 2

      STORAGE

      (

        INITIAL 65536

        NEXT 1048576

        MINEXTENTS 1

        MAXEXTENTS UNLIMITED

        BUFFER_POOL DEFAULT

      )

      NOPARALLEL

      CREATE INDEX HORUS_OWNER.OBJ_TRE_FK_I ON HORUS_OWNER.HORUS_OBJECTS (TRE_ID ASC)

      LOGGING

      TABLESPACE HORUS_OWNER_IDX

      PCTFREE 10

      INITRANS 2

      STORAGE

      (

        INITIAL 65536

        NEXT 1048576

        MINEXTENTS 1

        MAXEXTENTS UNLIMITED

        BUFFER_POOL DEFAULT

      )

      NOPARALLEL

      CREATE UNIQUE INDEX HORUS_OWNER.OBJ_UK ON HORUS_OWNER.HORUS_OBJECTS (WRK_ID ASC, NAME ASC, TRE_ID ASC)

      LOGGING

      TABLESPACE HORUS_OWNER_IDX

      PCTFREE 10

      INITRANS 2

      STORAGE

      (

        INITIAL 65536

        NEXT 1048576

        MINEXTENTS 1

        MAXEXTENTS UNLIMITED

        BUFFER_POOL DEFAULT

      )

      NOPARALLEL

      CREATE INDEX HORUS_OWNER.OBJ_WRK_FK_I ON HORUS_OWNER.HORUS_OBJECTS (WRK_ID ASC)

      LOGGING

      TABLESPACE HORUS_OWNER_IDX

      PCTFREE 10

      INITRANS 2

      STORAGE

      (

        INITIAL 65536

        NEXT 1048576

        MINEXTENTS 1

        MAXEXTENTS UNLIMITED

        BUFFER_POOL DEFAULT

      )

      NOPARALLEL

        • 1. Re: 4.0 EA1 - Cannot select from table with xmltype
          pacman

          It's the same for me.

          SELECTing all but the xmltype fields work. As soon as I add an xmltype field in the SELECT statement no records are returned.

          • 2. Re: 4.0 EA1 - Cannot select from table with xmltype
            Dwhittin-Oracle

            That is the only available workaround for this version (EA1).

             

            It is a known issue and is caused by an incompatibility between JDBC, XDB and the OSGI framework. We are working on a fix for a future release.

            • 3. Re: 4.0 EA1 - Cannot select from table with xmltype
              user9981652

              I have tried the latest version(EA2) 4.0.0.12.84. it shows as (XMLTYPE) instead of real xml strings as shown in 3.0.22 version. Once you click the column field. it pops up with empty window.

              • 4. Re: 4.0 EA1 - Cannot select from table with xmltype
                Jeff Smith Sqldev Pm-Oracle

                This appears to be fixed. Please give EA3 a try when it becomes available.

                • 5. Re: 4.0 EA1 - Cannot select from table with xmltype
                  nudo

                  I noticed the same issue today.  Until EA3 becomes available, you can get the XML string as a CLOB using XMLTYPE.GETCLOBVAL(). 

                  • 6. Re: 4.0 EA1 - Cannot select from table with xmltype
                    Pierre Techoueyres

                    Hello,

                    It's look like the issue is still there in EA3.

                    • 7. Re: 4.0 EA1 - Cannot select from table with xmltype
                      Gary Graham-Oracle

                      Hi Pierre,

                      It's look like the issue is still there in EA3.

                      Since SYS owns several tables and views with XMLTYPE columns, it is very to see there are cases where SELECT * succeeds using EA3.  It would help immensely if you would provide a detailed test case showing a failure.

                       

                      In my case, against Oracle 11.2.0.1, I tested both with thin and oci/thick drivers.  I believe the DDL of  SYS tables containing the XMLTYPE columns use STORE AS CLOB or STORE AS BASICFILE CLOB.  Perhaps your case is on 11.2.0.2+ and/or uses SECUREFILE CLOB or BINARY XML?  Please let us know.

                       

                      Thanks,

                      Gary

                      SQL Developer Team

                      • 8. Re: 4.0 EA1 - Cannot select from table with xmltype
                        Dwhittin-Oracle

                        Pierre,

                         

                        Are you certain the XMLTYPE columns you are now accessing and previously were accessing were backed by CLOBs rather that XML DB?

                         

                        SQL Developer has never been able to access the value of an XML DB XMLTYPE columns unless a thick OCI driver was being used. The original issue in EA1 was caused by an inconsistent client-side jar configuration which corrupted the result set metadata and so caused the display of the grid to fail. The inconsistency was resolved in EA2 onwards.

                         

                        Recently the 11.2.0.3 patch and 12c server-side has supported retrieval of XML DB XMLTYPE column via the CLOB api and so should be displayed in SQLDeveloper. However a server-side bug prevents this (incorrect detection of connection protocol) therefore the server-side patch 16881111 is required to fix this.

                         

                        Regards,

                        Richard

                        SQL Developer Team

                        • 9. Re: 4.0 EA1 - Cannot select from table with xmltype
                          Pierre Techoueyres

                          I'm not certain. But here is my really stupid test case :

                           

                           

                          select xmlElement("Document", 'Hello, World') x

                          from   dual;

                           

                          On 3.2.20.09 build 09-87 returns <Document>Hello, World</Document>

                          On 4.0.0.13 build 13-30 return null

                           

                          same database, same user, same connexion.

                           

                          Here is what returns select * from v$version;

                           

                          Oracle Database 10g Release 10.2.0.4.0 - Production

                          PL/SQL Release 10.2.0.4.0 - Production

                          "CORE    10.2.0.4.0    Production"

                          TNS for Linux: Version 10.2.0.4.0 - Production

                          NLSRTL Version 10.2.0.4.0 - Production

                           

                          Hope this could help

                          • 10. Re: 4.0 EA1 - Cannot select from table with xmltype
                            Gary Graham-Oracle

                            Well, connecting to 11.2.0.1 with 4.0 EA3, that test case works fine whether my Database > Advanced preference points to the Oracle client (and checks off the Use OCI/Thick driver box) or not.  In terms of how it all works with 10.2.0.4, I have no idea.

                             

                            Not sure if that makes sense in light of Richard's explanation, but perhaps the case is just simple enough to work without OCI.  The only 10.2 database I have access to happens to be 10.2.0.5.  That case also works there without OCI.

                             

                            -Gary-

                            • 11. Re: 4.0 EA1 - Cannot select from table with xmltype
                              Dwhittin-Oracle

                              Pierre,

                               

                              Thanks for the test case. I'll check it out. I'm wondering if there is a mismatch between the 10.2 database and the 12c XML DB client in SQL Dev 4.0. The 11g database XML server-side was completely rewritten.

                               

                              Thanks,

                              Richard

                              SQL Developer Team

                              • 12. Re: 4.0 EA1 - Cannot select from table with xmltype
                                nudo

                                Not sure if this helps, but I was trying to come up with some test cases myself and I noticed some strange behavior.  4 simple test cases:

                                1. SELECT XMLTYPE('<OUTER><INNER>TEST</INNER></OUTER>') FROM DUAL;
                                2. SELECT XMLELEMENT("OUTER", XMLELEMENT("INNER", 'TEST')) FROM DUAL;
                                3. SELECT XMLELEMENT("OUTER", XMLAGG(XMLFOREST('TEST' "INNER"))) FROM DUAL;
                                4. SELECT XMLQUERY('/OUTER' PASSING XMLTYPE('<OUTER><INNER>TEST</INNER></OUTER>') RETURNING CONTENT) FROM DUAL;

                                 

                                1 and 2 return the xml string in the result grid column.  3 and 4 return (XMLTYPE) with no data in the pop-up editor when double clicking the result.  I changed #3 to:

                                SELECT XMLELEMENT("OUTER", XMLFOREST('TEST' "INNER")) FROM DUAL;

                                and it returns the XML string.

                                I used undo to revert back to my original #3 and it started showing the XML string instead of (XMLTYPE) previously observed.

                                I changed my connection to another schema, ran #3 again, and it went back to (XMLTYPE).  I tried to recreate the same behavior using the 2nd schema, but I was unable to get to show the XML string. 

                                In any test case where I was getting the (XMLTYPE), I was not able to see data in the pop-up editor.

                                 

                                Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production    

                                PL/SQL Release 11.2.0.3.0 - Production                                          

                                CORE 11.2.0.3.0 Production                                                        

                                TNS for HPUX: Version 11.2.0.3.0 - Production                                   

                                NLSRTL Version 11.2.0.3.0 - Production 

                                • 13. Re: 4.0 EA1 - Cannot select from table with xmltype
                                  User286067

                                  I can confirm what user3919740 mentioned above, had a complaint from one of my users about grid not displaying contents of XMLTYPE column in a view. It displays data when you run with 'F5' or xmltype.getclobval() around the column.

                                   

                                  This is against a 11203 database and I am running 4EA3 as well.

                                  • 14. Re: 4.0 EA1 - Cannot select from table with xmltype
                                    Pierre Techoueyres

                                    Any news ?

                                    The bug is still present in final release. And the workaround is ... an workaround.

                                    This will become a nogo for us.

                                     

                                    The testcase is still the same :

                                    select xmlElement("Document", 'Hello, World') x

                                    from   dual;

                                     

                                    On 3.2.20.09 build 09-87 returns <Document>Hello, World</Document>

                                    On 4.0.0.13 build 13-30 return null

                                     

                                    same database, same user, same connexion.

                                     

                                    Here is what returns select * from v$version;

                                     

                                    Oracle Database 10g Release 10.2.0.4.0 - Production

                                    PL/SQL Release 10.2.0.4.0 - Production

                                    "CORE    10.2.0.4.0    Production"

                                    TNS for Linux: Version 10.2.0.4.0 - Production

                                    NLSRTL Version 10.2.0.4.0 - Production