Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

670317Nov 13 2008 — edited Nov 14 2008
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.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 12 2008
Added on Nov 13 2008
2 comments
590 views