error loading mutiple xml files in odi 11g

user6322937

Hi All.

I am in the need to load some xml files into an Oracle table with odi 11g.

I configured the topology using a custom xml file and I used it to reverse-engineer it, as to map the fields in the interface. To check this, I ran the interface and the table was correctly populated with the content of the xml file.

Next on, I created a package with some variables which read the xml filenames, and I modified the pointing to the topology. 

Now, my driver connection reads like this:

jdbc:snps:xml?f=#MCDS_SA.PMCDS_PATH_WORK_FOLDER\#MCDS_SA.PMCDS_ELAB_FILE_XML&d=#MCDS_SA.PMCDS_PATH_WORK_FOLDER\#MCDS_SA.PMCDS_ELAB_FILE.dtd&ro=true&dod=true&s=EVENTI_SCRAP&standalone=true

where EVENTI_SCRAP is the Physical Schema name.

I then tried to execute the package using two files: the one I used before for the reverse engineering, and another one, which has an empty field <DeviceType></DeviceType>

The package exited with the following error:

-5501 : 42501 :
java.sql.SQLException: user lacks privilege or object not found:
PMCDS_PHASE_EVENT.DEVICETYPE java.sql.SQLException: user lacks privilege or object not found: PMCDS_PHASE_EVENT.DEVICETYPE

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:1232)
at sun.reflect.GeneratedMethodAccessor19.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 $Proxy35.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.SQLDataProvider.readData(SQLDataProvider.java:81)
at oracle.odi.runtime.agent.execution.sql.SQLDataProvider.readData(SQLDataProvider.java:1)
at oracle.odi.runtime.agent.execution.DataMovementTaskExecutionHandler.handleTask(DataMovementTaskExecutionHandler.java:70)
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)
Caused by: org.hsqldb.HsqlException:
user lacks privilege or object not found: PMCDS_PHASE_EVENT.DEVICETYPE
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ExpressionColumn.checkColumnsResolved(Unknown Source)
at org.hsqldb.QueryExpression.resolve(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(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)
...27 more

if I launch the package forcing the execution order of the files so that it takes before the correct file, should not be in error, but always puts the same values (those of the correct file) so I thought we might keep in memory something.

so I then tried to insert a procedure aimed at truncating the schema, via these two
steps:

1. TRUNCATE SCHEMA PMCDS_PHASE (command on target)
2. SYNCHRONIZE ALL FROM FILE (command on source)

Next, I executed the package with the “wrong” file, and I got the following:

-5501 : 42501 : java.sql.SQLException: user lacks privilege or object not found: PMCDS_PHASE_EVENT.DEVICETYPE
java.sql.SQLException: user lacks privilege or object not found: PMCDS_PHASE_EVENT.DEVICETYPE at org.hsqldb.jdbc.Util.sqlException(Unknown Source)

So I’ve tried to insert a “IFNULL” command into the empty field (loading interface), but the error still casts.

Any help will be really appreciated!

Thanks in advance,

Rosamaria