Oracle Transparent Gateway Microsoft SQL Server Administrator's Guide Release 8.1.6 for Windows NT Part Number A82868-01 |
| Library | Contents | Index

# 6
Case Studies

The following case studies for Microsoft SQL Server demonstrate some of the features of the Oracle Transparent Gateway. You can verify that the gateway is installed and operating correctly by using the demonstration files included on the distribution CD-ROM.

The demonstration files are automatically copied to disk when the gateway is installed.

This chapter contains the following sections:

- Case Descriptions

- CD-ROM Contents

- Demonstration Files

- Demonstration Requirements

- Creating Demonstration Tables

- Case 1: Simple Queries

- Case 2: A More Complex Query

- Case 3: Joining Microsoft SQL Server Tables

- Case 4: Write Capabilities

- Case 5: Data Dictionary Query

- Case 6: The Pass-Through Feature

- Case 7: Executing Stored Procedures

## Case Descriptions

The cases illustrate:

- A simple query (Case 1)

- A more complex query (Case 2)

- Joining Microsoft SQL Server tables (Case 3)

- Write capabilities (Case 4)

- A data dictionary query (Case 5)

- The pass-through feature (Case 6)

- Executing a stored procedure (Case 7)

## CD-ROM Contents

The distribution CD-ROM contains the following:

- Demonstration files

- One SQL script file that creates the demonstration tables and stored procedures in the Microsoft SQL Server database

- One SQL script file that drops the demonstration tables and stored procedures from the Microsoft SQL Server database

## Demonstration Files

After a successful gateway installation, use the demonstration files stored in the directory `ORACLE_HOME\tg4msql\demo` where ORACLE_HOME is the ORACLE_HOME directory under which the gateway is installed. The directory contains the following demonstration files:

bldmsql.sql | case5.sql
case1.sql | case6a.sql
case2.sql | case6b.sql
case3.sql | case7.sql
case4a.sql | dropmsql.sql
case4b.sql |
case4c.sql |

## Demonstration Requirements

The case studies assume these requirements have been met:

- The gateway demonstration tables and stored procedures are installed in the Microsoft SQL Server database

- The Oracle server has an account named SCOTT with a password of TIGER

- The Oracle server has a database link called GTWLINK (set up as public or private to the user SCOTT) which connects the gateway to a Microsoft SQL Server database as SCOTT with password TIGER2

  For example, you can create the database link as follows:

      SQL> CREATE DATABASE LINK GTWLINK CONNECT TO SCOTT

        2    IDENTIFIED BY TIGER2 USING 'GTWSID';

- Net8 is configured correctly and running

## Creating Demonstration Tables

The case studies are based on the GTW_EMP, GTW_DEPT, and GTW_SALGRADE tables and the stored procedures InsertDept and GetDept. If the demonstration tables and stored procedures have not been created in the Microsoft SQL Server database, use the `bldmsql.sql` script to create them. Enter the following:

    > isql -USCOTT -PTIGER2 -ibldmsql.sql

The script creates the demonstration tables and stored procedures in the Microsoft SQL Server database accordingly:

    CREATE TABLE GTW_EMP (

    EMPNO      SMALLINT NOT NULL

    ENAME      VARCHAR(10) NULL,

    JOB        VARCHAR(9) NULL,

    MGR        SMALLINT NULL,

    HIREDATE   DATETIME NULL,

    SAL        NUMERIC(7,2) NULL,

    COMM       NUMERIC(7,2) NULL,

    DEPTNO     SMALLINT NULL);

    CREATE TABLE GTW_DEPT (

    DEPTNO     SMALLINT NOT NULL,

    DNAME      VARCHAR(14) NULL,

    LOC        VARCHAR(13) NULL);

    CREATE TABLE GTW_SALGRADE (

    GRADE      MONEY NULL,

    LOSAL      NUMERIC(9,4) NULL,

    HISAL      NUMERIC(9,4) NULL);

    DROP PROCEDURE InsertDept;

    CREATE PROCEDURE InsertDept (@dno INTEGER,

       @dname VARCHAR(14), @loc VARCHAR(13))

    AS INSERT INTO GTW_DEPT VALUES (@dno, @dname, @loc);

    DROP PROCEDURE GetDept;

    CREATE PROCEDURE GetDept (@dno INTEGER, @dname VARCHAR(14) OUTPUT)

    AS SELECT @dname=DNAME FROM GTW_DEPT WHERE DEPTNO=@dno;

### Demonstration Table Definitions

