ODBC를 이용한 방법을 해보세요.

 

PURPOSE
-------
 
This article documents setting up and troubleshooting Heterogeneous Services, 
also referred to as "Generic Connectivity", to Microsoft SQL Server 
using ODBC on Windows NT.
 
 
SCOPE & APPLICATION
-------------------
 
This bulletin describes a setup of Oracle 8.1.6 running on Windows
NT 4.0 to access a MS SQL Server database using the Microsoft Data 
Access Components (MDAC)SDK ODBC driver.
 
The bulletin assumes the Microsoft SQL Server system is already established on
a Windows NT Network.
 
-------------------------------------------------------------------
1) Install the Microsoft Data Access Components (MDAC)SDK ODBC driver. This can 
   be downloaded from http://www.microsoft.com/data. 
 
2) Install the Oracle Server. 
  
   A "Typical Installation" will include "Generic Connectivity using ODBC".
   If performing a "Custom Installation", do not omit this component.
   Directory "ORACLE_HOME/HS" is created during the installation which includes
   sample files.
     
3) Ensure CATHS has been run. 
   
   Oracle Generic Connectivity feature requires the data dictionary objects 
   created by script "ORACLE_HOME\RDBMS\ADMIN\CATHS.SQL".
 
   To test whether CATHS has run, do a "describe" of one of the HS objects. For 
   example, connect to the Oracle server as schema "SYSTEM" and do 
   "DESCRIBE SYS.HS_FDS_CLASS". If it does not exist, connect as SYS (or 
   internal) and run the CATHS script.
 
 
4) Configure an ODBC System Data Source.
   
   From the Control panel select "Data Sources (ODBC)". Click the "System DSN"
   tab. Click the "Add" button. Select the "SQL Server" driver. In the 
   configuration panel, key a name and description of your choice. Remember the 
   name, it is required later - on my system I called it "sstest". Select a SQL
   Server from the drop-down menu. Choose your preference for
   authentication. I chose "SQL Server authentication" and specified userid
   "SA" (you can guess the password). Click "Next". I didn't change any
   further options on the next two panels. Click "Finish", you get a button to
   "test data source", which worked for me, then clicked "ok" twice to finish.
   
5) Configure tnsnames.ora. This file is in ORACLE_HOME\NETWORK\ADMIN.
   Add an entry, e.g.:
 
   hsodbc.uk.oracle.com  = 
      (DESCRIPTION=
        (ADDRESS=
           (PROTOCOL=tcp)
           (HOST=<HOSTNAME>)           |<- adjust hostname for your system
           (PORT=1521)                 |<- adjust port if not using 1521 
        )  
        (CONNECT_DATA=
           (SID=hsodbc)
        )
        (HS=)
      )    
 
 
  Note, a sample file is located in ORACLE_HOME\HS\ADMIN.
 
6) Configure listener.ora. This file is also in ORACLE_HOME\NETWORK\ADMIN.
   Add the following line to the SID_List of the listener.ora 
 
        (SID_DESC=
           (SID_NAME=hsodbc)
           (ORACLE_HOME=d:\oracle\ora81)  |<- adjust Oracle_Home directory
           (PROGRAM=hsodbc)
         )
   
   Note, a sample file is located in ORACLE_HOME\HS\ADMIN.
 
7) Restart the Oracle listener: 
   In an MSDOS window, issue commands "LSNRCTL STOP" then LSNRCTL START".
   Do "LSNRCTL STATUS" to check there is a service handler for your hsodbc 
   entry.
 
8) Adjust the configuration file of the gateway.  It is located in 
   ORACLE_HOME\HS\ADMIN. The name depends on the SID you use for the 
   Heterogeneous Service.
   In my example the listener SID is "hsodbc", thus the configuration file 
   is 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 = sstest    |<- ODBC Data Source Name configured in step 4
   HS_FDS_TRACE_LEVEL = 0          |<- trace levels are from 0 to 4 (4 is maximum)
   HS_FDS_TRACE_FILE_NAME = sstest.trc |<- adjust as required  
 
   The trace file specification can be commented out if not required. 
   Trace files are generated with default names in the ORACLE_HOME/HS/TRACE 
   directory if trace level is not zero. 
 
9) In an SQL*Plus session, create a database link. E.g.:
 
   create database link hsodbc using 'hsodbc';
 
 
10) Test the connection. E.g.:
 
    select * from authors@hsodbc;    
 
 
ODBC PROBLEM DETERMINATION
--------------------------
 
If problems are encountered, (e.g. ORA-9100 errors), please check the ODBC 
configuration and obtain the following information prior to reporting the problem 
to Oracle Support Services:
 
1.  You should be using an MS SQL ODBC Driver.
    a. What is the DSN (Data Source Name)?  This can be determined by
       clicking on 'Control Panel' then click on 'ODBC' then click on
       the 'System DSN' tab.
 
    b. What is the SQL Driver Version?  Click on the '32 bit Administrator'
       icon in the Oracle group, Click on the 'Driver' button then Click on 
       the SQL Server driver and choose 'About'.
 
    c. Obtain an ODBC trace:
        Run REGEDT32, click on 'hkey_local_machine', choose
        software, odbc, odbc.ini. Change the value of the 
        TRACE parameter to 1 and provide a valid trace file
        name in the TRACEFILE parameter. 

답변 감사 합니다.

그런데 위 방법의 해결책을 아시는 분이 계시면 알려 주셨으면 합니다.

어떻게 해야 하는건지 궁금해서리...^^;;;

 

select * from openquery(oracle, 'select * from "TEST"."TABLE"')

쿼리는 아래와 같이 수정하시면 됩니다.

 

select * from openquery(oracle, 'select * from 스키마.테이블명')

 

"TEST"."TABLE" -> TEST.TABLE

"" ->요것만 빼주시면 돼요