Accessing SQL Server from Oracle with Database Gateway for ODBC (DG4ODBC)
To connect Oracle to a non-Oracle system through DG4ODBC:
Install and configure the ODBC driver on the machine where DG4ODBC is installed.
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\dg4odbc.exe
D:\oracle\product\10.2.0\db_1\bin\hsodbc.exe
D:\oracle\product\10.2.0\db_1\bin\dg4odbc.exe
for 10g 32 bit listener
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(
PROGRAM=hsodbc)
)
for 11g OR 10G 64 BIT listener
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(
PROGRAM=dg4odbc)
)
Configure Oracle:
Database gateway (init*.ora). initdg4odbc.ora
Database listener (listener.ora).
Network client (tnsnames.ora).
Create a database link with SQL*Plus.
----------odbcad32---------------------
datasource name (DSN) : dg4odbc
description: dg4odbc
sqlserver : 172.168.0.1
database name: mysqlserverdatabase
username: sa(sqlserver user to connect to oracle)
password: sa
test connection successfully
--------------------oracle------
Select * from v$parameter where name like 'global_names%';
alter system set global_names=false scope = both;
create public database link lnk_sqlserver connect to "sa" identified by "sa" using 'dg4odbc';
username password should be double quotes
The db link name is sqlserver. Username and password must be in double quotes, because the username and password are case sensitive in SQL Server. 'DG4ODBC' points to the alias in the tnsnames.ora file that calls the HS subsystem.
rename init<>.ora to initdg4odbc.ora
D:\app\Administrator\product\11.2.0\dbhome_1\hs\admin\initdg4odbc.ora
D:\oracle\product\10.2.0\db_1\hs\admin\initdg4odbc.ora
HS_FDS_CONNECT_INFO = dg4odbc
HS_FDS_TRACE_LEVEL = off
-----------listener-----D:\oracle\product\10.2.0\db_1------10g
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
)
Please check hsodbc, dg4odbc at $oracle_home/bin
-----------listener-----D:\oracle\product\10.2.0\db_1------11g
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(PROGRAM=dg4odbc)
)
-----------------tnsnames.ora--------D:\oracle\product\10.2.0\db_1-----
Host,SID should be same as listener entry and should be of Oracle (not sql server)
dg4odbc =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.1)(PORT=1521))
(CONNECT_DATA=(SID=dg4odbc))
(HS=OK)
)
Now Check the connection
select * from all_catalog@lnk_sqlserver
select * from "systables"@lnk_sqlserver;
INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
values('03','ksh-001',1,'a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit,SegmentAccount)
values('03','ksh-001',1,'a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit)
values('03','ksh-001','a');
Enclose the column names in double quotes due to case sensitivitiy of the columns
INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
values('03','ksh-001',1,'a');
SQL> alter package pk_asianauto compile body;
Warning: Package Body altered with compilation errors.
SQL> sho err
Errors for PACKAGE BODY PK_ASIANAUTO:
LINE/COL ERROR
-------- -----------------------------------------------------------------
28/13 PL/SQL: SQL Statement ignored
28/80 PL/SQL: ORA-00904: "SEGMENTACCOUNT": invalid identifier
60/13 PL/SQL: SQL Statement ignored
60/114 PL/SQL: ORA-00904: "ACTION": invalid identifier
92/13 PL/SQL: SQL Statement ignored
92/85 PL/SQL: ORA-00904: "ISPRIMARYDEALER": invalid identifier
126/13 PL/SQL: SQL Statement ignored
127/13 PL/SQL: ORA-00904: "ADHOCCAP": invalid identifier
169/13 PL/SQL: SQL Statement ignored
171/70 PL/SQL: ORA-00904: "FLAG": invalid identifier
204/13 PL/SQL: SQL Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
204/57 PL/SQL: ORA-00904: "CLIENTCODE": invalid identifier
261/17 PL/SQL: SQL Statement ignored
262/48 PL/SQL: ORA-00904: "DAYSELLAMT": invalid identifier
266/17 PL/SQL: SQL Statement ignored
267/48 PL/SQL: ORA-00904: "DAYSELLAMT": invalid identifier
278/14 PL/SQL: SQL Statement ignored
278/90 PL/SQL: ORA-00904: "SEGMENTACCOUNT": invalid identifier
288/14 PL/SQL: SQL Statement ignored
288/90 PL/SQL: ORA-00904: "SEGMENTACCOUNT": invalid identifier
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit,SegmentAccount)
2 values('03','ksh-001','a','a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit,SegmentAccount)
*
ERROR at line 1:
ORA-00904: "SEGMENTACCOUNT": invalid identifier
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit)
2 values('03','ksh-001','a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit)
*
ERROR at line 1:
ORA-00904: "BASEDEPOSIT": invalid identifier
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
2 values('03','ksh-001','a','a');
values('03','ksh-001','a','a')
*
ERROR at line 2:
ORA-28534: Heterogeneous Services preprocessing error
-------------
we are trying to insert char into number
--------------------
SQL> desc DBO.AMR@LNKASIANTRANSMIT
Name Null? Type
----------------------------------------- -------- ----------------------------
Header VARCHAR2(2)
ClientCode VARCHAR2(25)
BaseDeposit NUMBER(24,6)
SegmentAccount VARCHAR2(15)
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
2 values('03','ksh-001',1,'a');
1 row created.
SQL> rollback;