The table definitions are listed below using information retrieved by the SQL SERVER MANAGER DESCRIBE command:

GTW_EMP

    Name                            Null?    Type

    ------------------------------- -------- ----

    EMPNO                           NOT NULL NUMBER(5)

    ENAME                                    VARCHAR2(10)

    JOB                                      VARCHAR2(9)

    MGR                                      NUMBER(5)

    HIREDATE                                 DATE

    SAL                                      NUMBER(7,2)

    COMM                                     NUMBER(7,2)

    DEPTNO                                   NUMBER(5)

GTW_DEPT

    Name                            Null?    Type

    ------------------------------- -------- ----

    DEPTNO                          NOT NULL NUMBER(5)

    DNAME                                    VARCHAR2(14)

    LOC                                      VARCHAR2(13)

GTW_SALGRADE

    Name                            Null?    Type

    ------------------------------- -------- ----

    GRADE                                    NUMBER(19,4)

    LOSAL                                    NUMBER(9,4)

    HISAL                                    NUMBER(9,4)

### Demonstration Table Contents

The contents of the Microsoft SQL Server tables are:

GTW_EMP

    EMPNO ENAME   JOB         MGR   HIREDATE   SAL   COMM   DEPTNO

    ----- -----   ---         ---   --------   ---   ----   ------

    7369  SMITH   CLERK       7902  17-DEC-80   800             20

    7499  ALLEN   SALESMAN    7698  20-FEB-81  1600   300       30

    7521  WARD    SALESMAN    7698  22-FEB-81  1250   500       30

    7566  JONES   MANAGER     7839  02-APR-81  2975             20

    7654  MARTIN  SALESMAN    7698  28-SEP-81  1250  1400       30

    7698  BLAKE   MANAGER     7839  01-MAY-81  2850             30

    7782  CLARK   MANAGER     7839  09-JUN-81  2450             10

    7788  SCOTT   ANALYST     7566  09-DEC-82  3000             20

    7839  KING    PRESIDENT         17-NOV-81  5000             10

    7844  TURNER  SALESMAN    7698  08-SEP-81  1500     0       30

    7876  ADAMS   CLERK       7788  12-JAN-83  1100             20

    7900  JAMES   CLERK       7698  03-DEC-81   950             30

    7902  FORD    ANALYST     7566  03-DEC-81  3000             20

    7934  MILLER  CLERK       7782  23-JAN-82  1300             10

GTW_DEPT

    DEPTNO DNAME          LOC

    ----- -------------- --------

       10 ACCOUNTING     NEW YORK

       20 RESEARCH       DALLAS

       30 SALES          CHICAGO

       40 OPERATIONS     BOSTON

GTW_SALGRADE

    GRADE      LOSAL      HISAL

    ------     ------     -----

        1        700      1200

        2       1201      1400

        3       1401      2000

        4       2001      3000

        5       3001      9999

## Case 1: Simple Queries

Case 1 demonstrates the following:

- A simple query

- A simple query retrieving full date information

The first query retrieves all the data from GTW_DEPT and confirms that the gateway is working correctly. The second query retrieves all the data from GTW_EMP including the time portion of the hire date because the default date format was set to DD-MON-YY HH24:MM:SS for the session by an ALTER SESSION command.

To run Case 1, log on to SQL*Plus as SCOTT/TIGER and enter the following:

    SQL> START CASE1

Case 1 executes two SQL statements. The first statement is as follows:

    SELECT * FROM GTW_DEPT@GTWLINK;

which results in the following:

        DEPTNO DNAME          LOC

    ---------- -------------- -------------

            10 ACCOUNTING     NEW YORK

            20 RESEARCH       DALLAS

            30 SALES          CHICAGO

            40 OPERATIONS     BOSTON

The following command and SQL statement change the date format to DD-MON-YY HH24:MM:SS in Oracle and retrieve the employee name and hire date from the Microsoft SQL Server database:

    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MM:SS';
    SELECT ENAME, HIREDATE FROM GTW_EMP@GTWLINK;

which results in the following:

    ENAME      HIREDATE

    ---------- ------------------

    SMITH      17-Dec-80 00:00:00

    ALLEN      20-Feb-81 00:00:00

    WARD       22-Feb-81 00:00:00

    JONES      02-Apr-81 00:00:00

    MARTIN     28-Sep-81 00:00:00

    BLAKE      01-May-81 00:00:00

    CLARK      09-Jun-81 00:00:00

    SCOTT      09-Dec-82 00:00:00

    KING       17-Nov-81 00:00:00

    TURNER     08-Sep-81 00:00:00

    ADAMS      12-Jan-83 00:00:00

    JAMES      03-Dec-81 00:00:00

    FORD       03-Dec-81 00:00:00

    MILLER     23-Jan-82 00:00:00

    14 rows selected.

