I have 3 schemas created an oracle database:
ORIGEMTAB - where there is a TABLE
ORIGIN - Where there are a synonym for ORIGEMTAB.TABELA
ODI_CONEXAO - used to make the connection with Oracle
I'm trying to reverse synonymous, connecting through to ODI_CONEXAO through the RKM Oracle, however the Queru used by ODI to do this is:
ALL_TAB_PARTITIONS from ATP
JOIN ALL_SYNONYMS SY
ON (sY.OWNER = 'ORIGIN'
and SY.SYNONYM_NAME like '%% synonymous'
and ATP.TABLE_OWNER = SY.TABLE_OWNER
and ATP.TABLE_NAME SY.TABLE_NAME =)
instr (',' | | 'AT, Q, SY, ST, T, V' | | ',', ', SY;')> 0
But there is nothing in ALL_TAB_PARTITION table, so the query returns nothing.
Someone has gone through this or know how to solve?
I am very grateful
ODI_CONEXAO should have at least a read access on the underlying objects, so the synonym and the table.
If you connect to SQL Developer or sqlplus using ODI_CONEXAO and you can do a "select * from your_synonym;", then it should be ok.
If you can't, then you probably lack of privileges.
Hope it helps.
I've done this test, I can do a select normally the synonym, the problem really is in the form that RKM is the reverse, because it uses a join with a table (ALL_TAB_PARTITION) that does not have any data on the synonym.
Already tried to change the RKM no longer do this join, however it complains that it can not insert NULL in columns and PARTITION_NAME PART_DESC table SNP_REV_PARTITION
Hopefully this leads to the answer. The query that returns the Synonyms is actually part of the "Get tables" step in RKM Oracle. The below query is the second part of a union. Does this query return what is expected?
substr('<<SYNONYM FOR TABLE: ' || T.OWNER || '.' || T.TABLE_NAME || '>> ' || tc.COMMENTS,1,250) TABLE_DESC,
From ALL_TABLES t,
Where s.OWNER = '<%=odiRef.getModel("SCHEMA_NAME")%>'
and s.SYNONYM_NAME like '<%=odiRef.getModel("REV_OBJ_PATT")%>'
and t.OWNER = s.TABLE_OWNER
and t.TABLE_NAME = s.TABLE_NAME
and tc.OWNER(+) = t.OWNER
and tc.TABLE_NAME(+) = t.TABLE_NAME
and instr(';'||'<%=odiRef.getModel("REV_OBJ_TYPE")%>'||';', ';SY;') > 0
and tp.OWNER(+) = t.OWNER
and tp.TABLE_NAME(+) = t.TABLE_NAME