Tuesday 23 October 2012

Linked Server setup with Oracle


Linked Server Setup with Oracle in SQL Server:

Suppose you want to access data from oracle or other database products using T-SQL via SQL server database Engine then you need to configure Linked Server.
You can learn about Linked Server from MSDN .
Here I show an example to setup linked server with oracle.

First install oracle client on machine where you want to configure linked server.
Go to [Oracle Client Installation Directory]\product\11.2.0\]dbhome_1\NETWORK\ADMIN
Open tnsnames.ora file.(It is Network configuration file for oracle)
Add TNS setting in this file as Example here

PROD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS =
        (PROTOCOL = TCP)
        (HOST = 192.168.10.10)
        (PORT = 1521)
      )
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PROD)
    )
  )

See more details of tnsnames.ora file.

Test oracle connection:

Open cmd
Enter SQLPLUS
Enter USERNAME:  username@PROD
Enter PASSWORD: password
Type any select query to confirm connection.

Now come on SQL Server.
In object explorer, Expand Server Object tree Node and see linked server

Right click on linked server and select New Linked Server and Enter required information


Select Security option and enter credentials


Click Ok.
If all things are right linked server configured now.

Now Test your linked server.
Run query as
SELECT a.*
FROM OPENQUERY(linkedservername,'SELECT *from oracledatabasename.table_or_view') a;
Basic Syntax of OPENQUERY

OPENQUERY ( linked_server ,'query' )


Using OpenQuery you can INSERT,DELETE , UPDATE,DELETE.


Example Select Query:
SELECT a.*
FROM OPENQUERY(PRODNEW,'SELECT *from Apps.customer_details') a;

Example INSERT Query:

INSERT OPENQUERY (PRODNEW, ‘SELECT ID,name FROM Apps.customer_details’)
VALUES ('C121’,’Uma Shankar');

Example UPDATE Query:

UPDATE OPENQUERY (PRODNEW, 'SELECT name FROM Apps.customer_details WHERE id = ''C121''')
SET name = 'Uma Shankar Patel';

Example DELETE Query:

DELETE OPENQUERY (PRODNEW, 'SELECT ID FROM Apps.customer_details WHERE ID = ''C121''');


This query fetch data from oracle via SQL server database engine.
It is the little about linked server.I have learned this when my client require to develop a web site that fetch data from sql server but customer data of client comes from oracle.

  • Insert data from Oracle to SQL Server over linked server 


INSERT INTO sqldatabasename.dbo.tablename

