Compartilhar via

Linked server SQL <->Oracle and a revelation about using Oracle Synonyms in Four part query

  • Artigo

I was working on one the Enterprise customer Incident. The issue at hand was that we were unable to use Oracle Synonyms in a Four part naming query against a SQL-Oracle Linked Server. To probe the scenario, I had to set up a repro locally.

Step 1 : To Create a test user on Oracle

Login using a SYSTEM account ( This would preferably be a SYSADMIN account ). In this scenario, Oracle10g host name was ORCL.

Create user TEST identified by TEST ;

Grant connect, resource to TEST ;

Grant create synonym to TEST ;

Alter user SYSTEM account unlock;

Connect SYSTEM / ******* @ ORCL

Grant select on EMP to TEST ;

Connect TEST / TEST @ ORCL

Create synonym EMP FOR SYSTEM.EMP ;

 

Step 2 : Creating Linked Server in SQL Server 2005

Here in my example , I'm calling the Linked Server as TIGER. Specify the product name as Oracle and Data source as ORCL.

 

In the security page of the linked server, choose the option "Be made using this security context" and specify the login of the Test user created previously.

 

To test the issue , I created a table EMP in Oracle with the following contents:

 

Once this done, we start querying using four part query:

 

Further continuing, when we use Oracle Synonym in our four part query we observe the following:

 Full Error message is:

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "OraOLEDB.Oracle" for linked server "TIGER" does not contain the table ""Test"."EMP"". The table either does not exist or the current user does not have permissions on that table.

After spending some quality time doing research on this issue, found that this is a limitation in the Linked Server infrastructure when using Oracle Synonym in four part query.

Found that open query works fine using the Synonym. The other method to execute a four part query would be to use Oracle views instead of Synonyms.

Hence dropped the synonym which I had created earlier by running the following query :

 Drop synonym EMP ;

Next created a view by running the following query :

Create view EMP as select * from SYSTEM.EMP;

 

Next when i tried to run the four part query again but instead of oracle synonym , I used the Oracle View and the result was as below:

Conclusion :  

Oracle synonyms will not work with four part queries in a SQL-Oracle linked server.  Workaround is to use Open query or to use Oracle views instead of synonyms. A revelation after a long overhaul of trials and errors.

Comments

  • Anonymous
    April 09, 2011
    THANK YOU!  THANK YOU!  THANK YOU!  This worked like a CHAMP!
  • Anonymous
    April 19, 2011
    We had this very same issue and found that as long as the synonym owner was the same length as the table owner it worked ok.We had tables owned by a 7 character length owner name i.e. BOXNAME and synonym owner was also 7 characters in length i.e. FOXFOOT then the following query would workselect * from TIGER..FOXFOOT.table_nameWhere the synonym was set up like soCREATE SYNONYM FOXFOOT.table_name for BOXNAME.table_name;HTH
  • Anonymous
    May 29, 2013
    All the trick is just making the table name "UPPER" case.I did that and it work fine.....
  • Anonymous
    July 13, 2014
    Nice post. Thanks for helping me solve a query issue i had
  • Anonymous
    September 03, 2014
    I feel the issue can be solved as below , I could see this is due to case sensitivityselect * from DBLINK1..SCHEMA.TABLENAME – Works Correctlyselect * from DBLINK1..schema.tablename – Doesn’t Work
  • Anonymous
    July 09, 2015
    This solved my issue perfectly. Thanks for the article