0

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 ?

user6276653
  • 140
  • 1
  • 2
  • 11
  • 1
    You're showing the table being created in the SYSTEM schema, which is a bad idea if true; but as you're missing a closing parenthesis you may have edited that for posting anyway. Does the schema you're passing as `mydb` match the schema you actually created the table in; and are you passing it in uppercase? Are you connected as that same user? – Alex Poole Sep 07 '16 at 18:19
  • I corrected it in the question. I am using "SYSTEM" as user and "mydb" as schema, but getting the above mentioned exception. I am logging in as "SYSTEM". – user6276653 Sep 07 '16 at 20:10
  • 1
    If the table is owned by SYSTEM and you are connecting as SYSTEM (both bad ideas) then the path shoukd be `/SYSTEM/WAREHOUSES`. I don't understand what `mydb` is doing there. From what you've shown either that user doesn't exist, or it doesn't have a table with that name. – Alex Poole Sep 07 '16 at 20:41
  • OOPS, my mistake. Thanks a lot man. That answered everything. – user6276653 Sep 07 '16 at 23:01
  • Can you have a look at this one too ? http://stackoverflow.com/questions/39369749/import-xml-file-in-oracle-xml-db-repository .... if you can help me with this .... – user6276653 Sep 07 '16 at 23:02

0 Answers0