Oracle Database Gateway for SQL Server 用户指南

需积分: 5 27 浏览量更新于2024-06-25 收藏 1.64MB PDF 举报

"Oracle Database Gateway for SQL Server User’s Guide, 11g Release 2 (11.2) E12069-03" Oracle Database Gateway for SQL Server 是一个重要的工具，它允许Oracle数据库与Microsoft SQL Server数据库之间进行通信和数据交换。此用户指南详细介绍了11g Release 2 (11.2)版本的相关功能和操作方法。 1. **简介** Oracle Database Gateway 提供了一个桥梁，使得Oracle客户能够访问和操作SQL Server数据库中的数据，而无需迁移或复制数据。这有助于实现异构数据库环境中的数据集成和互操作性。 2. **安装与配置** - **系统需求**：在开始安装前，确保满足硬件和软件的要求，包括操作系统兼容性、内存和磁盘空间等。 - **安装过程**：指南会详述如何下载和安装Gateway，以及配置相关的网络设置和安全参数。 - **配置文件**：如tnsnames.ora和sqlnet.ora文件的编辑，用于定义连接信息和网络通信设置。 3. **连接管理** - **连接描述**：学习如何定义和管理到SQL Server数据库的连接，包括服务名、主机、端口和认证方式。 - **连接池**：了解如何使用Oracle的连接池技术优化资源使用，提高性能。 4. **数据访问** - **SQL语法**：Oracle客户端可以使用SQL语句查询SQL Server数据库，但需注意可能存在的语法差异。 - **PL/SQL接口**：通过PL/SQL包，如DBMS_SQLSERVER，可以直接在Oracle环境中调用SQL Server的存储过程和函数。 5. **安全性** - **身份验证**：了解如何配置Oracle Gateway以支持SQL Server的身份验证机制，如Windows集成认证或SQL Server的用户名/密码认证。 - **权限管理**：学习如何控制对SQL Server资源的访问权限，包括Oracle和SQL Server两方面的权限设置。 6. **性能优化** - **查询优化**：了解如何利用Oracle的查询优化器处理SQL Server数据库的查询，包括绑定变量和执行计划的选择。 - **网络优化**：讨论网络带宽、延迟和协议选择对性能的影响，以及相应的调整策略。 7. **故障排查** - **日志与跟踪**：学习如何启用和分析Oracle和SQL Server的日志，以诊断和解决连接问题。 - **错误处理**：提供常见错误代码及其含义，帮助用户识别和解决问题。 8. **维护与升级** - **备份与恢复**：讨论如何在Oracle Gateway环境中进行SQL Server数据库的备份和恢复操作。 - **版本升级**：了解升级Oracle Database Gateway到新版本的步骤和注意事项。 9. **许可证和法律条款** - **使用限制**：用户必须遵循提供的许可证协议，包括使用、复制和分发的限制。 - **知识产权**：Oracle对软件及其文档享有版权，未经授权的逆向工程、反编译等活动是禁止的。这个用户指南由Maitreyee Chaliha为主要作者，并有多位贡献者共同完成，旨在为用户提供全面的Oracle Database Gateway for SQL Server的使用指导。任何发现的错误，建议以书面形式向Oracle报告。

Executing Stored Procedures and Functions

2-2 Oracle Database Gateway for SQL Server User's Guide

■ SHUTDOWN

■ RELEASE

■ SAVEPOINT

■ CONNECT

■ SQL Server tool commands

The DBMS_HS_PASSTHROUGH package supports passing bind values and executing

SELECT statements.

Executing Stored Procedures and Functions

Using the procedural feature, the gateway can execute stored procedures that are

defined in the SQL Server database. It is not necessary to relink the gateway or define

the procedure to the gateway, but the procedure's access privileges must permit access

by the user that the gateway is logging in as.

Standard PL/SQL statements are used to execute a stored procedure.

The gateway supports stored procedures in three mutually exclusive modes:

■ Normal mode: Have access to IN/OUT arguments only

■ Return value mode: Have a return value for all stored procedures

■ Resultset mode: Out values are available as last result set

CHAR Semantics

This feature allows the gateway to optionally run in CHAR Semantics mode. Rather

than always describing SQL Server CHAR columns as CHAR(n BYTE), this feature

describes them as CHAR(n CHAR) and VARCHAR(n CHAR). The concept is similar to

Oracle database CHAR Semantics. You need to specify HS_NLS_LENGTH_

SEMANTICS=CHAR gateway parameter to activate this option. Refer to Appendix D

for more detail.

Multi-byte Character Sets Ratio Suppression

This feature optionally suppresses the ratio expansion from SQL Server database to

Oracle database involving multi-byte character set. By default, Oracle gateways

assume the worst ratio to prevent data being truncated or insufficient buffer size

situation. However, if you have specific knowledge of your SQL Server database and

Note: TRUNCATE cannot be used in a pass-through statement.

See Also: Oracle Database PL/SQL Packages and Types Reference and

Chapter 3, Features of Oracle Database Gateways, of Oracle

Database Heterogeneous Connectivity User's Guide for more

