How to load data from SQL Server into Oracle Table using PLSQL Developer

Question

I have an Oracle 19c test DB and credentials to a certain SQL Server.

Now I need to create an plsql loader package which selects on one table stored in SQL Server and load it into a table stored in my Oracle database.

The problem is not the coding of the package, its how can I create a DB link for SQL Server.

Usually I create DB links when I want to select on tables to another Oracle database, I do it like this:

create database link 'CHOOSE A NAME' 
connect to 'SCHEMA NAME' 
identified by 'PASSWORD' 
using '.....com' (DB HOST)

But for my SQL Server credentials I only have the server name like TEST101\MUC2, schema name and password.

The logon with SQL Server Management Studio works.

But how can I create the db link on the Oracle side, when I don't have the ....com for the 'using ' part?

Thank you all.

So sounds like the question is really how to create a database link from Oracle to SQL Server? I'm pretty sure the syntax will work in any desktop tool. — William Robertson
– William Robertson, Commented Oct 18, 2021 at 18:51

EdStevens · Accepted Answer · 2021-10-18 19:35:26Z

1

The syntax for the db link from Oracle to MSSQL is the same as for oracle to oracle. But you have to use the Oracle Transparent Gateway (OTG) Use either the OTG for MSSQL (paid) or OTG for ODBC (free). I have a write-up on it, here. But to be a bit more concise, here's how the pieces fit together:

Install the OTG for ODBC. It can really go anywhere that has network connectivity between itself and both databases. If you only have the one connection, I find it easiest to put it on the same server as the MSSQL db. It will go into its own oracle home, and run its own listener.

The 'using' clause will refer to a tnsnames.ora entry, located on the same server as the oracle database containing the link. Remember, at the point of using the db link, the oracle database is then acting as a client to the remote db. A typical tns entry would be like this:

NORTHWIND =
   (DESCRIPTION = 
     (ADDRESS_LIST = 
       (ADDRESS = (PROTOCOL = TCP)
       (HOST=oraotghost)(PORT=1521))
     )
     (CONNECT_DATA =
        (SID=northwind)(HS=OK)
     )
   )

The tnsnames entry will point to HOST where the OTG is installed, and the port the OTG listener is on. This listener works exactly the same as the one for the database, and there should be no reason not to use the default port of 1521.

The OTG listener will need a SID_LIST entry, like this (actual names of SID and HOME are just examples):

SID_DESC=
   SID_NAME=northwind
   ORACLE_HOME=D:\APP\OTG
   PROGRAM=dg4odbc

Next is a file to detail the connection between oracle and the ODBC DSN. In the above example, that file would be named 'initnorthwind.ora' And would look like this:

HS_FDS_CONNECT_INFO=northwind

And this final use of 'northwind' then names an ODBC system DSN that further points to your MSSQL database.

Again, read thru the linked article for more detail and more explanation of how the pieces fit together. There I only show all the relationships. I do not get int the mechanics of configuring ODBC, etc. I assume anyone doing this knows HOW to create the pieces, as long as they understand what pieces need to be created and how they all relate to one another.

answered Oct 18, 2021 at 19:35

EdStevens

3,9203 gold badges12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Swoop Over a year ago

Thank you for this detailed answer! But I am not quite sure If I am allowed to perform all these steps within the company I am working at. Is there any good and fast alternative? Like creating a bash script and run it with a windows task scheduler? Or shell script running on a unix maschine using cronjob?

EdStevens Over a year ago

And what, exactly, would you expect such a script to do? Oracle Transparent Gateway is the method for an Oracle database to access a non-Oracle database. At the very least some script would have to access the mssql database, extract the data into a csv file, make that file accessible to the oracle server, then use sqlldr or an external table to load the data from there into the database. However, if your company has both databases and is expecting to move data between them, they really should invest in installing the proper infrastructure to do that. You need to sell them on it.

Collectives™ on Stack Overflow

How to load data from SQL Server into Oracle Table using PLSQL Developer

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related