1 Reply Latest reply: Mar 17, 2011 8:36 AM by BluShadow RSS

    SQL Server to Oracle

    816392
      Hi all,

      I'm not sure if I'm posting this question in the rite place. If I'm wrong I'm sorry for any inconvenience.

      I wanted to find out a way to copy tables (6 tables) in SQL Server 2000 to Oracle 10G. I would appreciate any kind of documents for this. Please dont mention you can use DTS or SSIS. I just wanted to know the actual steps.


      Thanks
      Bob
        • 1. Re: SQL Server to Oracle
          BluShadow
          Bob wrote:
          Hi all,

          I'm not sure if I'm posting this question in the rite place. If I'm wrong I'm sorry for any inconvenience.

          I wanted to find out a way to copy tables (6 tables) in SQL Server 2000 to Oracle 10G. I would appreciate any kind of documents for this. Please dont mention you can use DTS or SSIS. I just wanted to know the actual steps.
          Hi Bob,

          Afraid I don't have the actual specific steps to hand, but you could do this with heterogeneous services (HS) i.e. using an ODBC driver, so that the SQL Server database appears as a database link inside Oracle from which you can just query the data.

          I've got an example of doing something similar where we treat an Excel (.xls) file as a database using HS as excel has an ODBC driver, so some adaptation would work for SQL Server...

          1- Go to Control Panel>Administrative Tools>Data Sources (ODBC)>System DSN and create a data source with appropriate driver. Name it EXCL.

          2- In %ORACLE_HOME%\Network\Admin\Tnsnames.ora fie add entry:
          EXCL =
            (DESCRIPTION =
              (ADDRESS_LIST =
                (ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.0.24)(PORT = 1521))
              )
              (CONNECT_DATA =
                (SID = EXCL)
              )
              (HS = OK)
            )
          Here SID is the name of data source that you have just created.

          3- In %ORACLE_HOME%\Network\Admin\Listener.ora file add:
          (SID_DESC = 
            (PROGRAM = hsodbc) 
            (SID_NAME = <hs_sid>) 
            (ORACLE_HOME = <oracle home>) 
          )
          under SID_LIST_LISTENER like:
          SID_LIST_LISTENER =
            (SID_LIST =
              (SID_DESC =
                (SID_NAME = PLSExtProc)
                (ORACLE_HOME = d:\ORA9DB)
                (PROGRAM = extproc)
              )
              (SID_DESC =
                (GLOBAL_DBNAME = ORA9DB)
                (ORACLE_HOME = d:\ORA9DB)
                (SID_NAME = ORA9DB)
              )
              (SID_DESC = 
                (PROGRAM = hsodbc) 
                (SID_NAME = EXCL) 
                (ORACLE_HOME = D:\ora9db) 
              )
            ) 
          Dont forget to reload the listener
          C:\> lsnrctl reload
          4- In %ORACLE_HOME%\hs\admin create init<HS_SID>.ora. For our sid EXCL we create file initexcl.ora.

          In this file set following two parameters:
          HS_FDS_CONNECT_INFO = excl
          HS_FDS_TRACE_LEVEL = 0
          5- Now connect to Oracle database and create database link with following command:
          SQL> CREATE DATABASE LINK excl
          2 USING 'excl'
          3 /
          
          Database link created.
          Now you can perform query against this database like you would for any remote database.
          SQL> SELECT table_name FROM all_tables@excl;
          
          TABLE_NAME
          ------------------------------
          DEPT
          EMP
          Note:
          1) Each worksheet in the Excel workbook is taken to be a "table" with the worksheet name being the table name (must not exceed the 30 character limit of Oracle, as Excel allows 32 chrs).
          2) The first row on the worksheet provides the column names for the table (again, limited to 30 characters)
          3) The second and subsequent rows provide the actual rows of data on the table.


          Or refer to this Article...
          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4406709207206