## Case 2: A More Complex Query

Case 2 demonstrates the following:

- The functions SUM(expression) and NVL(expr1, expr2) in the SELECT list

- The GROUP BY and HAVING clauses

This query retrieves the departments from GTW_EMP whose total monthly expenses are higher than $10,000.

To run Case 2, log on to SQL*Plus as SCOTT/TIGER and enter the following:

    SQL> START CASE2

which executes the following SQL statement:

    SELECT SUM(SAL), SUM(COMM), SUM(SAL + NVL(COMM,0))

    "TOTAL MONTHLY EXPENSES OF", DEPTNO "DEPARTMENT"

    FROM GTW_EMP@GTWLINK GROUP BY DEPTNO

    HAVING SUM(SAL + NVL(COMM,0)) > 10000;

which results in the following:

      SUM(SAL)  SUM(COMM) TOTAL MONTHLY EXPENSES OF DEPARTMENT

    ----------  --------- ------------------------- ----------

         10875                                10875         20

          9400       2200                     11600         30

## Case 3: Joining Microsoft SQL Server Tables

Case 3 demonstrates the following:

- Joins between Microsoft SQL Server tables

- Subselects

- SQL tracing

The query retrieves information from three Microsoft SQL Server tables and relates the employees to their department name and salary grade, but only for those employees earning more than the average salary.

To run Case 3, log on to SQL*Plus as SCOTT/TIGER and enter the following:

    SQL> START CASE3

which executes the following SQL statement:

    SELECT ENAME, DNAME, GRADE

    FROM GTW_EMP@GTWLINK A, GTW_DEPT@GTWLINK B,

    GTW_SALGRADE@GTWLINK C

    WHERE A.DEPTNO = B.DEPTNO

    AND SAL >= LOSAL AND SAL <= HISAL

    AND SAL > (SELECT AVG(SAL) FROM GTW_EMP@GTWLINK);

which results in the following:

    ENAME      DNAME               GRADE

    ---------- -------------- ----------

    CLARK      ACCOUNTING              4

    KING       ACCOUNTING              5

    JONES      RESEARCH                4

    SCOTT      RESEARCH                4

    FORD       RESEARCH                4

    BLAKE      SALES                   4

    6 rows selected.

## Case 4: Write Capabilities

Case 4 is split into three cases and demonstrates the following:

- DELETE Statement

- UPDATE Statement

- INSERT Statement

### DELETE Statement

Case 4a demonstrates bind values and subselect. All employees in department 20 and one employee, WARD, in department 30 are deleted.

To run Case 4a, log on to SQL*Plus as SCOTT/TIGER and enter the following:

    SQL> START CASE4A

which executes the following PL/SQL block:

    DECLARE

       EID        SMALLINT;

       EMPLOYEE   VARCHAR(10);

       DEPARTMENT VARCHAR(14);

    BEGIN

       EMPLOYEE := 'WARD';

       DEPARTMENT := 'RESEARCH';

       SELECT EMPNO INTO EID FROM GTW_EMP@GTWLINK

          WHERE ENAME = EMPLOYEE;

       DELETE FROM GTW_EMP@GTWLINK

          WHERE EMPNO = EID

             OR DEPTNO = (SELECT DEPTNO FROM GTW_DEPT@GTWLINK

                 WHERE DNAME = DEPARTMENT);

    END;

    /

Note: For Microsoft SQL Server, you must add the schema name before the dblink. For example, instead of `GTW_DEPT@GTWLINK`, you need `SCOTT.GTW_DEPT@GTWLINK`.

To ensure the outcome is correct, the script also issues:

    SELECT ENAME, DEPTNO FROM GTW_EMP@GTWLINK;
    ROLLBACK;

which results in the following:

    ENAME          DEPTNO

    ---------- ----------

    ALLEN              30

    MARTIN             30

    BLAKE              30

    CLARK              10

    KING               10

    TURNER             30

    JAMES              30

    MILLER             10

    8 rows selected.

    Rollback complete.

WARD and all employees of the research department 20 were removed.

### UPDATE Statement

