1 2 Previous Next 15 Replies Latest reply: Nov 17, 2010 4:52 AM by 815459 RSS

    Select XMLTYPE column

    461430
      I'm running SQL Developer 1.5.5. on Windows XP SP2.

      Doing a SELECT with 1 xmltype column crashes SQL Developer redrawing. Table and select ist given in following sample code.
      The effect is that sql worksheet is not redrawed. Hover with the mouse over result sheets brings every column to display except xmltype column.
      SQL> desc ibb_formulardaten
       Name                  Null?    Typ
       ------------------------------ --------------
      
       ID                    NOT NULL NUMBER(10)
       FRM_ID                NOT NULL NUMBER(10)
       DATEN                          XMLTYPE
       STATUS                NOT NULL VARCHAR2(20)
      
      SQL> SELECT id, frm_id, daten FROM ibb_formulardaten WHERE id = 12345;
      Can anyone reproduce my problem?

      Many thanks for any hint!
      Christian
        • 1. Re: Select XMLTYPE column
          orclrunner
          This works for me.
          create table t1
          (   id number,
              xml_file xmltype
          )
          /
          
          create table succeeded.
          
          insert into t1 values (
           1,
           xmltype('
            <stuff>
              <numbers>
                 <item>1</item>
                 <item>2</item>
              </numbers>
              <animals>
                 <item>zebra</item>
                 <item>cat</item>
                 <item>unicorn</item>
              </animals>
            </stuff>
          '));
          
          1 rows inserted
          
          insert into t1 values (
            2,
            xmltype('
            <stuff>
              <countries>
                <item>Canada</item>
                <item>UK</item>
                <item>Spain</item>
              </countries>
              <numbers>
                <item>05</item>
                <item>55</item>
              </numbers>
            </stuff>
          '));
          
          1 rows inserted
          
          insert into xml_stuff values (
            3,
            xmltype('
            <stuff>
              <cities>
                <item>New York</item>
                <item>London</item>
                <item>Paris</item>
              </cities>
              <animals>
                <item>cockroaches</item>
                <item>snake</item>
              </animals>
            </stuff>
          '));
          
          commit ;
          
          commit succeeded.
          Now that we have 3 xml documents let's select the 2nd animal listed in each document.
          select extractValue(xml_file,'/stuff/animals/item[position()=2]') "2nd Animal" 
          from t1 ;
          
          2nd Animal
          ------------------------------
          cat
          
          snake
          
          3 rows selected
          • 2. Re: Select XMLTYPE column
            461430
            Please try to select from xml_stuff*.
            Displaying the xmltype column is it, what my sql developer crashes. Converting the xmltype to clob works well, but selecting the real xmltype causes heavy redraw problems.

            Are you using SQL Developer deliverd with java runtime or without. I'm using package with java runtime (its version 1.5).
            I found some tips, that using java 1.6 is recommended in case of display redrawing problems. I tried this today, but no change in behavior.


            Christian

            Edited by: christian13467 on 07.08.2009 18:49
            • 3. Re: Select XMLTYPE column
              orclrunner
              I'm using SQL Devleoper version 1.5.4 (without Java runtime).

              The Java version I am using is 1.5.0_15
              • 4. Re: Select XMLTYPE column
                orclrunner
                select * from t1  where id=1 ;
                
                ID                     XML_FILE  
                ---------------         --------------------------------------------
                1                       <stuff>
                                            <numbers> 
                                                 <item>1</item>
                                                 <item>59</item> 
                                            </numbers> 
                                            <animals> 
                                                 <item>bird</item>  
                                                 <item>cat</item>  
                                                 <item>dog</item>  
                                            </animals> 
                                        </stuff>   
                • 5. Re: Select XMLTYPE column
                  461430
                  Curious. I did the same as you.
                  Doing SELECT with xmltype column crashes my sql developer display.

                  I downloaded and installed last java jdk and last sql developer version.
                  Are there any config entries to change?
                  Are there any other dependencies?
                  • 6. Re: Select XMLTYPE column
                    -K-
                    To switch JDK, you have to set this an entry in \sqldeveloper\bin\sqldeveloper.conf:
                    SetJavaHome C:\Archivos de programa\Java\jdk1.6.0_10
                    Hope that helps,
                    K.
                    • 7. Re: Select XMLTYPE column
                      461430
                      No it didn't.

                      What can it be that sql developer hinders to render the query result properly?
                      • 8. Re: Select XMLTYPE column
                        -K-
                        Did you run the exact same script as Jimmy?
                        Is this your first sqldev installation?

                        K.
                        • 9. Re: Select XMLTYPE column
                          461430
                          Yes did the very same as Jimmy.

                          I started using SQL Developer from 1.2. I can't remember if it ever works on my computer.
                          What I always did was downloading the version bundeled with jdk. After reading "it might be a problem with your configuration" I downloaded the other version.
                          • 10. Re: Select XMLTYPE column
                            orclrunner
                            What happens when you run the select statement in SQL*Plus? Does it crash?

                            Post your insert statements. This way we can verify it is valid xml.

                            You might try downloading SQL Developer again. Maybe something got corrupted on your current install.

                            I'm using 'sqldeveloper-1.5.4.59.40-no-jre'
                            • 11. Re: Select XMLTYPE column
                              -K-
                              Installing new versions over a previous one could have undesirable effects, so make sure you installed in a new, clean directory.
                              Then you can try resetting to factory defaults by backing up/deleting the SQL Developer folder under the hidden Application Data under your Windows user profile.

                              Hope that helps,
                              K.
                              • 12. Re: Select XMLTYPE column
                                461430
                                Months are gone. I installed version 2.1 on a fresh computer connecting against the same databases.

                                The sql developer isn't crashing anymore, but the xmltype column is displayed as oracle.sql.OPAQUE@1dec174 (the number changes with every row and select execution). A log window is poping up showing errors like following:

                                SEVERE     843     0     oracle.dbtools.raptor.controls.grid.OracleRSTRenderer     java.lang.ClassLoader.loadLibrary(ClassLoader.java:1709)
                                SEVERE     843     0     oracle.dbtools.raptor.controls.grid.OracleRSTRenderer     java.lang.ClassLoader.loadLibrary(ClassLoader.java:1709)
                                SEVERE     843     15     oracle.dbtools.raptor.controls.grid.OracleRSTRenderer     java.lang.ClassLoader.loadLibrary(ClassLoader.java:1709)

                                Any idea which class is missing?
                                Found that oracle.sql.OPAQUE is the base class for oracle.xdb.XMLTYPE (or oracle.xdb.SQLXMLType). Is the installation missing this?

                                I installed 2.1.0.63 (without jre) using java 1.6.0_14-b08 on windows.
                                Databases are rac databases running on x64 windows.

                                Edited by: christian13467 on 07.01.2010 10:24
                                • 13. Re: Select XMLTYPE column
                                  499310
                                  This seems like an unclean installation from the evidence you have provided. OracleRSTRenderer.class is very much in the jar and is a new class in 2.1

                                  Can you please retry unzipping sqldev into a new directory ?

                                  -Raghu
                                  • 14. Re: Select XMLTYPE column
                                    461430
                                    I did this. I downloaded from otn and unzipped all into a new directory. I removed my settings from earlier versions.
                                    I don't know, what I can do more?!

                                    Christian
                                    1 2 Previous Next