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?

odie_63

Never seen that error before.

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

Thanks.

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

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
 

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

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)

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.

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?

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

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?

odie_63

What's your database version btw?

Please give all digits from : <tt>SELECT * FROM v$version;</tt>