This discussion is archived
5 Replies Latest reply: Feb 7, 2013 3:43 PM by mRainey RSS

Reverse Synonym (RKM Oracle)

988962 Newbie
Currently Being Moderated
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:

SELECT SY.SYNONYM_NAME,
ATP.PARTITION_NAME,
HIGH_VALUE
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 =)
where
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
  • 1. Re: Reverse Synonym (RKM Oracle)
    JeromeFr Expert
    Currently Being Moderated
    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.

    Regards,
    JeromeFr
  • 2. Re: Reverse Synonym (RKM Oracle)
    988962 Newbie
    Currently Being Moderated
    Hello JeromeFr

    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
  • 3. Re: Reverse Synonym (RKM Oracle)
    JeromeFr Expert
    Currently Being Moderated
    I've nothing to test it right now, I'll try to have a look at that tomorrow.
  • 4. Re: Reverse Synonym (RKM Oracle)
    385310 Newbie
    Currently Being Moderated
    anyone?
  • 5. Re: Reverse Synonym (RKM Oracle)
    mRainey Journeyer
    Currently Being Moderated
    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?


    Select
         s.SYNONYM_NAME               TABLE_NAME,
         s.SYNONYM_NAME               RES_NAME,
         replace(s.SYNONYM_NAME,'<%=odiRef.getModel("REV_ALIAS_LTRIM")%>','')          TABLE_ALIAS,
         substr('<<SYNONYM FOR TABLE: ' || T.OWNER || '.' || T.TABLE_NAME || '>> ' || tc.COMMENTS,1,250)          TABLE_DESC,
         'SY' TABLE_TYPE,
         t.NUM_ROWS               R_COUNT,
         SUBSTR(PARTITIONING_TYPE ,1,1),
         SUBSTR(SUBPARTITIONING_TYPE,1,1)
    From     ALL_TABLES     t,
         ALL_SYNONYMS s,
         ALL_TAB_COMMENTS      tc,
         ALL_PART_TABLES tp
    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


    Regards,
    Michael Rainey

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points