The “what’s new” document states that DS 4.0 finally supports Oracle (and DB2) synonyms (section 2.9 “Source and target support” on page 15). I can’t seem to find/figure out how to import the metadata using an Oracle Synonym. It won’t accept the @xyz dblink parameter and doesn’t find it just using the synonym name.
Anybody done that yet? Is it too obvious for me to see?
petersjd (BOB member since 2003-11-21)
Update - I can browse the catalog in DS and find the synonyms in “PUBLIC”, but it won’t let me import.
petersjd (BOB member since 2003-11-21)
what is the error you are getting ?
is the synonym for a table or vew or another synonym ?
is the synonym created using DB Link ?
manoj_d (BOB member since 2009-01-02)
We don’t have rights to the linked objects, though indications are that they are tables. From an SQL window, we can read the data from the synonyms simply with a “select * from synonym_name”, but seem to have only select rights and can’t see the structure or anything else. In the DS Designer metadata browser, all of the synonyms show up in the list of objects with type “SYNONYM”, but when we right click and import, we get a “not found” error.
Is there something we need to do in the data store definition that enables importing metadata on a synonym?
petersjd (BOB member since 2003-11-21)
synonym doesn’t have any metadata so for importing the metadata of the synonym DS will first find the underlying object for which this synonym is created and get the metadata from the actual object (using the ALL_* views)
have you tried importing the synonym using import by name ?
manoj_d (BOB member since 2009-01-02)
Yes, same error on "import by name as with a right click/import. Error message attached.
SYNERR.doc (36.0 KB)
petersjd (BOB member since 2003-11-21)
can you send me the output of the following Query (use the same username that you are using in datastore)
select synonym_name, owner, table_owner, table_name, db_link from all_synonyms where synonym_name =
manoj_d (BOB member since 2009-01-02)
Does anybody has any hint about this, I can see my development connection is using the synonyms as tables in it.
I have just created another connection for test database and cannot import the synonyms from this conneciton. Same connection works from TOAD for these tables.
This is Oracle 11g with DS 4.0.
kzbhutta (BOB member since 2009-05-08)
Tell me the complete version…SP1 has the bug for Oracle Synonym…I would have posted it somewhere here!!!
ganeshxp 
(BOB member since 2008-07-17)
The workaround that was used in prior versions will still work here. Create a view, import the metadata, then drop the view and recreate the synonym. DS works fine with views.
However, I just tested the import against a synonym and it worked as advertised. I’m running 14.0.2.376.
eganjp (BOB member since 2007-09-12)
ADAPT01581224
Fixed for 14.0.2
ganeshxp (BOB member since 2008-07-17)
My DS version is 14.0.1.142.
I take it as applying the latest patch will fix it or do the work around as proposed.
Thanks a lot guys, you are fantastic.
kzbhutta (BOB member since 2009-05-08)
The very simple solution that I was looking for is this:
Import the table by name. You get the same error only if you do not change the owner name that is defaulted to connection name in this case.
Enter the table name, owner name and import. To get the correct owner name, run the query that is provided by manoj_d in one of the posts:
select synonym_name, owner, table_owner, table_name, db_link from all_synonyms where synonym_name = ‘’
kzbhutta (BOB member since 2009-05-08)
So you mean to say Synonym will work that way?
ganeshxp (BOB member since 2008-07-17)