Case 4b provides an example of a simple UPDATE statement. In this example, employees are given a $100 per month salary increase. To run Case 4b, log on to SQL*Plus as SCOTT/TIGER and enter the following:

    SQL> START CASE4B

This script issues these statements:

    UPDATE GTW_EMP@GTWLINK SET SAL = SAL + 100;

    SELECT ENAME, SAL FROM GTW_EMP@GTWLINK;

    ROLLBACK;

which results in the following:

    ENAME             SAL

    ---------- ----------

    SMITH             900

    ALLEN            1700

    WARD             1350

    JONES            3075

    MARTIN           1350

    BLAKE            2950

    CLARK            2550

    SCOTT            3100

    KING             5100

    TURNER           1600

    ADAMS            1200

    JAMES            1050

    FORD             3100

    MILLER           1400

    14 rows selected.

    Rollback complete.

### INSERT Statement

Case 4c is an example of a simple insert statement that does not provide information for all columns. To run Case 4c, log on to SQL*Plus as SCOTT/TIGER and enter the following:

    SQL> START CASE4C

The script adds the department SHIPPING as department 50 to GTW_DEPT by issuing the following:

    INSERT INTO GTW_DEPT@GTWLINK (DEPTNO, DNAME)

    VALUES (50, 'SHIPPING');

    SELECT * FROM GTW_DEPT@GTWLINK;
    ROLLBACK;

which results in the following:

    1 row created.

    DEPTNO DNAME          LOC

    ------ -------------- -------------

        10 ACCOUNTING     NEW YORK

        20 RESEARCH       DALLAS

        30 SALES          CHICAGO

        40 OPERATIONS     BOSTON

        50 SHIPPING

    Rollback complete.

## Case 5: Data Dictionary Query

Case 5 demonstrates data dictionary mapping. It retrieves all the tables and views that exist in the Microsoft SQL Server database that begin with "GTW". To run Case 5, log on to SQL*Plus as SCOTT/TIGER and enter the following:

    SQL> START CASE5

The script issues the following statement:

    SELECT * FROM ALL_TABLES@GTWLINK

       WHERE SUBSTR(TABLE_NAME,1,3) = 'GTW';

which results in the following:

    OWNER                          TABLE_NAME                     T C I   PCT_FREE

    ------------------------------ ------------------------------ - - - ----------

     PCT_USED  INI_TRANS  MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

    ---------- ---------- ---------- -------------- ----------- -----------

    MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS L B   NUM_ROWS     BLOCKS

    ----------- ------------ ---------- --------------- - - ---------- ----------

    EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS

    ------------ ---------- ---------- ----------- -------------------------

    NUM_FREELIST_BLOCKS D I C T SAMPLE_SIZE L P I T S N B R G U D S M

    ------------------- - - - - ----------- - - - - - - - - - - - - -

    SCOTT                          GTW_EMP                                       0

            0          0          0              0           0           0

              0            0          0               0              0          0

               0          0          0           0                         0

                      0                   0

    SCOTT                          GTW_DEPT                                      0

            0          0          0              0           0           0

              0            0          0               0              0          0

               0          0          0           0                         0

                      0                   0

    SCOTT                          GTW_SALGRADE                                  0

            0          0          0              0           0           0

              0            0          0               0              0          0

               0          0          0           0                         0

                      0                   0

    SCOTT                          GTW_BONUS                                     0

            0          0          0              0           0           0

              0            0          0               0              0          0

               0          0          0           0                         0

                      0                   0

    SCOTT                          GTW_DUMMY                                     0

            0          0          0              0           0           0

              0            0          0               0              0          0

               0          0          0           0                         0

## Case 6: The Pass-Through Feature

Case 6 demonstrates the gateway pass-through feature which allows an application to send commands or statements to Microsoft SQL Server.

This case demonstrates:

- A pass-through UPDATE statement using bind variables

- A pass-through SELECT statement

### UPDATE Statement

Case 6a provides an example of a pass-through UPDATE statement with bind variables. In this example, the salary for EMPNO 7934 is set to 4000. To run Case 6a, log on to SQL*Plus as SCOTT/TIGER and enter the following:

    SQL> START CASE6A