information about the DBMS_HS_PASSTHROUGH package.

See Also: Oracle Database Heterogeneous Connectivity User's Guide

for more information about executing stored procedures.

Remote User-defined Function Support

SQL Server Gateway Features and Restriction 2-3

do not want the expansion to occur, you can specify HS_KEEP_REMOTE_COLUMN_

SIZE parameter to suppress the expansion. Refer to Appendix D for more detail.

IPv6 Support

Besides full IPv6 support between Oracle databases and the gateway, IPv6 is also

supported between this gateway and SQL Server database. Refer to the HS_FDS_

CONNECT_INFO parameter in Appendix D for more detail.

Gateway Session IDLE Timeout

You can optionally choose to terminate long idle gateway sessions automatically with

the gateway parameter HS_IDLE_TIMEOUT. Specifically, when a gateway session is

idle for more than the specified time limit, the gateway session is terminated with any

pending update rolled back. Refer to the HS_IDLE_TIMEOUT parameter in

Appendix D for more detail.

Remote User-defined Function Support

User-defined functions in a remote non-Oracle database can be used in SQL

statements.

Return Values and Stored Procedures

By default, all stored procedures and functions do not return a return value to the

user. To enable return values, set the HS_FDS_PROC_IS_FUNC parameter value to

TRUE.

In the following example, the employee name JOHN SMYTHE is passed to the SQL

Server stored procedure REVISE_SALARY. The stored procedure retrieves the salary

value from the SQL Server database to calculate a new yearly salary for JOHN

SMYTHE. The revised salary returned in RESULT is used to update EMP in a table of an

Oracle database:

DECLARE

INPUT VARCHAR2(15);

RESULT NUMBER(8,2);

BEGIN

INPUT := 'JOHN SMYTHE';

RESULT := REVISE_SALARY@

MSQL

(INPUT);

See Also: Oracle Database Heterogeneous Connectivity User's Guide

for more information about executing user-defined functions on a

non-Oracle database.

See Also: Appendix D, "Initialization Parameters" for

information about both editing the initialization parameter file and

the HS_FDS_PROC_IS_FUNC parameter.

Note: If you set the HS_FDS_PROC_IS_FUNC gateway

initialization parameter to TRUE, you must change the syntax of the

procedure execute statement for all existing stored procedures.

Remote User-defined Function Support

2-4 Oracle Database Gateway for SQL Server User's Guide

UPDATE EMP SET SAL = RESULT WHERE ENAME =: INPUT;

END;

The procedural feature automatically converts non-Oracle data types to and from

PL/SQL data types.

Result Sets and Stored Procedures

The Oracle Database Gateway for SQL Server provides support for stored procedures

which return result sets.

By default, all stored procedures and functions do not return a result set to the user.

To enable result sets, set the HS_FDS_RESULTSET_SUPPORT parameter value to

TRUE.

When accessing stored procedures with result sets through the Oracle Database

Gateway for SQL Server, you will be in the sequential mode of Heterogeneous

Services.

The Oracle Database Gateway for SQL Server returns the following information to

Heterogeneous Services during procedure description:

■ All the input arguments of the remote stored procedure

■ None of the output arguments

■ One out argument of type ref cursor (corresponding to the first result set returned

by the stored procedure)

Client programs have to use the virtual package function DBMS_HS_RESULT_

SET.GET_NEXT_RESULT_SET to get the ref cursor for subsequent result sets. The last

result set returned is the out argument from the procedure.

The limitations of accessing result sets are the following:

■ Result sets returned by a remote stored procedure have to be retrieved in the order

in which they were placed on the wire

■ On execution of a stored procedure, all result sets returned by a previously

executed stored procedure will be closed (regardless of whether the data has been

completely retrieved or not)

In the following example, the SQL Server stored procedure is executed to fetch the

contents of the emp and dept tables from SQL Server:

create procedure REFCURPROC (@arg1 varchar(255), @arg2 varchar(255) output)

select @arg2 = @arg1

See Also: Appendix D, "Initialization Parameters" for information

about both editing the initialization parameter file and the HS_

FDS_RESULTSET_SUPPORT parameter. For further information

about Oracle support for result sets in non-Oracle databases see

Oracle Database Heterogeneous Connectivity User's Guide.

Note: If you set the HS_FDS_RESULTSET_SUPPORT gateway

initialization parameter to TRUE, then you must change the syntax

of the procedure execute statement for all existing stored

procedures, else errors will occur.

剩余81页未读，继续阅读

weixin_40191861_zj

粉丝: 83
资源: 1万+

Oracle Database Gateway for SQL Server 用户指南

数据库-oracle.pdf

oracle-sql.pdf

oracle 连sql server,连接到 Oracle (OracleToSQL)

oracle数据库里连接sql server数据库

oracle dblink到mysql 28545,ORACLE 创建DBlink访问SQL SERVER数据库

SQL server访问Oracle其中一个表

PLSQL连接远程服务器数据库

gateway怎么安装

oracle 可以连接mysql的数据吗

mysql和oracle可以互通读表吗

最新资源