user6276653
user6276653

Reputation: 140

Accessing XML using XQuery (within XQJ code) from a column in database table

I have created the following table in my locally installed Oracle database :-

CREATE TABLE "SYSTEM"."WAREHOUSES" 
   (    "WAREHOUSE_ID" NUMBER NOT NULL ENABLE, 
    "WAREHOUSE_SPEC" CLOB, 
     CONSTRAINT "WAREHOUSES_PK" PRIMARY KEY ("WAREHOUSE_ID");

It as 4 rows with primary keys 1,2,3 and 4.

Now I have written the following Java code to access the "WAREHOUSE_SPEC" column (which contains an XML file for every row) :-

import oracle.xml.xquery.xqjdb.OXQDDataSource;

import javax.xml.xquery.XQItemType;
import javax.xml.xquery.XQResultSequence;
import javax.xml.xquery.XQConnection;
import javax.xml.xquery.XQPreparedExpression;
import javax.xml.namespace.QName;

public class AccessData
{
    public static void main(String argv[])
    {
        try
        {
            OXQDDataSource oxqDS = new OXQDDataSource();
            oxqDS.setProperty("driver", "jdbc:oracle:thin");
            oxqDS.setProperty("dbusername", "SYSTEM");
            oxqDS.setProperty("dbpassword", "PWD");
            oxqDS.setProperty("dbserver", "localhost");
            oxqDS.setProperty("dbport", "1521");
            oxqDS.setProperty("serviceName", "xe");
            XQConnection conn = oxqDS.getConnection();
            XQItemType  itemTypeInt = conn.createAtomicType(XQItemType.XQBASETYPE_INT);
            XQPreparedExpression expr = conn.prepareExpression("declare variable $x as xs:int external; for $i in fn:collection('oradb:/mydb/WAREHOUSES') where $i/ROW/WAREHOUSE_ID < $x return $i/ROW/WAREHOUSE_SPEC/Warehouse");
            expr.bindInt(new QName("x"), 3, itemTypeInt);
            XQResultSequence xqSeq = expr.executeQuery();
            while (xqSeq.next()) {
                System.out.println(1);
                System.out.println (xqSeq.getItemAsString(null));
            }
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
}

Here, "mydb" is database schema and "WAREHOUSES" is table name.

The code should return 2 rows with primary keys 1 and 2, but instead, it is unable to find the table, and is giving the following error :-

javax.xml.xquery.XQException: java.sql.SQLException: ORA-00942: table or view does not exist

    at oracle.xml.xquery.xqjimpl.OXQDSequence.next(OXQDSequence.java:421)
    at example1.main(AccessData.java:33)
Caused by: java.lang.RuntimeException: java.sql.SQLException: ORA-00942: table or view does not exist

    at oracle.xml.xquery.xqjimpl.xqjdbinfrastructure.OXQDXDBConnExpr.prepare(OXQDXDBConnExpr.java:367)
    at oracle.xml.xquery.xqjimpl.xqjdbinfrastructure.OXQDXDBConnExpr.access$000(OXQDXDBConnExpr.java:102)
    at oracle.xml.xquery.xqjimpl.xqjdbinfrastructure.OXQDXDBConnExpr$XDBConnIterator.Restart(OXQDXDBConnExpr.java:808)
    at oracle.xml.xquery.xqjimpl.xqjdbinfrastructure.OXQDXQuerySequence.next(OXQDXQuerySequence.java:127)
    at oracle.xml.xquery.xqjimpl.OXQDSequence.next(OXQDSequence.java:386)
    ... 1 more
Caused by: java.sql.SQLException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415)
    at oracle.xml.xquery.xqjimpl.xqjdbinfrastructure.OXQDXDBConnExpr.prepare(OXQDXDBConnExpr.java:351)
    ... 5 more

Am I doing anything wrong here ?

Upvotes: 0

Views: 109

Answers (0)

Related Questions