The script issues these statements:

      SELECT * FROM GTW_EMP@GTWLINK WHERE EMPNO = 7934;

      DECLARE

        crs binary_integer;

        ret integer;

      BEGIN

        crs := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@GTWLINK;

        DBMS_HS_PASSTHROUGH.PARSE@GTWLINK(crs,

         'UPDATE GTW_EMP SET SAL = ? WHERE EMPNO = ?');

        DBMS_HS_PASSTHROUGH.BIND_VARIABLE@GTWLINK(crs, 1, 4000);

        DBMS_HS_PASSTHROUGH.BIND_VARIABLE@GTWLINK(crs, 2, 7934);

        ret := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@GTWLINK(crs);

        DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@GTWLINK(crs);

      END;

      /

      SELECT * FROM GTW_EMP@GTWLINK WHERE EMPNO = 7934;

The results are as follows:

           EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

      ---------- ---------- --------- ---------- --------- ---------- ----------

          DEPTNO

      ----------

            7934 MILLER     CLERK           7782 23-JAN-82       1300

              10

      PL/SQL procedure successfully completed.

           EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM

      ---------- ---------- --------- ---------- --------- ---------- ----------

          DEPTNO

      ----------

            7934 MILLER     CLERK           7782 23-JAN-82       4000

              10

### SELECT Statement

Case 6b provides an example of a pass-through SELECT statement. The data that is returned from the SELECT statement is inserted into a local table at the Oracle database server. To run Case 6b, log on to SQL*Plus as SCOTT/TIGER and enter the following:

    SQL> START CASE6B

The first two statements drop and recreate the LOCAL_PT_TABLE table in the Oracle database. The PL/SQL block uses the DBMS_HS_PASSTHROUGH package to execute a SELECT statement at Microsoft SQL Server and inserts the data returned into LOCAL_PT_TABLE:

        DROP TABLE LOCAL_PT_TABLE;

        CREATE TABLE LOCAL_PT_TABLE (EMPNO NUMBER(5), ENAME VARCHAR2(10));

        DECLARE

          crs binary_integer;

          ret binary_integer;

          ename varchar2(10);

          empno number;

        BEGIN

          crs := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@GTWLINK;

          DBMS_HS_PASSTHROUGH.PARSE@GTWLINK(crs,

           'SELECT EMPNO, ENAME FROM GTW_EMP');

          begin

            ret := 0;

            WHILE (true)

            LOOP

              ret := DBMS_HS_PASSTHROUGH.FETCH_ROW@GTWLINK(crs, false);

              DBMS_HS_PASSTHROUGH.GET_VALUE@GTWLINK(crs, 1, empno);

              DBMS_HS_PASSTHROUGH.GET_VALUE@GTWLINK(crs, 2, ename);

              insert into LOCAL_PT_TABLE values (empno, ename);

            END LOOP;

            EXCEPTION

             when NO_DATA_FOUND then

               BEGIN

                DBMS_OUTPUT.PUT_LINE('End of Fetch');

                DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@GTWLINK(crs);

               END;

          END;

        END;

        /

        SELECT * FROM LOCAL_PT_TABLE;

The results are as follows:

        Table created.

        PL/SQL procedure successfully completed.

             EMPNO ENAME

        ---------- ----------

              7369 SMITH

              7499 ALLEN

              7521 WARD

              7566 JONES

              7654 MARTIN

              7698 BLAKE

              7782 CLARK

              7788 SCOTT

              7839 KING

              7844 TURNER

              7876 ADAMS

              7900 JAMES

              7902 FORD

              7934 MILLER

        14 rows selected.

## Case 7: Executing Stored Procedures

Case 7 demonstrates the gateway executing a stored procedure in the Microsoft SQL Server database.

To run Case7, log on to SQL*Plus as SCOTT/TIGER and enter the following:

    SQL> START CASE7

The first two statements drop and recreate the LOCAL_GTW_DEPT table in the Oracle database:

    DROP TABLE LOCAL_GTW_DEPT;

    CREATE TABLE LOCAL_GTW_DEPT (DEPTNO INTEGER, DEPTNAME VARCHAR2(14));

The next PL/SQL block declares the variable DNAME and executes the stored procedure GetDept in the Microsoft SQL Server. The stored procedure retrieves the department name from the Microsoft SQL Server table, as follows:

    DECLARE

       DNAME VARCHAR2(14);

    BEGIN

       "GetDept"@GTWLINK(10, DNAME);

       INSERT INTO LOCAL_GTW_DEPT VALUES (10, DNAME);

    END;

    /

The results in this row in the Oracle LOCAL_GTW_DEPT table are as follows:

    DEPTNO DNAME

    ------ --------------

        10 ACCOUNTING

| Copyright © 2001 Oracle Corporation. All Rights Reserved. |
| Library | Contents | Index