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

codeproject

Basics of Triggers in SQL Server

Querying Microsoft SQL Server : Basics of Triggers in SQL Server: SQL Triggers:  A trigger is a procedural code like stored procedure that is automatically executed on certain actions (like insert, de...

Basics of Indexes in SQL Server

Querying Microsoft SQL Server : Basics of Indexes in SQL Server: Indexes in SQL Server: If you see a book, in last of book an index is provided that has some titles and page numbers. You don’t need ...

Connection String from Visual Studio

Querying Microsoft SQL Server : Connection String from Visual Studio: Connection String: A connection string contains connection information need to connect with data source.You can connect your project with ...

Data Types in SQL Server

Querying Microsoft SQL Server : Data Types in SQL Server: Data Type Description Remarks BIGINT Used for large Integer data.(Range -2^63  to 2^63-1) It takes 8 B...

Functions in SQL Server

 Functions in SQL Server: Functions in SQL Server In SQL Server functions are subrotienes that encapsulate a group of T-SQL statements for reuse.SQL Server pro...

Error Handling in T-SQL

 Error Handling in T-SQL: Error handling in T-SQL: Error handling in T-SQL is similer to other programming languages as C#,C++,Java.A group of statements  encl...

Defining Variables

Querying Microsoft SQL Server : Defining Variables: Defining Variables in SQL Server: Like other programming languages T-SQL allows to defining your variables. A variable is known as lo...

Querying Microsoft SQL Server : Certifications

Querying Microsoft SQL Server : Certifications: If you  would like to obtain a certification for SQL Server. The latest certification Microsoft offer for SQL Server are the MC...

Stored Procedures in SQL Server

Querying Microsoft SQL Server : Stored Procedures in SQL Server: Stored Procedure: Stored Procedure is a powerful part of SQL Server. It is group of T-SQL statements compiled into single execution ...

Control Statements in T-SQL

Querying Microsoft SQL Server : Control Statements in T-SQL: Control statements are used to control execution flow within a T-SQL.Here I provides some example on control statements. ·          ...

Create database to test Queries of this blog..........

Run this script:

CREATE DATABASE dbTest;

CREATE TABLE Person

(

pid INT IDENTITY(1,1),

name VARCHAR(200) NOT NULL,

dob DATETIME NULL,

occupation varchar(100) NULL,

address VARCHAR(200),

city VARCHAR(100),

state VARCHAR(100),

isBlock BIT,

PRIMARY KEY(PID)

)

USE [dbTest]

GO

/****** Insert Data into Person table   ******/

SET IDENTITY_INSERT [dbo].[Person] ON

INSERT [dbo].[Person] ([pid], [name], [dob], [occupation], [address], [city], [state], [isBlock]) VALUES (1, N'Abhishek', CAST(0x00007A1B00000000 AS DateTime), N'Student', N'E-56', N'Delhi', N'Delhi', 0)

INSERT [dbo].[Person] ([pid], [name], [dob], [occupation], [address], [city], [state], [isBlock]) VALUES (2, N'Ram', CAST(0x00006E2500000000 AS DateTime), N'DBA', N'PritVihar', N'Lucknow', N'U.P.', 0)

INSERT [dbo].[Person] ([pid], [name], [dob], [occupation], [address], [city], [state], [isBlock]) VALUES (3, N'Abhijit', CAST(0x00007E8300000000 AS DateTime), N'J.DBA', N'', NULL, NULL, 1)

SET IDENTITY_INSERT [dbo].[Person] OFF

Welcome Querying SQL.

Hi,
I extend a hearty welcome on my Blog.I hope this blog be helpful for those who preparing Microsoft SQL Server certifications and also for those who are hungry to learn querying SQL server. 



Links:

Y