(
col1,
col2,
col3
)
SELECT a.*
FROM OPENQUERY(likedservaername,'SELECT
col1,
col2,
col3
from oracletablename') a;

  • Insert data from SQL server to Oracle over linked server 
INSERT INTO OPENQUERY(linkedservername,'select
col1,
col2,
col3                   
from oracletablename')

   SELECT
   col1,
   col2,
   col3
  FROM sqldatabase.dbo.tablename;

  • Delete data from Oracle over linked server
DELETE OPENQUERY (linkedserver, 'Select *from oracetablename');   

In the same way you can create linked server with oracle,mysql and other rdbms and can use above sample query as required. 


  • Update oracle table from SQL Server


    UPDATE u
    SET u.oraclefieldname = ‘value’
    from
    openquery(likedservername,'select *from oracletable') as u
    where u.hdr_id = @rid














17 comments:

  1. Just want to say your article is as amazing. Τhe сlearnesѕ to
    yοur роst is ѕimρly ѕpectacular and i can suppose
    yоu are knowledgeable in thiѕ subϳеct.
    Well alοng with youг permiѕѕіon let
    me to snatch уour feed to keep updatеd wіth imminent рost.
    Τhanks one millіon anԁ please carry on
    the rewarding work.

    Looκ іnto my site :: cheap rv rentals indiana

    ReplyDelete
  2. This opens a HUGE security hole. Anyone accessing the data stored in Oracle accesses it under the security context of "username"

    ReplyDelete
  3. VERY NICE POST

    ReplyDelete
  4. Hi,

    How to fetch the values from the linked sever based on some condition in where clause?For example to fetch all the values with Date as '24/JUN/2013'
    I tried but it doesnt allow special chars in the where clause! Please help

    ReplyDelete
    Replies
    1. It is very good question.Let me explain with example

      select
      a.Customer_Number,
      a.Customer_Name
      from openquery(proderp,'select * from Apps.Customer_Deatils')a

      where Convert(varchar,a.Creation_Date,101)=Convert(varchar,GETDATE(),101)

      There are some important points as
      1. a.Customer_Number ,Customer_Name are field of Oracle table that I am selected in "select * from Apps.Customer_Deatils".
      2. You can use Oracle field in where clause as I used Creation date.
      3. If you are comparing SQL Server date with Oracle date,You should CAST date,because sometimes it is not compatible to each other.

      Delete
    2. Hi can you please help me

      Im trying to querying from sql to Oracle database but I want to use parameters

      I declared the parameter @fecha in sql and I want to use it in the oracle select

      select * from openquery(linkserver,'select * from oracletable where fecha=@fecha')

      Is this posible? Any suggestions?

      I´ll really appreciated your help

      Delete
    3. Hi everyone
      Im trying to querying from Sql Server 2008 to a linked server in Oracle.
      But I want to use sqlparameters

      For example, from sqlserver I´m trying

      select * from openquery(linkedserver,'select * from oracletable where fecha=@fecha')

      Is this posible? Any suggestions?

      I´ll really appreciated

      Delete
    4. Hi can you please help me

      Im trying to querying from sql to Oracle database but I want to use parameters

      I declared the parameter @fecha in sql and I want to use it in the oracle select

      select * from openquery(linkserver,'select * from oracletable where fecha=@fecha')

      Is this posible? Any suggestions?

      I´ll really appreciated your help

      Delete

    5. you can do this by using alias as below
      declare @param1 varchar(15)
      set @param1='value of parameter'
      select * from openquery(linkedservername,'select * from oracleTable') a where a.id=@param1

      a.id is your column of oracle table.

      Delete
  5. Hi , I have to call the storeproc with one input param and three output param.can any one help with me

    StoreProcName(Param1 IN VARCHAR2,Param2 OUT INT,Param3 OUT INT, Param4 OUT DATE) - this is the oracle store proc which needs to be run in the sql with the sqlserver, please help

    ReplyDelete
    Replies
    1. Use

      EXECUTE ('begin my1.spProc(?,?,?,?); end;', @param_in_1, @param_in_2, @param_out_3 OUTPUT, @param_out_4 OUTPUT) AT DBLINK_NAME;


      Some times an error generates regarding RPC.
      In that case , go to properties of Linked Server and under Server Option set RPC and RPC out to true.

      Delete
  6. Hi , I have to call the storeproc with one input param and three output param.can any one help with me

    StoreProcName(Param1 IN VARCHAR2,Param2 OUT INT,Param3 OUT INT, Param4 OUT DATE) - this is the oracle store proc which needs to be run in the sql with the sqlserver, please help

    ReplyDelete
    Replies
    1. Did you create Linked Server with Oracle.
      If not try above steps and use
      EXECUTE ('begin my1.spProc(?,?,?,?); end;', @param_in_1, @param_in_2, @param_out_3 OUTPUT, @param_out_4 OUTPUT) AT DBLINK_NAME;

      Delete
  7. Hi, How can we Auto Increment value in Oracle table where insertion is happening through SQL Server Linked Server?

    ReplyDelete
    Replies
    1. Hi Jitender,
      Create sequence in oracle for identity column and then use in Query on linked server..
      Try somthing like this if possible becoz i did'nt try this
      INSERT INTO OPENQUERY(linkedservername,'select dept_seq.NEXTVAL,
      col1,
      col2,
      col3
      from oracletablename')

      SELECT
      col1,
      col2,
      col3
      FROM sqldatabase.dbo.tablename;

      other wise
      use
      DECLARE @unq_id int;
      SELECT a.id in @unq_id
      FROM OPENQUERY(PRODNEW,'SELECT MAX(id) from oracletable') a;

      INSERT INTO OPENQUERY(linkedservername,'select id,
      col1,
      col2,
      col3
      from oracletablename')

      SELECT
      (@unq_id +1),
      col1,
      col2,
      col3
      FROM sqldatabase.dbo.tablename;




      Delete
  8. Thanks Uma Shankar, But I have one more query for you :). How can I pass linked server name dynamically to OPENQUERY? I have three databases in Oracle local, stage and production. So I have created three linked server but I am getting problem of passing Linked Server name dynamically to OpenQuery so that I can push data in one of the Oracle database based on a condition.

    So far I have tried to achieve my objective with something like this by passing linked server as variable:


    DECLARE @LinkedServerName VARCHAR(100);
    SET @LinkedServerName='StageLinkedServer';

    OPENQUERY(@LinkedServerName,'SELECT MAX(id) from oracletable') a;

    and later I have tried by passing linked server name by direct select query:

    OPENQUERY(SELECT name FROM sys.servers where server_id=3,'SELECT MAX(id) from oracletable') a;

    But none of them worked for me.

    ReplyDelete
    Replies
    1. Use Dynamic SQL QUERY as
      http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/

      Delete

Please leave a comment for this post