|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621915 is a reply to message #621909] |
Tue, 19 August 2014 10:57   |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
Solomon Yakobson wrote on Tue, 19 August 2014 09:44
What platform Oracle database resides on? You need nothing but ODBC if Oracle resides on windows.
SY.
Just for my own education, how do you point a db link directly to ODBC without going through an Oracle Gateway?
FWIW, I've been looking at this a lot the last couple of weeks. We had a database that was being used as a 'pass-through' from another oracle database to an MSSQL database. I replaced that database with Oracle Transparent Gateway for ODBC. Had never worked with HS before, and what I found was this: Transparent Gateway comes in two basic flavors. The "generic" gateway (dg4odbc) takes the call from the source db (db link) and passes it on to ODBC. That version is free. Otherwise, a target-specific gateway that doesn't rely on ODBC has additional licensing costs -- and they are not cheap.
When I undertook this, several questions were raised by my new manager, who is very (VERY) MS-centric. To answer those questions, I did a lot more digging out of how the gateway works and comparing that to MSSQL use of OLE DB drivers. All this is very fresh on my mind, and I really don't see how oracle can use ODBC without the gateway. If there is a way, I'd certainly like to add that to my knowledge and the document I wrote for my team.
|
|
|
|
| Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621917 is a reply to message #621915] |
Tue, 19 August 2014 12:20 |
Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
EdStevens wrote on Tue, 19 August 2014 11:57Just for my own education, how do you point a db link directly to ODBC without going through an Oracle Gateway?
Oracle provides HS (heterogenious connectivity) for free. For example:
%ORACLE_HOME%/hs/admin/initMSSQL.ora:
HS_FDS_CONNECT_INFO = MSSQL
HS_FDS_TRACE_LEVEL = 16
HS_FDS_PROC_IS_FUNC = TRUE
HS_FDS_RESULTSET_SUPPORT = TRUE
HS_FDS_RESULTSET_SUPPORT=FALSE
%ORACLE_HOME%/network/admin/listener.ora:
(SID_DESC =
(SID_NAME = MSSQL)
(ORACLE_HOME = C:\app\sy\product\11.2.0\dbhome_1)
(PROGRAM = dg4odbc)
)
%ORACLE_HOME%/network/admin/tnsnames.ora:
MSSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
(CONNECT_DATA =
(SID = MSSQL)
)
(HS = OK)
)
ODBC system data source MSSQL is pointing to SQL Server connecting as SQL Server authenticated user SCOTT with default database AdventureWorks. Now:
SQL> select *
2 from "HumanResources"."Department"@mssql
3 /
DepartmentID Name GroupName ModifiedD
------------ -------------------------- ------------------------------ ---------
1 Engineering Research and Development 01-JUN-98
2 Tool Design Research and Development 01-JUN-98
3 Sales Sales and Marketing 01-JUN-98
4 Marketing Sales and Marketing 01-JUN-98
5 Purchasing Inventory Management 01-JUN-98
6 Research and Development Research and Development 01-JUN-98
7 Production Manufacturing 01-JUN-98
8 Production Control Manufacturing 01-JUN-98
DepartmentID Name GroupName ModifiedD
------------ -------------------------- ------------------------------ ---------
9 Human Resources Executive General and Administ 01-JUN-98
ration
10 Finance Executive General and Administ 01-JUN-98
ration
11 Information Services Executive General and Administ 01-JUN-98
ration
DepartmentID Name GroupName ModifiedD
------------ -------------------------- ------------------------------ ---------
12 Document Control Quality Assurance 01-JUN-98
13 Quality Assurance Quality Assurance 01-JUN-98
14 Facilities and Maintenance Executive General and Administ 01-JUN-98
ration
15 Shipping and Receiving Inventory Management 01-JUN-98
16 Executive Executive General and Administ 01-JUN-98
DepartmentID Name GroupName ModifiedD
------------ -------------------------- ------------------------------ ---------
ration
16 rows selected.
SQL>
SY.
|
|
|
|
|
|
| Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621919 is a reply to message #621917] |
Tue, 19 August 2014 12:53 |
|
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
Solomon Yakobson wrote on Tue, 19 August 2014 12:20EdStevens wrote on Tue, 19 August 2014 11:57Just for my own education, how do you point a db link directly to ODBC without going through an Oracle Gateway?
Oracle provides HS (heterogenious connectivity) for free. For example:
%ORACLE_HOME%/hs/admin/initMSSQL.ora:
HS_FDS_CONNECT_INFO = MSSQL
HS_FDS_TRACE_LEVEL = 16
HS_FDS_PROC_IS_FUNC = TRUE
HS_FDS_RESULTSET_SUPPORT = TRUE
HS_FDS_RESULTSET_SUPPORT=FALSE
%ORACLE_HOME%/network/admin/listener.ora:
(SID_DESC =
(SID_NAME = MSSQL)
(ORACLE_HOME = C:\app\sy\product\11.2.0\dbhome_1)
(PROGRAM = dg4odbc)
)
Exactly. PROGRAM = dg4odbc. The Oracle Generic Gateway.
When the listener gets the request (tnsames .. SID=MSSQL) it calls dg4odbc, which uses that sid name to construct the name of the 'init<sid>' file it needs to get the name of the ODBC DSN. So using SID=MSSQL, it opens initMSSQL.ora and finds "HS_FDS_CONNECT_INFO = MSSQL", which is the DSN to be passed on to ODBC. And of course, that DSN then informs about which driver to use, the final target database, and credentials.
Quote:
%ORACLE_HOME%/network/admin/tnsnames.ora:
MSSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
(CONNECT_DATA =
(SID = MSSQL)
)
(HS = OK)
)
ODBC system data source MSSQL is pointing to SQL Server connecting as SQL Server authenticated user SCOTT with default database AdventureWorks. Now:
SY.
I could draw more, point to some additional documentation, but that would require quoting MOS content. I'd just say please review note 1083703.1 I don't think it would be a violation if I just point out that the note states that Oracle Gateway products are based on Heterogeneous Services.
I did see a reference, that I can't seem to put my hands on at the moment, that said that the Generic Gateway for ODBC is installed by default with the database. And so it is. It can also, if one chooses, to install it on any other machine on the network. Or one could choose to install (and pay for) Oracle Gateway for MSSQL (DG4Msql), Oracle Gateway for Sybase (DG4Sybase), etc, etc. But it's all still Oracle Gateway.
As for related questions that are in this thread ... the Oracle 'dedicated' gateways come with additional cost. The 'generic (odbc)' gateway is free and already installed with the database. But either way, it is still the/a Oracle Gateway. It just a question of a free version that relies on ODBC as an additional step, or an additional cost version that can connect "directly" to the target without going through ODBC.
|
|
|
|
| Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621920 is a reply to message #621919] |
Tue, 19 August 2014 12:59 |
|
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
|
BTW, also seen MOS note 1274143.1. Oracle has a nifty "provided as-is" utility for configuring a gateway. It's a little java program that asks for information about source and target database, then generates the necessary files or pieces of files ... SID_DESC section of listener.ora, an entry for tnsnames.ora, the gateway's init<sid>.ora, and the sql to create the db link.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
