Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

error loading mutiple xml files in odi 11g

user6322937Sep 13 2013
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

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 11 2013
Added on Sep 13 2013
0 comments
868 views