Search This Blog

Saturday, November 20, 2010

Connecting SQL Server Database from Oracle Applications database

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



No comments:

Post a Comment