8 Replies Latest reply: May 23, 2013 11:02 AM by rp0428 RSS

    ResultSetMetaData.getColumnType() returning "2007" for SQLXML

    994207

      A constant with value "2007" doesn't even exist in the entire world of java.. how is this possible?

      Reference:
      http://docs.oracle.com/javase/6/docs/api/constant-values.html

      Additional information:

      Database: Oracle XE 11.2.0.2.0

      JDBC driver info (taken form MANIFEST.MF):
      Created-By: 1.5.0_24-rev-b08 (Sun Microsystems Inc.)
      Implementation-Vendor: Oracle Corporation
      Implementation-Title: JDBC
      Implementation-Version: 11.2.0.2.0

      ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName); // contains a single column with XMLTYPE ResultSetMetaData metaData = rs.getMetaData(); int colType = metaData.getColumnType(1);

      Edited by: 991204 on 21.05.2013 03:35

        • 1. Re: ResultSetMetaData.getColumnType() returning "2007" for SQLXML
          Joe Weinstein-Oracle
          Look into the Oracle driver docs regarding OracleTypes.

          http://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/oracle/jdbc/OracleTypes.html

          I believe 2007 will correspond to the OPAQUE type:

          http://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/constant-values.html#oracle.jdbc.OracleTypes.OPAQUE
          • 2. Re: ResultSetMetaData.getColumnType() returning "2007" for SQLXML
            rp0428
            >
            A constant with value "2007" doesn't even exist in the entire world of java.. how is this possible?
            . . .
            ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName); // contains a single column with XMLTYPE
            >
            It is possible because, as Joe showed, that represents the Oracle OPAQUE datatype of which XMLTYPE is one example.

            Why are you concerned about what the value is?

            See the Oracle® Database JPublisher User's Guide

            Type Mapping Support for OPAQUE Types
            http://docs.oracle.com/cd/B28359_01/java.111/b31226/datamap.htm#i1005908

            In particular see Support for XMLTYPE
            http://docs.oracle.com/cd/B28359_01/java.111/b31226/datamap.htm#i1009357
            >
            In Oracle Database 11g, the SYS.XMLTYPE SQL OPAQUE type is supported with the oracle.xdb.XMLType Java class located in ORACLE_HOME/lib/xsu12.jar. This class is the default mapping, but it requires the Oracle Database 11g JDBC Oracle Call Interface (OCI) driver. It is currently not supported by the JDBC Thin driver.
            • 3. Re: ResultSetMetaData.getColumnType() returning "2007" for SQLXML
              994207
              So do I understand correctly that theoretically the Opaque datatype could be anything? So it technically doesn't necessarly have to be convertible to a SQLXML, which means I have to rely on the ResultSetMetaData.getColumnTypeName(), which will return "SYS.XMLTYPE" apparently.

              Are there experiences with how reliable these things are? I'm writing a jdbc wrapper and I can't have this string suddenly change in the next Oracle version. "SYS.XMLTYPE" is always used for SQLXML right? Or is it possible that it uses a different schema?
              Any additional advice on this? As far as I can tell, no other datatype uses the 2007/Opaque Oracle datatype as well.
              • 4. Re: ResultSetMetaData.getColumnType() returning "2007" for SQLXML
                rp0428
                >
                So do I understand correctly that theoretically the Opaque datatype could be anything? So it technically doesn't necessarly have to be convertible to a SQLXML, which means I have to rely on the ResultSetMetaData.getColumnTypeName(), which will return "SYS.XMLTYPE" apparently.
                >
                OPAQUE is OPAQUE, SQLXML is SQLXML, XMLTYPE is XMLTYPE. XMLTYPES are OPAQUE but not all OPAQUE types are XMLTYPES.
                >
                Are there experiences with how reliable these things are?
                >
                That question is meaningless. What 'things' are you talking about? Why wouldn't they be 'reliable'?
                >
                I'm writing a jdbc wrapper and I can't have this string suddenly change in the next Oracle version.
                >
                What 'string' are you talking about? Of course the contents of a 'string' might change from one row to the next row. Your address is not the same as your neighbor's address.
                >
                "SYS.XMLTYPE" is always used for SQLXML right? Or is it possible that it uses a different schema?
                >
                You are talking apples and oranges. XMLTYPE is an Oracle database datatype. SQLXML is Java related.
                >
                Any additional advice on this?
                >
                Advice on what? You haven't told us what PROBLEM you are even trying to solve or deal with.
                >
                As far as I can tell, no other datatype uses the 2007/Opaque Oracle datatype as well.
                >
                That statement makes no sense to me. There are many different datatypes use by Oracle; they each have their own 'Oracle-defined' INTERNAL code for identification. It wouldn't make sense for any other datatype to use the same code or you wouldn't be able to distinguish between the datatypes.

                Since you don't want to share your actual problem no specific advice can be given.

                I suggest you read the extensive documentation for XMLTYPE and SQLXML and how to they are used.
                Oracle® Database JPublisher User's Guide
                http://docs.oracle.com/cd/B28359_01/java.111/b31226/datamap.htm#i1009357
                >
                Support for XMLTYPE
                In Oracle Database 11g, the SYS.XMLTYPE SQL OPAQUE type is supported with the oracle.xdb.XMLType Java class located in ORACLE_HOME/lib/xsu12.jar. This class is the default mapping, but it requires the Oracle Database 11g JDBC Oracle Call Interface (OCI) driver. It is currently not supported by the JDBC Thin driver.
                >
                Oracle® Database SQLJ Developer's Guide and Reference

                6 Objects, Collections, and OPAQUE Types

                http://docs.oracle.com/cd/B28359_01/java.111/b31227/objcoll.htm#i1008113
                >
                Oracle OPAQUE Types
                Oracle OPAQUE types are abstract data types. With data implemented as simply a series of bytes, the internal representation is not exposed. Typically an OPAQUE type will be provided by Oracle, not implemented by a customer.

                OPAQUE types are similar in some basic ways to object types, with similar concepts of static methods, instances, and instance methods. Typically, only the methods supplied with an OPAQUE type allow you to manipulate the state and internal byte representation. In Java, an OPAQUE type can be represented as oracle.sql.OPAQUE or as a custom class implementing the oracle.sql.ORAData interface. On the client-side, Java code can be implemented to manipulate the bytes, assuming the byte pattern is known. The Oracle Database 11g JPublisher utility can be useful in this way, creating a custom class implementing ORAData to allow you to manipulate data without having to make repeated round trips to the database.
                >
                The Java Tutorial
                http://docs.oracle.com/javase/tutorial/jdbc/basics/sqlxml.html
                >
                Using SQLXML Objects
                The Connection interface provides support for the creation of SQLXML objects using the method createSQLXML. The object that is created does not contain any data. Data may be added to the object by calling the setString, setBinaryStream, setCharacterStream or setResult method on the SQLXML interface.

                The following topics are covered:

                Creating SQLXML Objects
                Retrieving SQLXML Values in ResultSet
                Accessing SQLXML Object Data
                Storing SQLXML Objects
                Initializing SQLXML Objects
                Releasing SQLXML Resources
                Sample Code
                >
                The Java SQLXML interface API
                http://docs.oracle.com/javase/6/docs/api/java/sql/SQLXML.html
                >
                java.sql
                Interface SQLXML
                --------------------------------------------------------------------------------
                public interface SQLXMLThe mapping in the JavaTM programming language for the SQL XML type. XML is a built-in type that stores an XML value as a column value in a row of a database table. By default drivers implement an SQLXML object as a logical pointer to the XML data rather than the data itself. An SQLXML object is valid for the duration of the transaction in which it was created.

                The SQLXML interface provides methods for accessing the XML value as a String, a Reader or Writer, or as a Stream. The XML value may also be accessed through a Source or set as a Result, which are used with XML Parser APIs such as DOM, SAX, and StAX, as well as with XSLT transforms and XPath evaluations.

                Methods in the interfaces ResultSet, CallableStatement, and PreparedStatement, such as getSQLXML allow a programmer to access an XML value. In addition, this interface has methods for updating an XML value.
                • 5. Re: ResultSetMetaData.getColumnType() returning "2007" for SQLXML
                  994207
                  That statement makes no sense to me. There are many different datatypes use by Oracle; they each have their own 'Oracle-defined' INTERNAL code for identification. It wouldn't make sense for any other datatype to use the same code or you wouldn't be able to distinguish between the datatypes.
                  You just said before that multiple datatypes can be OPAQUE.

                  Please do not answer if you do not understand the question or you can ask specifically what you don't understand. People with real answers are getting demotivated when they see such large, inflammatory responses. My questions were clear, but I can cite them again if it helps:

                  >
                  which means I have to rely on the ResultSetMetaData.getColumnTypeName(), which will return "SYS.XMLTYPE" apparently.
                  Are there experiences with how reliable these things are *?*
                  >

                  >
                  So do I understand correctly that theoretically the Opaque datatype could be anything?
                  >

                  You did not clearly answer this either, although it seems that I did understood correctly as you said Opaque is a parent type to an unknown quantity of other types. The real question is why the Oracle JDBC driver returns "opaque" instead of "SQLXML", when clearly it has knowledge of the fact that the real type is XMLTYPE.

                  Let me repeat it just in case: Why is the Oracle JDBC driver returning "opaque"(=2007) instead of "SQLXML" (=2009)?
                  • 6. Re: ResultSetMetaData.getColumnType() returning "2007" for SQLXML
                    gimbal2
                    Its not in your best interest to push away people who are trying to help you, rp0428 is a serious contributor to this forum and one of the few people who is willing to go to great lengths to answer a question. If you can't deal with such an incredibly large volume of information that's your problem, don't try to blame him for being excessively helpful by turning it around on him and calling it "inflammatory".
                    Why is the Oracle JDBC driver returning "opaque"(=2007) instead of "SQLXML" (=2009)?
                    You'd have to ask the developers of the closed source OJDBC driver; they don't visit here. Did you try with other driver versions (older and possibly newer) to be sure it is not just a bug in the specific version you're using?
                    • 7. Re: ResultSetMetaData.getColumnType() returning "2007" for SQLXML
                      994207
                      >
                      Did you try with other driver versions?
                      >

                      Yes, but they don't work at all, I get the "cannot cast XMLTYPE to SQLXML" thing. Do you know how I can ask the jdbc driver developers directly?
                      • 8. Re: ResultSetMetaData.getColumnType() returning "2007" for SQLXML
                        rp0428
                        >
                        You just said before that multiple datatypes can be OPAQUE.
                        >
                        That is correct - I provided a doc link and an excerpt from the Oracle docs that discuss what an OPAQUE type is. It is also used for custom types that users might create which could contain literally ANYTHING. Oracle would have no idea how to deal with these types and treats them as OPAQUE.
                        >
                        Please do not answer if you do not understand the question or you can ask specifically what you don't understand.
                        >
                        I do understand the question. It is YOU who do not understand the answer.
                        >
                        The real question is why the Oracle JDBC driver returns "opaque" instead of "SQLXML", when clearly it has knowledge of the fact that the real type is XMLTYPE.

                        Let me repeat it just in case: Why is the Oracle JDBC driver returning "opaque"(=2007) instead of "SQLXML" (=2009)?
                        >
                        Let me repeat it just in case:

                        Oracle wrote the Oracle driver.

                        The Oracle driver returns Oracle types.

                        OPAQUE is an Oracle type: see the 'oracle.jdbc.OracleTypes' section of the link Joe provided
                        (http://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/constant-values.html#oracle.jdbc.OracleTypes.OPAQUE)

                        OPAQUE has a value of 2007 in that table: public static final int OPAQUE 2007

                        SQLXML is a JAVA type: see the 'java.sql.Types' section of the Constant Field Values API
                        http://docs.oracle.com/javase/6/docs/api/constant-values.html#java.sql.Types.SQLXML

                        SQLXML has a value of 2009 in that table: public static final int SQLXML 2009

                        Let me repeat it again just in case:

                        OPAQUE is SQLXML; 2009 is NOT 2007

                        They are DIFFERENT datatypes just like an Apple and an Oracle are DIFFERENT fruits. You can't treat one as if it were the other. You can't convert one to the other.

                        You are trying to compare apples and oranges and asking why an apple is not an orange.

                        This was your original question.
                        >
                        A constant with value "2007" doesn't even exist in the entire world of java.. how is this possible?
                        >
                        Because the value 2007 is for an ORACLE constant as just discussed. Oracle constants exist in an Oracle package: oracle.jdbc.OracleTypes.