Home » RDBMS Server » Networking and Gateways » insert into oracle_table (column1) select column1 from ms_sql_table (Oracle 11.2.0.1.0)
icon5.gif  insert into oracle_table (column1) select column1 from ms_sql_table [message #621899] Tue, 19 August 2014 09:07 Go to next message
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
Senior Member
Hi all,

how do I import data from ms sql table ?

begin
insert into oracle_table (column1)
select column1 from ms_sql_table;
commit;
end;
Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621901 is a reply to message #621899] Tue, 19 August 2014 09:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ecivgamer wrote on Tue, 19 August 2014 07:07
Hi all,

how do I import data from ms sql table ?

begin
insert into oracle_table (column1)
select column1 from ms_sql_table;
commit;
end;


posted code only operates against a single database
Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621902 is a reply to message #621899] Tue, 19 August 2014 09:10 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Did you search google? I bet you will get plenty of solutions to export data from sql server to Oracle.

I found this quick search result http://stackoverflow.com/questions/1728694/export-tables-from-sql-server-to-be-imported-to-oracle-10g

Edit : provided a link

[Updated on: Tue, 19 August 2014 09:13]

Report message to a moderator

Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621903 is a reply to message #621902] Tue, 19 August 2014 09:13 Go to previous messageGo to next message
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
Senior Member
Lalit Kumar B wrote on Tue, 19 August 2014 17:10
Did you search google? I bet you will get plenty of solitions to import data from sql server to Oracle.


You're right, but I can't find solution except using third-part software. Can you?
Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621904 is a reply to message #621901] Tue, 19 August 2014 09:14 Go to previous messageGo to next message
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
Senior Member
BlackSwan wrote on Tue, 19 August 2014 17:10
ecivgamer wrote on Tue, 19 August 2014 07:07
Hi all,

how do I import data from ms sql table ?

begin
insert into oracle_table (column1)
select column1 from ms_sql_table;
commit;
end;


posted code only operates against a single database


Yes, I started to think about some way to connect to ms sql database, is it possible to use some kind of dblink ?

begin
insert into oracle_table (column1)
select column1 from ms_sql_table@mydblink;
commit;
end;
Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621905 is a reply to message #621903] Tue, 19 August 2014 09:16 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
> Can you?

Well, I don't know any other way except using some tools. Since they are two different databases belonging to two different vendors. Did you have a look at sql management studio?
Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621906 is a reply to message #621905] Tue, 19 August 2014 09:22 Go to previous messageGo to next message
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
Senior Member
Well, I think about creating a heterogeneous data source, so I can query a MS SQL Server database from within Oracle. Is it possible at all?
Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621907 is a reply to message #621904] Tue, 19 August 2014 09:26 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
ecivgamer wrote on Tue, 19 August 2014 09:14

Yes, I started to think about some way to connect to ms sql database, is it possible to use some kind of dblink ?

begin
insert into oracle_table (column1)
select column1 from ms_sql_table@mydblink;
commit;
end;


Yes, you can do that if you have configured the Oracle Transparent Gateway.

http://docs.oracle.com/cd/E11882_01/server.112/e11050/toc.htm

Also see MOS note 1083703.1
Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621908 is a reply to message #621906] Tue, 19 August 2014 09:28 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
This is standard functionality,
http://docs.oracle.com/cd/E11882_01/gateways.112/e12069/toc.htm
Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621909 is a reply to message #621903] Tue, 19 August 2014 09:44 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
[quote title=ecivgamer wrote on Tue, 19 August 2014 10:13]Lalit Kumar B wrote on Tue, 19 August 2014 17:10
but I can't find solution except using third-part software.


What platform Oracle database resides on? You need nothing but ODBC if Oracle resides on windows.

SY.

[Updated on: Tue, 19 August 2014 09:45]

Report message to a moderator

Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621910 is a reply to message #621909] Tue, 19 August 2014 10:00 Go to previous messageGo to next message
ecivgamer
Messages: 147
Registered: May 2011
Location: Ukraine
Senior Member
Solomon Yakobson wrote on Tue, 19 August 2014 17:44

What platform Oracle database resides on? You need nothing but ODBC if Oracle resides on windows.

SY.


Solomon Yakobson,
thank you for your reply.

In this case Oracle database resides on RedHat Linux.
How about this?
Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621913 is a reply to message #621910] Tue, 19 August 2014 10:45 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Then you need ODBC for Linux or Oracle Gateway. I think there are free ODBC for Linux on the web. Oracle Gateway is way not free.

SY.
Re: insert into oracle_table (column1) select column1 from ms_sql_table [message #621914 is a reply to message #621913] Tue, 19 August 2014 10:56 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
To use Heterogeneous Services to talk to SQL Server from Linux you need two additional packages:

* UnixODBC for the ODBC layer.
* A Linux ODBC driver to talk to SQL Server. There I use FreeTDS.

Here is a pretty concise writeup on how to set it up:

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
EdStevens wrote on Tue, 19 August 2014 11:57
Just 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 #621918 is a reply to message #621917] Tue, 19 August 2014 12:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oops,

I forgot to post db link:

CREATE DATABASE LINK MSSQL
  CONNECT TO SCOTT
  IDENTIFIED BY TIGER
  USING 'MSSQL'
/


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 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Solomon Yakobson wrote on Tue, 19 August 2014 12:20
EdStevens wrote on Tue, 19 August 2014 11:57
Just 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 Go to previous message
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.
Previous Topic: SQLNET.AUTHENTICATION_SERVICES=(NTS,NONE)
Next Topic: How to connect to Server Database using front end application over internet
Goto Forum:
  


Current Time: Thu Mar 28 08:17:42 CDT 2024