XML DB, JDBC and tyring to delete an XML Instance ( Row )

670317

h2. Version
Using SQL*Plus: Release 10.2.0.1.0 Express Edition and Java

h3. Settings
JBDC Settings
oracle.jdbc.driver.OracleDriver
jdbc:oracle:thin:@localhost:1521:XE

h2. Schema
I am using a register Schema

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.example.net/addressXMLSchema"
xmlns:tns="http://www.example.net/addressXMLSchema"
elementFormDefault="qualified" xmlns:xdb="http://xmlns.oracle.com/xdb"
xdb:storeVarrayAsTable="true" version="1.0">
<xs:element name="ADDRESSBOOK" type="tns:ADDRESSBOOKTYPE"
xdb:defaultTable="ADDRESSBOOK">
</xs:element>
<xs:complexType name="ADDRESSBOOKTYPE">
<xs:sequence>
<xs:element name="ADDRESS" type="tns:ADDRESSTYPE"
maxOccurs="unbounded" minOccurs="0">
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="ADDRESSTYPE">
<xs:sequence>
<xs:element name="FIRSTNAME" type="xs:string"></xs:element>
<xs:element name="LASTNAME" type="xs:string"></xs:element>
<xs:element name="MIDDLENAME" type="xs:string"></xs:element>
<xs:element name="ADDRESS1" type="xs:string"></xs:element>
<xs:element name="ADDRESS2" type="xs:string"></xs:element>
<xs:element name="CITY" type="xs:string"></xs:element>
<xs:element name="STATE" type="xs:string"></xs:element>
<xs:element name="ZIP" type="xs:string"></xs:element>
</xs:sequence>
</xs:complexType>
</xs:schema>

h2. Java

Connection conn = OracleJDBCUtil.getConnection();

String table = "ADDRESSBOOK";
String xpathWhere = "/ADDRESSBOOK/ADDRESS[FIRSTNAME=\"John\"]";

// This is close to what we want to search for a case, reasoning is
// that there is one case per file
// Maybe cases not case should be root

String statement3 = "DELETE "
+ "FROM "
+ table
+ " p "
+ "WHERE existsNode(value(p),'"
+ xpathWhere + "') = 1";
System.out.println("Statement=" + statement3);

OraclePreparedStatement stmt = (OraclePreparedStatement) conn
.prepareStatement(statement3);
ResultSet resultSet = stmt.executeQuery();
OracleResultSet orset = (OracleResultSet) resultSet;
CLOB clob;
org.w3c.dom.Document doc = null;

while (resultSet.next()) {
clob = orset.getCLOB(1);

DOMParser parser = new DOMParser();
parser.parse(clob.getCharacterStream());
doc = parser.getDocument();
System.out.println("GetXMLType.doSomething : DOM = "
+ doc.getClass().getName());
System.out.println(DOMtoString(doc));

}
resultSet.close();
stmt.close();
conn.close();


h2. Error
java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:227)
at oracle.jdbc.driver.OracleResultSetImpl.getCLOB(OracleResultSetImpl.java:1229)
at net.andrewkburger.oraclexmldb.TestDelete.test(TestDelete.java:74)
at net.andrewkburger.oraclexmldb.TestDelete.main(TestDelete.java:24)

h2. Question
I am getting the above error but it is deleting the XML Instances. Can some explain what I am doing wrong? I am a newbie with XPATH and XML DBs.

Marco Gralike

What happens if you try to do the same via using SQL*Plus only...?

670317

I figured it out. I was trying to get the result set after the delete. That is what was causing the error. Thanks for your help.