11 Replies Latest reply: May 5, 2012 11:25 AM by tsuji RSS

    Can't use index addresses

    929890
      When I want to use index addressing I always get an +ORA-31065: Cannot modify read-only property[class]+. I don't understand this because I just perform a SELECT this way:

      select xml from results where xmlexists('//object[1]' passing xml)

      How can I access the n ^th^ occurence of an object element?
        • 1. Re: Can't use index addresses
          odie_63
          Never seen that error before.

          Could you post a reproducible test case (CREATE TABLE + INSERT + Query)?

          Thanks.
          • 2. Re: Can't use index addresses
            929890
            CREATE TABLE
            RESULTS
            (
            ID VARCHAR2(1024) NOT NULL,
            XML SYS.XMLTYPE NOT NULL,
            PRIMARY KEY (ID)
            )

            INSERT INTO Results VALUES
            (
            'ContributionReport/CF2_All/CF2_All/2012-04-16 04:00:00',
            '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
            <rootelem>
            <object class="com.apc.db.DbObject">
            <object class="api::Result" name="Content">
            <string name="DbReference">ContributionReport/CF2_All/CF2_All/2012-04-16 04:00:00</string>
            <object class="RiskReportResult" name="Result">
            <object class="object[]" name="RiskReportResult" size="6077">
            <object class="api::Parameters">
            <string name="PackageInstrument">101755</string>
            <string name="CoreInstrument">101755_Bla1</string>
            <string name="Portfolio"></string>
            <double name="PackageInstrumentPrice">0</double>
            <double name="CoreInstrumentPrice">0</double>
            <string name="Underlying"></string>
            <double name="UnderlyingPrice">0</double>
            <object class="object[]" name="Rhoibush" size="0"/>
            <object class="object[]" name="Veganer" size="0"/>
            </object>
            </object>
            </object>
            <date name="Date">2012-04-16 02:00:00</date>
            </object>
            <date name="CreationTime">2012-04-16 02:08:54</date>
            <string name="Creator">xxx</string>
            <string name="Owner">xxx</string>
            <string name="State">draft</string>
            <string name="Typename">Results</string>
            <date name="UpdateTime">2012-04-16 02:08:54</date>
            <string name="Updater">xxx</string>
            <null name="ValidTo"/>
            </object>
            </rootelem>'
            )

            select xml from results where xmlexists('//object[1]' passing xml)


            I hope this is sufficient for you!?! (Don't wonder about the structure of the XML. It is used this way here since years. I know that it is not really XML schema friendly... :-o )

            Edited by: 926887 on 30-Apr-2012 07:50
            • 3. Re: Can't use index addresses
              odie_63
              Works for me on 11.2.0.2 :
              SQL> CREATE TABLE RESULTS
                2  (
                3  ID VARCHAR2(1024) NOT NULL,
                4  XML SYS.XMLTYPE NOT NULL,
                5  PRIMARY KEY (ID)
                6  )
                7  ;
               
              Table created
               
              SQL> 
              SQL> INSERT INTO Results VALUES
                2  (
                3  'ContributionReport/CF2_All/CF2_All/2012-04-16 04:00:00',
                4  '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
                5  <rootelem>
                6  <object class="com.apc.db.DbObject">
                7  <object class="api::Result" name="Content">
                8  <string name="DbReference">ContributionReport/CF2_All/CF2_All/2012-04-16 04:00:00</string>
                9  <object class="RiskReportResult" name="Result">
               10  <object class="object[]" name="RiskReportResult" size="6077">
               11  <object class="api::Parameters">
               12  <string name="PackageInstrument">101755</string>
               13  <string name="CoreInstrument">101755_Bla1</string>
               14  <string name="Portfolio"></string>
               15  <double name="PackageInstrumentPrice">0</double>
               16  <double name="CoreInstrumentPrice">0</double>
               17  <string name="Underlying"></string>
               18  <double name="UnderlyingPrice">0</double>
               19  <object class="object[]" name="Rhoibush" size="0"/>
               20  <object class="object[]" name="Veganer" size="0"/>
               21  </object>
               22  </object>
               23  </object>
               24  <date name="Date">2012-04-16 02:00:00</date>
               25  </object>
               26  <date name="CreationTime">2012-04-16 02:08:54</date>
               27  <string name="Creator">xxx</string>
               28  <string name="Owner">xxx</string>
               29  <string name="State">draft</string>
               30  <string name="Typename">Results</string>
               31  <date name="UpdateTime">2012-04-16 02:08:54</date>
               32  <string name="Updater">xxx</string>
               33  <null name="ValidTo"/>
               34  </object>
               35  </rootelem>'
               36  );
               
              1 row inserted
               
              SQL> select xml from results where xmlexists('//object[1]' passing xml);
               
              XML
              --------------------------------------------------------------------------------
              <?xml version="1.0" encoding="UTF-8" standalone='yes'?>
              <rootelem>
                <object c
               
              • 4. Re: Can't use index addresses
                Jason_(A_Non)
                Also works in 11.1.0.6
                Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
                ...<snipped>...
                
                SQL> select xml from results where xmlexists('//object[1]' passing xml);
                 
                XML
                --------------------------------------------------------------------------------
                <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
                  <rootelem>
                  <objec
                • 5. Re: Can't use index addresses
                  929890
                  Wow, that's weird!!!
                  We have Oracle 11g 11.2 running. In DbVizualizer I am using a JDBC driver version 11.1 because DbVisualizer has a problem in displaying XMLTypes with version 11.2. but even with a 11.1. version and in my Java application I still get the ORA-Error. If I don't use index addressing DbVisualizer displays XMLTypes well with the 11.1. version:
                  select xml from results where xmlexists('//object[1]' passing xml)
                  java.sql.SQLException:ORA-31065: Cannot modify read-only property [class]
                       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
                       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
                       at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
                       at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
                       at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
                       at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
                       at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
                       at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)
                       at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
                       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
                       at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
                       at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
                       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                       at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
                       at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
                       at java.lang.reflect.Method.invoke(Unknown Source)
                       at oracle.ucp.jdbc.proxy.StatementProxyFactory.invoke(StatementProxyFactory.java:230)
                       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                       at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
                       at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
                       at java.lang.reflect.Method.invoke(Unknown Source)
                       at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:44)
                       at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
                       at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:41)
                       at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
                       at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
                       at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
                       at org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:79)
                       at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:71)
                       at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:49)
                       at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
                       at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
                       at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
                       at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
                       at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
                       at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
                       at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:31)
                       at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
                       at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
                       at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
                       at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
                       at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
                       at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
                       at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
                  • 6. Re: Can't use index addresses
                    Jason_(A_Non)
                    It makes sense in a way. Odie and I were running the query through SQL*Plus so we were just dealing with the DB only. You've added in levels with DbVizualizer and a JDBC driver. If you can repeat Odie's results via SQL*Plus or some other tool (SQL Developer, PL/SQL Developer, etc) then you have eliminated your DB installation as the cause of the issue. I'm guessing the issue is probably in the JDBC driver since you mention your Java app has the same problem.

                    What that could be I don't know as I rarely work with JDBC.

                    So by simply removing the [1], your Java app avoids the ORA error? Interesting because
                    http://psoug.org/oraerror/ORA-31065.htm
                    I'm not sure why adding [1] would cause a non-read operation to be performed.
                    • 7. Re: Can't use index addresses
                      929890
                      That was my guess, too, A_Non.
                      Unfortunately I do not have SQL*Plus. Do you know where I can report this possibly bug in the JDBC driver?
                      • 8. Re: Can't use index addresses
                        Jason_(A_Non)
                        I would assume over in the {forum:id=1050} forum

                        Also, you can find a SQL*Plus client at http://www.oracle.com/technetwork/topics/winsoft-085727.html
                        • 9. Re: Can't use index addresses
                          929890
                          Yet I had the chance to let somone else run
                          select xml from results where xmlexists('//object[1]' passing xml);
                          through SQL*Plus but he had the same result (error message) as I did.
                          Hence, it looks as if it is NOT the JDBC driver but maybe some kind of DB configuration or so?
                          • 10. Re: Can't use index addresses
                            odie_63
                            What's your database version btw?

                            Please give all digits from : <tt>SELECT * FROM v$version;</tt>
                            • 11. Re: Can't use index addresses
                              tsuji
                              I would suggest you test/use eventually the oracle function existsNode() rather than the standard xml sql function XMLExists() where the former is born earlier and might enjoys a more thorough support with DBVisualizer in the middle.
                              select xml from results where existsNode(xml, '//object[1]')=1;
                              Change the [1] to [4] say if you want to make sure what found has at least 4 object elements (same level or not).

                              existsNode() can also use xpath functions like this (say, case for at least 4 object elements) as a variant without using position index.
                              select xml from results where existsNode(xml, 'count(//object)>=4')=1;
                              Furthermore, sometimes multiple versions are installed side-by-side on a m/c whereas sqlplus logs into one version whereas something like sql-developer logs into another. One way to investigate on windows is to check the actual services (say, with the help of services.msc) started or not, and enabled or disabled...