ODI Error on loading XML tables: java.sql.SQLException: ORA-00926: missing VALUES keyword

(Doc ID 2287052.1)

Last updated on JULY 27, 2017

Applies to:

Oracle Data Integrator - Version 11.1.1.6.0 and later
Information in this document applies to any platform.

Symptoms

There is an Oracle Data Integrator (ODI) job that loads data from Oracle tables to XML tables using knowledge modules LKM SQL to SQL and IKM XML Control Append. The XML tables are created in an external database schema using a XML JDBC driver properties parameter to specify the settings file.

When running the job with multiple standalone agents, the below error occurs in the Integration task at the 'Insert new lines' step even though the executed SQL code is correct:

ODI-1228: Task Test xml (Integration) fails on the target XML connection XML.
Caused By: java.sql.SQLException: ORA-00926: missing VALUES keyword
  at com.sunopsis.jdbc.driver.xml.SnpsXmlPreparedStatementRedirector.execute(SnpsXmlPreparedStatementRedirector.java:211)
  at com.sunopsis.jdbc.driver.xml.SnpsXmlPreparedStatement.execute(SnpsXmlPreparedStatement.java:48)
  at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:163)
  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:2913)
  at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java:2625)
  at com.sunopsis.dwg.dbobj.SnpSessStep.treatAttachedTasks(SnpSessStep.java:577)
  at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java:468)
  at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java:2128)
  at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$2.doAction(StartSessRequestProcessor.java:366)
  at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:216)
  at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.doProcessStartSessTask(StartSessRequestProcessor.java:300)
  at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$0(StartSessRequestProcessor.java:292)
  at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:855)
  at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:126)
  at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:82)
  at java.lang.Thread.run(Thread.java:662)

JDBC Configuration Details:

JDBC Driver - com.sunopsis.jdbc.driver.xml.SnpsXmlDriver
JDBC URL - jdbc:snps:xml?d=D:\temp\TEST_Billing.xsd&s=XML_INVOICE&ni=false&id_length=255&re=Batch&dp=xmlinvoice.properties

Sample xmlinvoice.properties file with dedicated schema for XML tables (ODI_TEST_XML). In this schema, XML tables will be prefixed with XML_INVOICE. For example, XML_INVOICE_TABLE1, XML_INVOICE_TABLE2 etc.

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:local
user=ODI_TEST_XML
password=[encrypted_password]
schema=ODI_TEST_XML
drop_on_connect=Y
create_tables=AUTO
create_indexes=Y
truncate_before_load=N
ids_in_db=Y
drop_tables_on_drop_schema=Y
use_prepared_statements=Y
use_batch_update=Y
batch_update_size=30
commit_periodically=Y
num_inserts_before_commit=1000
reserve_chars_for_column=3
reserve_chars_for_table=3

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms