SQL Server连接Oracle详细步骤

时间:2023-01-10 19:58:43


在SQL Server  Management Studio 中操作oracle数据库:

1. 配置oracle数据源

SQL Server连接Oracle详细步骤


2.在sqlserver中创建连接服务器

SQL Server连接Oracle详细步骤

SQL Server连接Oracle详细步骤

SQL Server连接Oracle详细步骤

查看sqlserver代码如下:

EXEC master.dbo.sp_addlinkedserver @server = N'ORCL_LS', @srvproduct=N'oracle', @provider=N'OraOLEDB.Oracle', @datasrc=N'orcl'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'ORCL_LS', @optname=N'use remote collation', @optvalue=N'true'


上面使用的访问接口为:Oracle Provider for OLE DB


还有一种方法创建,使用接口为: Microsoft OLE DB Provider ODBC Driver

SQL Server连接Oracle详细步骤



3.执行查询

select * from openquery(ORCL_LS,'select * from SCOTT.EMP');

出现错误:


无法对链接服务器 "ORCL_LS" 的 OLE DB 访问接口 "OraOLEDB.Oracle" 执行查询"select * from SCOTT.EMP"。


SQL Server连接Oracle详细步骤


解决:链接服务器->访问接口->OraOLEDB.Oracle->右键属性,选中 "Allow inprocess" (中文为:允许进程内)


4.其他相关操作(看图)

a.插入

SQL Server连接Oracle详细步骤


b.更新

SQL Server连接Oracle详细步骤


c.删除

SQL Server连接Oracle详细步骤


/*代码如下*/

SELECT * FROM OPENQUERY(ORCL_LS,'SELECT * FROM SCOTT.DEPT');

INSERT OPENQUERY (ORCL_LS, 'SELECT DEPTNO,DNAME,LOC FROM SCOTT.DEPT') VALUES (50,'NewDEPT','NewLOC');

UPDATE OPENQUERY (ORCL_LS, 'SELECT LOC FROM SCOTT.DEPT WHERE DEPTNO = 50') SET LOC = 'UpdateLOC';

DELETE OPENQUERY (ORCL_LS, 'SELECT DEPTNO FROM SCOTT.DEPT WHERE DEPTNO = 50');



数据库配置参考:​​Oracle Windows ODBC 数据源配置​​