Analytics Software

Hi,

I am trying to load an XML file from an Oracle table. The XSD has been provided (TEST_PERSON.xsd) and the target XML should have that specified structure.

But the interface fails saying:

ODI-1228: Task SrcSet0 (Loading) fails on the target XML connection XML_DS_PERSON.
Caused By: java.sql.SQLException: unknown token

Could you please help me?



I have created the topology as follows:



JDBC Url:



jdbc:snps:xml?d=C:\oracle\product\11.1.1\Oracle_ODI_1\oracledi\demo\demo\xml\TEST_PERSON.xsd&re=personnel&ro=true



I have tested the connection of the XML Data server which has shown Successful Connection.



The physical schema has been created as follows.



Physical Schema:



DATA Schema: TEST_PERSON



WORK Schema: TEST_PERSON


The model has been reversed successfully and the data stores are available in the Designer.

the knowledge modules are chosen as follows:

LKM: LKM SQL to SQL
IKM: IKM XML Control Append
CKM: CKM SQL

the Target Properties selected in the IKM are as follows:

CREATE_XML_FILE: true
XML_PATH: <default>:D:\Test_Files\<%=odiRef.getSession("SESS_NO")>.xml


While executing the interface, I have found the following err:

ODI Error:

ODI-1228: Task SrcSet0 (Loading) fails on the target XML connection PERSONAL_XML.

Caused By: java.sql.SQLException: unknown token

at org.hsqldb.jdbc.Util.sqlException(Unknown Source)

at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)

at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)

at com.sunopsis.jdbc.driver.xml.SnpsXmlConnection.prepareStatement(SnpsXmlConnection.java:1192)

at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at oracle.odi.core.datasource.dwgobject.support.OnConnectOnDisconnectDataSourceAdapter$OnDisconnectCommandExecutionHandler.invoke(OnConnectOnDisconnectDataSourceAdapter.java:200)

at $Proxy2.prepareStatement(Unknown Source)

at oracle.odi.runtime.agent.execution.sql.SQLCommand.doInitializeStatement(SQLCommand.java:83)

at oracle.odi.runtime.agent.execution.sql.SQLCommand.getStatement(SQLCommand.java:117)

at oracle.odi.runtime.agent.execution.sql.SQLCommand.getStatement(SQLCommand.java:111)

at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:158)

at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:102)

at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:1)

at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:50)

at com.sunopsis.dwg.dbobj.SnpSessTaskSql.processTask(SnpSessTaskSql.java:2906)

at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2609)

at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:537)

at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:453)

at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:1740)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:338)

at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:214)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:272)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:263)

at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:822)

at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:123)

at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)

at java.lang.Thread.run(Thread.java:662)

Caused by: org.hsqldb.HsqlException: unknown token

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.error.Error.error(Unknown Source)

at org.hsqldb.ParserBase.read(Unknown Source)

at org.hsqldb.ParserDQL.readNewSchemaObjectName(Unknown Source)

at org.hsqldb.ParserDDL.compileCreateTable(Unknown Source)

at org.hsqldb.ParserDDL.compileCreate(Unknown Source)

at org.hsqldb.ParserCommand.compilePart(Unknown Source)

at org.hsqldb.ParserCommand.compileStatement(Unknown Source)

at org.hsqldb.Session.compileStatement(Unknown Source)

at org.hsqldb.StatementManager.compile(Unknown Source)

at org.hsqldb.Session.execute(Unknown Source)

... 28 more


Related Code:

create table TEST_PERSON.C$_0PERSONNEL

(

C1_PERSONNELPK NUMERIC(10) NULL,

C2_SNPSFILENAME VARCHAR(255) NULL,

C3_SNPSFILEPATH VARCHAR(255) NULL

)

The same error comes whether I select the "Staging Area Different from Target" checkbox or not.

Another thing to note is that, there is no Oracle schema named TEST_PERSON, the code which has been generated here tries to create a table in the schema name as the name mentioned in the Work schema of the physical schema of XML data server. Even if I change the name of the work schema to an existing Oracle schema, I am getting the same error saying unknown token.


Please let me know if you have any ideas. I will appreciate any help regarding this at all very much.


Thanks and Regards,

Anindita Mitra