Sugeng Rawöh

Step 1
siapkan sistem DSN SQL Server (dengan nama SQLKU) pada Windows ODBC Data Source

Step 2
Buat Heterogeneous Services Initialization File contoh file ada di alamat berikut :
—————-
$ORACLE_HOME/hs/admin/inithsodbc.ora
# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO =
HS_FDS_TRACE_LEVEL =
#
# Environment variables required for the non-Oracle system
#
#set =
diganti menjadi
$ORACLE_HOME/hs/admin/initsqlku.ora
# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = SQLKUDSN
HS_FDS_TRACE_LEVEL = OFF
—————-

Step 3
- Buat 1 listener baru dengan nama LISTENERSQL
- ubah port menjadi 1522
- ubah sid_name menjadi SQLKUDSN
- ubah letak ORACLE_HOME
- Ubah program menjadi hsodbc
contoh file ada di $ORACLE_HOME/hs/admin/listener.ora.sample file diubah sehingga menjadi :
————————–
LISTENERSQLSERVER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENERSQLSERVER=
(SID_LIST=
(SID_DESC=
(SID_NAME=SQLKUDSN)
(ORACLE_HOME = d:\oracle\product\10.1.0\db_1)
(PROGRAM=hsodbc)
)
)
————————-

STEP 4
buat alias Name server (TNSName)
————————
SQLSERVERDSN =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=SQLKUDSN))
(HS=OK)
)
———————–

STEP 5
Validasi koneksi DSN
C:\>tnsping sqlserverdsn
TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 – Production on 01-DEC-2004 13:19:54
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
d:\oracle\product\10.1.0\db_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=SQLSERVERDSN)) (HS=OK))
OK (30 msec)

STEP 6
Buat database link
SQL> create database link sqlsvr
connect to sa identified by using ‘SQLSERVERDSN’;
Database link created.

STEP 7
select data
SQL> desc employees@sqlserverdsn
Name Null? Type
—————————————– ——– —————————-
EmployeeID NOT NULL NUMBER(10)
LastName NOT NULL VARCHAR2(20)
FirstName NOT NULL VARCHAR2(10)
Title VARCHAR2(30)
TitleOfCourtesy VARCHAR2(25)
BirthDate DATE
HireDate DATE
Address VARCHAR2(60)
City VARCHAR2(15)
Region VARCHAR2(15)
PostalCode VARCHAR2(10)
Country VARCHAR2(15)
HomePhone VARCHAR2(24)
Extension VARCHAR2(4)
Photo LONG RAW
Notes LONG
ReportsTo NUMBER(10)
PhotoPath VARCHAR2(32512 CHAR)

SQL> select “EmployeeID”,”LastName”,”FirstName” from employees@sqlserverdsn;
EmployeeID LastName FirstName
———- ——————– ———-
1 Davolio Nancy
2 Fuller Andrew
3 Leverling Janet
4 Peacock Margaret
5 Buchanan Steven
6 Suyama Michael
7 King Robert
8 Callahan Laura
9 Dodsworth Anne
9 rows selected.

————————————
Kredit dan sumber tulisan : Ditya Octavianto