Connecting SQL Server Database from Oracle Applications database:
1. Database of the SQL Server 2005(Test)
2. Database of Oracle Applications - Oracle 10.2 (UATDB)
Step 1: First Create the ODBC System DSN on UATDB.
Step 3: Edit the TNSNAMES.ORA file.
Step 4: Edit the Listener.ora:
#
# $Header: ad8ilsnr.ora 120.1 2006/06/07 05:52:02 sbandla noship $
#
# ###############################################################
#
# This file is automatically generated by AutoConfig. It will be read and
# overwritten. If you were instructed to edit this file, or if you are not
# able to use the settings created by AutoConfig, refer to Metalink Note
# 387859.1 for assistance.
#
# ###############################################################
#
#
# Net8 definition for Database listener
#
JANC =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCJANC))
(ADDRESS= (PROTOCOL= TCP)(Host=****)(Port= ****))
)
SID_LIST_JANC =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME= D:\DB\****\db\tech_st\10.2.0)
(SID_NAME = ****)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\DB\****\db\tech_st\10.2.0)
(PROGRAM = extproc)
)
(SID_DESC=
(SID_NAME=HSODBC)
(ORACLE_HOME=D:\DB\**** \db\tech_st\10.2.0)
(PROGRAM=hsodbc)
)
)
STARTUP_WAIT_TIME_JANC = 0
CONNECT_TIMEOUT_JANC = 10
TRACE_LEVEL_JANC = OFF
LOG_DIRECTORY_JANC = D:\DB\****\db\tech_st\10.2.0/network/admin
LOG_FILE_JANC = JANC
TRACE_DIRECTORY_JANC = D:\DB\****\db\tech_st\10.2.0/network/admin
TRACE_FILE_JANC = JANC
ADMIN_RESTRICTIONS_JANC = OFF
SUBSCRIBE_FOR_NODE_DOWN_EVENT_JANC = OFF
IFILE=D:\DB\****\db\tech_st\10.2.0\network\admin\JANC_uatdb\listener_ifile.ora
Step 5 : Login to **** Database using APPS user;
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\appluat>sqlplus / as sysdba;
SQL*Plus: Release 10.1.0.5.0 - Production on Sun May 9 08:57:57 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> conn apps/xxxxxx@**** as sysdba;
Connected.
SQL> select name from v$database;
NAME
---------
****
SQL> create public database link HSODBC connect to "sa" identified by "password"
using 'hsodbc';
Database link created.
SQL> select count(*) from employees@hsodbc;
COUNT(*)
----------
30378
You did it! J
Step 6 : Now create view in SQL Server database. Make all the column names as capital:
create view [dbo].[****] as select query for sql server database
Step 7 : Create synonym for the view created in SQL Server:
Create synonym **** for ****@HSBDBC;
Cherrrrrrrrrrrrrrrrrrrrrrrrssssssssssssssssssssssss….
J
