This content has been marked as final. Show 1 reply
Bob wrote:Hi Bob,
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.
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:
Here SID is the name of data source that you have just created.
EXCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.12.0.24)(PORT = 1521)) ) (CONNECT_DATA = (SID = EXCL) ) (HS = OK) )
3- In %ORACLE_HOME%\Network\Admin\Listener.ora file add:
under SID_LIST_LISTENER like:
(SID_DESC = (PROGRAM = hsodbc) (SID_NAME = <hs_sid>) (ORACLE_HOME = <oracle home>) )
Dont forget to reload the listener
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) ) )
4- In %ORACLE_HOME%\hs\admin create init<HS_SID>.ora. For our sid EXCL we create file initexcl.ora.
C:\> lsnrctl reload
In this file set following two parameters:
5- Now connect to Oracle database and create database link with following command:
HS_FDS_CONNECT_INFO = excl HS_FDS_TRACE_LEVEL = 0
Now you can perform query against this database like you would for any remote database.
SQL> CREATE DATABASE LINK excl 2 USING 'excl' 3 / Database link created.
SQL> SELECT table_name FROM all_tables@excl; TABLE_NAME ------------------------------ DEPT EMP
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...