Release 8.1.6 for Windows NT
Part Number A80982-01
 Library |
 Contents |
 Index |
| Oracle Transparent Gateway Sybase Administrator's Guide Release 8.1.6 for Windows NT Part Number A80982-01 |
|
The following case studies for Sybase 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:
The cases illustrate:
The distribution CD-ROM contains the following:
After a successful gateway installation, use the demonstration files stored in the directory ORACLE_HOME\tg4sybs\demo where ORACLE_HOME is the ORACLE_HOME directory under which the gateway is installed. The directory contains the following demonstration files:
|
bldsybs.sql |
case5.sql |
|
case1.sql |
case6a.sql |
|
case2.sql |
case6b.sql |
|
case3.sql |
case7.sql |
|
case4a.sql |
dropsybs.sql |
|
case4b.sql |
|
|
case4c.sql |
|
The case studies assume these requirements have been met:
For example, you can create the database link as follows:
SQL> CREATE DATABASE LINK GTWLINK CONNECT TO SCOTT
2 IDENTIFIED BY TIGER2 USING 'GTWSID';
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 Sybase database, use the bldsybs.sql script to create them. Enter the following:
> isql -USCOTT -PTIGER2 -ibldsybs.sql
The script creates the demonstration tables and stored procedures in the Sybase database accordingly:
CREATE TABLE GTW_EMP (
EMPNO SMALLINT NOT NULL
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR SMALLINT,
HIREDATE DATETIME,
SAL NUMERIC(7,2),
COMM NUMERIC(7,2),
DEPTNO SMALLINT)
go
CREATE TABLE GTW_DEPT (
DEPTNO SMALLINT NOT NULL,
DNAME VARCHAR(14),
LOC VARCHAR(13))
go
CREATE TABLE GTW_SALGRADE (
GRADE MONEY,
LOSAL NUMERIC(9,4),
HISAL NUMERIC(9,4))
go
DROP PROCEDURE InsertDept
go
CREATE PROCEDURE InsertDept (@dno INTEGER,
@dname VARCHAR(14), @loc VARCHAR(13))
AS INSERT INTO GTW_DEPT VALUES (@dno, @dname, @loc)
go
DROP PROCEDURE GetDept
go
CREATE PROCEDURE GetDept (@dno INTEGER, @dname VARCHAR(14) OUTPUT)
AS SELECT @dname=DNAME FROM GTW_DEPT WHERE DEPTNO=@dno
go
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)
The contents of the Sybase 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 demonstrates the following:
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 Sybase 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 demonstrates the following:
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 demonstrates the following:
The query retrieves information from three Sybase 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 is split into three cases and demonstrates the following:
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;
/
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.
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.
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 demonstrates data dictionary mapping. It retrieves all the tables and views that exist in the Sybase 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 demonstrates the gateway pass-through feature which allows an application to send commands or statements to Sybase.
This case demonstrates:
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
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 Sybase 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 demonstrates the gateway executing a stored procedure in the Sybase 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 Sybase. The stored procedure retrieves the department name from the Sybase 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. |
|