Release 8.1.6 for Windows NT
Part Number A82868-01
 Library |
 Contents |
 Index |
| Oracle Transparent Gateway Microsoft SQL Server Administrator's Guide Release 8.1.6 for Windows NT Part Number A82868-01 |
|
After the gateway is installed and configured, you can use the gateway to access Microsoft SQL Server data, pass Microsoft SQL Server commands from applications to the Microsoft SQL Server database, perform distributed queries, and copy data.
This chapter contains the following sections:
To access Microsoft SQL Server data through the gateway, complete the following steps on the Oracle database server:
SQL> CREATE DATABASE LINK MSQL 2 CONNECT TO user_id IDENTIFIED BY password
3 USING 'tg4msql'
where:
SQL> SELECT * FROM "EMP"@MSQL
SQL> SELECT * FROM "CORPDATA"."EMP"@MSQL
The ORACLE user must have the appropriate Microsoft SQL Server privileges to access the CORPDATA.EMP table.
SQL> UPDATE "EMP"@MSQL SET "SAL"="SAL"*1.10
The gateway rewrites SQL statements when the statements need to be translated or post-processed.
For example, consider a program that requests the following from the Microsoft SQL Server database:
SELECT "COL_A" FROM "test"@MSQL WHERE "COL_A" = INITCAP('jones');
The Microsoft SQL Server database does not recognize INITCAP, so the Oracle database server does a table scan of test and filters the results locally. The gateway rewrites the SELECT statement as follows:
SELECT "COL_A" FROM "test"@MSQL
The results of the query are sent to the gateway and are filtered by the Oracle database server.
Consider the following UPDATE request:
UPDATE "test"@MSQL WHERE "COL_A" = INITCAP('jones');
In this case, the Oracle database server and the gateway cannot compensate for the lack of support at the Microsoft SQL Server side, so an error is issued.
If you are performing operations on large amounts of data stored in the Microsoft SQL Server database, keep in mind that some functions require data to be moved to the integrating Oracle database server before processing can occur.
The gateway converts Microsoft SQL Server data types to Oracle data types as follows:
You can provide complete data location transparency and network transparency by using the synonym feature of the Oracle database server. When a synonym is defined, you do not have to know the underlying table or network protocol. A synonym can be public, which means that all Oracle users can refer to the synonym. A synonym can also be defined as private, which means every Oracle user must have a synonym defined to access the Microsoft SQL Server table.
The following statement creates a system wide synonym for the EMP table in the schema of user ORACLE in the Microsoft SQL Server database:
SQL> CREATE PUBLIC SYNONYM EMP FOR "ORACLE"."EMP"@MSQL
The gateway can pass Microsoft SQL Server commands or statements from the application to the Microsoft SQL Server database using the DBMS_HS_PASSTHROUGH package.
Use the DBMS_HS_PASSTHROUGH package in a PL/SQL block to specify the statement to be passed to the Microsoft SQL Server database, as follows:
DECLARE num_rows INTEGER; BEGIN num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@MSQL('command'); END; /
Where command cannot be one of the following:
The DBMS_HS_PASSTHROUGH package does support passing bind values and executing SELECT statements.
Using the procedural feature, the gateway can execute stored procedures that are defined in the Microsoft 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 gateway.
Standard PL/SQL statements are used to execute a stored procedure. 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 in the initialization parameter file.
|
See Also:
For information about editing the initialization parameter file, see "Task 2: Customize the Initialization Parameter File". For information about the HS_FDS_PROC_IS_FUNC parameter, see Appendix C, "Heterogeneous Services Initialization Parameters". |
In the following example, the employee name JOHN SMYTHE is passed to the Microsoft SQL Server stored procedure REVISE_SALARY. The stored procedure retrieves the salary value from the Microsoft 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';
REVISE_SALARY@MSQL(INPUT,RESULT);
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; the Microsoft SQL Server DATETIME data type, however, is not supported.
Stored procedures that return a result set are executed. However, any result sets are skipped and only output parameters and return values are returned.
Oracle Transparent Gateway technology enables the execution of distributed queries that join the Oracle database server and the Microsoft SQL Server database, and any other data store for which Oracle Corporation provides a gateway. These complex operations can be invisible to the users requesting the data.
The following example joins data between the Oracle database server, an IBM DB2 database, and the Microsoft SQL Server database:
SQL> SELECT O.CUSTNAME, P.PROJNO, E.ENAME, SUM(E.RATE*P."HOURS")
FROM ORDERS@DB2 O, EMP@ORACLE8 E, "PROJECTS"@MSQL P
WHERE O.PROJNO = P."PROJNO"
AND P."EMPNO" = E.EMPNO
GROUP BY O.CUSTNAME, P."PROJNO", E.ENAME
Through a combination of views and synonyms, using the following SQL statements, the process of distributed queries is transparent to the user:
SQL> CREATE SYNONYM ORDERS FOR ORDERS@DB2
SQL> CREATE SYNONYM PROJECTS FOR "PROJECTS"@MSQL
SQL> CREATE VIEW DETAILS (CUSTNAME,PROJNO,ENAME,SPEND)
AS
SELECT O.CUSTNAME, P."PROJNO", E.ENAME, SUM(E.RATE*P."HOURS")
SPEND
FROM ORDERS O, EMP E, PROJECTS P
WHERE O.PROJNO = P."PROJNO"
AND P."EMPNO" = E.EMPNO
GROUP BY O.CUSTNAME, P."PROJNO", E.ENAME
Use the following SQL statement to retrieve information from the data stores in one command:
SQL> SELECT * FROM DETAILS;
The command retrieves the following table:
CUSTNAME PROJNO ENAME SPEND
-------- ------ ----- -----
ABC Co. 1 Jones 400
ABC Co. 1 Smith 180
XYZ Inc. 2 Jones 400
XYZ Inc. 2 Smith 180
When the gateway is configured as COMMIT_CONFIRM, it is always the commit point site when the Microsoft SQL Server database is updated by the transaction, regardless of the HS_COMMIT_POINT_STRENGTH setting. The Oracle database server commits the unit of work in the Microsoft SQL Server database after verifying that all Oracle databases in the transaction have successfully prepared the transaction.
Only one gateway can participate in an Oracle two-phase commit transaction. Two-phase commit transactions are recorded in the HS_TRANSACTION_LOG table that is created when configuring the gateway.
Because the HS_TRANSACTION_LOG table is used to record the status of a gateway transaction, the table must reside in the database where the Microsoft SQL Server update takes place.
Updates to the HS_TRANSACTION_LOG table cannot be part of an Oracle distributed transaction.
Use the SQL*Plus COPY command to copy data from the local database to the Microsoft SQL Server database. The syntax is as follows:
COPY FROM username/password@db_name
INSERT destination_table USING query
The following example selects all rows from the local Oracle EMP table, inserts them into the EMP table on the Microsoft SQL Server database, and commits the transaction:
SQL> COPY FROM SCOTT/TIGER@ORACLE8 -
> INSERT SCOTT.EMP@MSQL -
> USING SELECT * FROM EMP
The COPY command supports APPEND, CREATE, INSERT, and REPLACE options. However, INSERT is the only option supported when copying to Microsoft SQL Server. The SQL*Plus COPY command does not support copying to tables with lowercase table names. Use the following PL/SQL syntax with lowercase table names:
DECLARE v1 oracle_table.column1%TYPE; v2 oracle_table.column2%TYPE; v3 oracle_table.column3%TYPE; . . . CURSOR cursor_name IS SELECT * FROM oracle_table; BEGIN OPEN cursor_name; LOOP FETCH cursor_name INTO v1, v2, v3, ... ; EXIT WHEN cursor_name%NOTFOUND; INSERT INTO destination_table VALUES (v1, v2, v3, ...); END LOOP; CLOSE cursor_name; END;
/
The following Oracle SQL INSERT statement is not supported for copying data from the Oracle database server to Microsoft SQL Server:
INSERT INTO table_name SELECT column_list FROM table_name
For example, consider the following statement:
SQL> INSERT INTO MSQL_TABLE SELECT * FROM MY_LOCAL_TABLE
The statement returns the following error message:
ORA-2025: All tables in the SQL statement must be at the remote database
The CREATE TABLE command lets you copy data from a Microsoft SQL Server database to the Oracle database server. To create a table on the local database and insert rows from the Microsoft SQL Server table, use the following syntax:
CREATE TABLE table_name AS query
The following example creates the table EMP in the local Oracle database and inserts the rows from the EMP table of the Microsoft SQL Server database:
SQL> CREATE TABLE EMP AS SELECT * FROM SCOTT."EMP"@MSQL
Alternatively, you can use the SQL*Plus COPY command to copy data from the Microsoft SQL Server database to the Oracle database server.
The Distributed Access Manager is used for monitoring distributed sessions that involve non-Oracle systems. The tool is distributed and installed with Oracle Enterprise Manager and runs as a standalone application.
Start the Distributed Access Manager as follows:
oemapp damgr
Using Distributed Access Manager requires connecting to an instance of an Oracle database. Once connected, the tool displays all of the gateways currently registered with the server. For each active gateway, runtime information about the gateway is displayed. It contains the machine on which the gateway runs, its process ID, and the operating system user who started the gateway. The modules supported in the gateway are also listed.
In addition, data about the current sessions through this gateway are available. Sessions are tied to the gateway by the gateway's class and instance ID, which uniquely identify the gateway. The parent client session of the distributed session is described in terms of the client program, the user/schema that owns the session, the session ID, the status, and the terminal.
Information about the database link that was used for establishing the distributed connection, the startup time, and the set of initialization parameters used for the session is also available.
All of the runtime information is derived from dynamically updated V$ tables. The Distributed Access Manager has a refresh capability available through the menu and toolbar that allows users to rerun queries if necessary and update the data. When the data is refreshed, the tool verifies that the set of registered agents remains the same. If it is not, the global view is updated.
|
 Copyright © 2001 Oracle Corporation. All Rights Reserved. |
|