Oracle ODI报错-ORA-00942: 表或视图不存在,求解决方案?
0
目前我在研究Oracle ODI,做了两个例子。发现其中有些问题,百思不得其解。特在此请教各位。
我做了一个很简单的例子,将本地数据库中的SCOTT用户的EMP表中数据,装载到同一数据库中另一用户的EMP中。
所有前期工作都已经完成,在执行接口时,报错了。具体错误如下:
942 : 42000 : java.sql.SQLException: ORA-00942: 表或视图不存在
java.sql.SQLException: ORA-00942: 表或视图不存在
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1086)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3057)
at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)
......................
找到执行模块的代码 是:
/* DETECTION_STRATEGY = NOT_EXISTS */
insert /*+ APPEND */ into ODITRG.I$_EMP
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
IND_UPDATE
)
select
C1_EMPNO EMPNO,
C2_ENAME ENAME,
C3_JOB JOB,
C4_MGR MGR,
C5_HIREDATE HIREDATE,
C6_SAL SAL,
C7_COMM COMM,
C8_DEPTNO DEPTNO,
'I' IND_UPDATE
from ODITRG.C$_0EMP
where (1=1)
and not exists (
select 'X'
from ODITRG.EMP T
where T.EMPNO = C1_EMPNO
and ((C2_ENAME = T.ENAME) or (C2_ENAME IS NULL and T.ENAME IS NULL))
and ((C3_JOB = T.JOB) or (C3_JOB IS NULL and T.JOB IS NULL))
and ((C4_MGR = T.MGR) or (C4_MGR IS NULL and T.MGR IS NULL))
and ((C5_HIREDATE = T.HIREDATE) or (C5_HIREDATE IS NULL and T.HIREDATE IS NULL))
and ((C6_SAL = T.SAL) or (C6_SAL IS NULL and T.SAL IS NULL))
and ((C7_COMM = T.COMM) or (C7_COMM IS NULL and T.COMM IS NULL))
and ((C8_DEPTNO = T.DEPTNO) or (C8_DEPTNO IS NULL and T.DEPTNO IS NULL))
)
整个代码中只有
ODITRG.I$_EMP,ODITRG.C$_0EMP,ODITRG.EMP
这三个表和视图,我在PL/SQL中 select 一下,发现 只有ODITRG.C$_0EMP 这个会报 表或视图不存在 的错误,其它的都是存在的表
而ODITRG.C$_0EMP这个是Synonyms(同义词),再查检了一下,是由接口中第三步执行的代码所创建的
create synonym ODITRG.C$_0EMP
for SCOTT.C$_0EMP@ORABI
现在需要用到这个东西,却找不到。请教各位大神帮忙解决呀!
我做了一个很简单的例子,将本地数据库中的SCOTT用户的EMP表中数据,装载到同一数据库中另一用户的EMP中。
所有前期工作都已经完成,在执行接口时,报错了。具体错误如下:
942 : 42000 : java.sql.SQLException: ORA-00942: 表或视图不存在
java.sql.SQLException: ORA-00942: 表或视图不存在
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:282)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:639)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:185)
at oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPreparedStatement.java:633)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1086)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:2984)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3057)
at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)
......................
找到执行模块的代码 是:
/* DETECTION_STRATEGY = NOT_EXISTS */
insert /*+ APPEND */ into ODITRG.I$_EMP
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
IND_UPDATE
)
select
C1_EMPNO EMPNO,
C2_ENAME ENAME,
C3_JOB JOB,
C4_MGR MGR,
C5_HIREDATE HIREDATE,
C6_SAL SAL,
C7_COMM COMM,
C8_DEPTNO DEPTNO,
'I' IND_UPDATE
from ODITRG.C$_0EMP
where (1=1)
and not exists (
select 'X'
from ODITRG.EMP T
where T.EMPNO = C1_EMPNO
and ((C2_ENAME = T.ENAME) or (C2_ENAME IS NULL and T.ENAME IS NULL))
and ((C3_JOB = T.JOB) or (C3_JOB IS NULL and T.JOB IS NULL))
and ((C4_MGR = T.MGR) or (C4_MGR IS NULL and T.MGR IS NULL))
and ((C5_HIREDATE = T.HIREDATE) or (C5_HIREDATE IS NULL and T.HIREDATE IS NULL))
and ((C6_SAL = T.SAL) or (C6_SAL IS NULL and T.SAL IS NULL))
and ((C7_COMM = T.COMM) or (C7_COMM IS NULL and T.COMM IS NULL))
and ((C8_DEPTNO = T.DEPTNO) or (C8_DEPTNO IS NULL and T.DEPTNO IS NULL))
)
整个代码中只有
ODITRG.I$_EMP,ODITRG.C$_0EMP,ODITRG.EMP
这三个表和视图,我在PL/SQL中 select 一下,发现 只有ODITRG.C$_0EMP 这个会报 表或视图不存在 的错误,其它的都是存在的表
而ODITRG.C$_0EMP这个是Synonyms(同义词),再查检了一下,是由接口中第三步执行的代码所创建的
create synonym ODITRG.C$_0EMP
for SCOTT.C$_0EMP@ORABI
现在需要用到这个东西,却找不到。请教各位大神帮忙解决呀!
没有找到相关结果
重要提示:提问者不能发表回复,可以通过评论与回答者沟通,沟通后可以通过编辑功能完善问题描述,以便后续其他人能够更容易理解问题.
1 个回复
jjzhangfre - 数仓、ETL 2015-11-13 回答
赞同来自: