Using Oracle to read data from SQL Server

Hi everyone, Andy from Easysoft Limited here, what I want to show you today is how to get data from SQL Server into Oracle.

Now for this we will be accessing SQL Server 2014 from Oracle 12 and getting data across.

This could be quite easily be any SQL Server version from 2008 and any Oracle version from 11.2, the configuration and process is the same. We can obviously get access to data on SQL Server prior to 2008 and Oracle prior to version 11.2 but it will need a different configuration to what I am going to show you today

The plan is also to add more data to the SQL Server table and show this new data back in Oracle.

To start off you'll need access to ensure that you have access to your SQL Server and Oracle databases.

You'll also need the Easysoft SQL Server ODBC Driver available from www.easysoft.com; just download it, install it and request the free 14 day trial licence.

Before we start we need to make some changes to the standard Easysoft config files.

cat /etc/odbc.ini

Here I have changed Mars Connection to Yes. It must be set to "Yes" if you are connecting to SQL Server 2005 or newer.

Easysoft supports all SQL data types from the version of SQL Server from version 7.

Oracle supports data types that are common to most ODBC Drivers, but not some of the SQL Server ones.
If you intend to access VARCHAR(MAX) for example, then there are some additional settings that need to be set up.

Now as I would like to show you VARCHAR(MAX) data moving from SQL Server to Oracle, I have made the relevant change.

As you can see, VarMaxAsLong says "Yes"

We now need to create a link from Oracles Database Gateway (DG4ODBC) to the ODBC datasource that we already have, this is done in an init(sid) oracle file; let's have a look at our file.

cat $ORACLE_HOME/hs/admin/initsqlsrv.ora

Please note that HS_NLS_NCHAR must be set to UCS2

Oracle assume that most database vendors use UTF-8 for wide characters, however Easysoft, like almost all ODBC Driver vendors (including Oracles own ODBC Driver) uses UCS2 for wide characters.

So that's the link between DG4ODBC and the datasource setup correctly.

Next we need to set up the Listener entry

The Oracle Listener waits for incoming requests from the Oracle database. For the Oracle Listener to listen for requests, information about DG4ODBC must be added to the Oracle Listener configuration file, listener.ora

cat $ORACLE_HOME/network/admin/listener.ora

The PROGRAM=dg4odbc tells the Listener that we are wanting to use the Oracle database gateway to communicate with ODBC.

The ENVS=LD_LIBRARY_PATH tells Oracle what library to load

Now that's all set up and OK we need to tell Oracle which server to attach the listener entry to. This is done in tnsnames.ora, and he is the file that we ensured was set up earlier.

cat $ORACLE_HOME/network/admin/tnsnames.ora

The Oracle Listener now needs to be restarted.

lsnrctl stop
lsnrctl start

OK, that's now started the Listener up again.

So we have ensured that all the configuration files are set up correctly.

The Listener and tnsnames.ora both set up OK.


The final step is to create a link from the Oracle database to the "tnsnames.ora" entry.

CREATE PUBLIC DATABASE LINK ODBC CONNECT TO "test" IDENTIFIED BY "test" USING 'sqlsrv_ptr';

Right, that's all done.

Let's access the data in SQL Server now.

select * from demo@odbc;

Where 'demo' is the name of the SQL Server table and 'odbc' is the public database link name.

OK, there we have the data. So to recap, I'm in Oracle and we have managed to read the data from SQL Server.

Let's just add an additional row in that SQL Server table and retrieve the new data in Oracle.

quit;

/usr/local/easysoft/unixODBC/bin/isql.sh -v sqlsrv

insert into demo values (2,'Easysoft')
select * from demo

select * from demo@odbc;

And there is the new data.

Easysoft Limited can help you with your data access ... for our full range of ODBC and JDBC Drivers, Bridges and Gateways visit easysoft.com

Be sure to let us know if you have any comments or questions.

Until next time, thanks again and we'll see you again soon.

---

Closed Caption:

I every sunday again for meself limited
here i just want to show you something
quit today and this that is how we get
data from sequel server into oracle now
for this we're going to accessing sequel
server 2014 from oracle 12 and that's
how we're gonna get data across this
could quite easily done with any sequel
server version from 2008 and the article
version from one point to the
configuration process is exactly the
same can obviously get data access to a
sequel server pride 2008 and the article
and prior to version 11.2 but you'll
need a slight different configurations
i'm going to show you now the place to
once you've done that is to add more day
to sequel server table and show the new
data comes back into miracle
so start off you just need to make sure
you get access the sequel server and
oracle databases you'll also need the
easysoft sequel server odbc driver which
is available from these self.com just
download it install it and request a
free 30-day trial license so we always
start just need to make sure that some
of the changes in standard these files
have been made so few those in 30 bc .
any file
ok you can see here mark connection has
been changed to yes it must be set to
yes
connecting to a sequel server version
2005 are newer now just easy
self-support all sequel data types for
sequel server version 7
oracle supports data types that are
common to most odbc drivers but not so
the sequel server ones so if you intend
to do something like a chess match for
example then there's some additional
settings you need to make sure setup
I'd like to demonstrate to you our charm
acts data moon is equal so torical
self-made relevant change here watch
imax is long be set to yes that's all up
on that now we need to create a link
from the Oracles database gateway the
duty for odbc to the article states are
steps we have already set up in $YEAR
sequel server this is done from its
historical file so let's just take this
lock
I file
ok here you can just see that the HSN LS
and jazz is set to ucs-2 article assumes
that most database based database
vendors use utf-8 far wider characters
however easysoft like almost all other
odbc driver vendors including Oracle's
own odbc driver actually use as ucs-2
for white characters so we wanted to
work correctly ensure that says yes
doing there
so that's the link check between duty
forward to be seeing a sauce so thats
all set correctly now we need to take a
look out the listener entry so let's
listen to . ARA ok the program equals
just history showing she's telling the
listed that i'm going to be using oracle
database gateway to communicate with
odbc the nvs LD library just tells
article what library to load so that's
all set up an ok we now need to tell
article which server to attach the
listener entry to this is done in the
DNS names . our file so let's take a
look at but no okay and everything there
is set up fine
there's a problem ass that was all
that's all be not setup and fine no
problem there
we now need to stop and restart the
listener just to make sure that any
changes we have made have been picked up
okay so i shall stop the list
and all our restart the listener the
command completed successfully
ok so that's now restarted listen up
again so we've already ensured that the
configure of that configuration about
the setup correctly add a listener and
10's names are files are both smoked egg
then the final step is to create a link
from the Oracle database to the DNS
names to our entry so to do that let's
sign onto sequel server
plus he's probably my typing error you
start that again that was obviously a
human error to apologize for that i just
need to now create a crater public
database link inside oracle they go so
that's the commands there you want to
wrong
Notre case looks like it's already been
done before so that's a jubilee entry
there but everything's running fine i'm
i'm happy with that
ok let's access the data in sequel
server now let's all make sure that
everything is hopefully set up okay
they go and that's a strong the that's
some data from single server from the
table called the demo there's just one
contact piece of information there which
is my name and just on that commanded
except the demos name of the server and
odbc is the public database think name
that we created and so if i just go now
I just quit from re call and if I going
to
fine I'll just going to sequel server
insert into demo which is the name of
the table of got set up
so these values so i'll enter another
row not that's because again so the typo
from my perspective to apologize father
insert in to value was 20 so that's
returning okay and just checked the left
is working yourself now got the two so I
mean single server and I'm gonna prove
that I've got the two rows in there with
my name under the name of easysoft so
they're working fine i just need to go
now back in two
some of the work that's better
unixodbc pain is Q thought say they see
call server so I'm now back in a second
sequel server which I didn't mean to do
because I've always check sequel server
so now back in oracle and therefore to
select star wrong demo which is named
the table odbc kool on that so now come
back into our currency in the data data
coming back and then there's new data
you've seen that few little given
everything that which I apologize for
breaks a live demo so you want you
expect sometimes these to go around but
there we have it and we've set up we set
up a original set up a table in sequel
server called demo we make your other
configuration files were correct and all
the links are correct
we're going to article we proved that
works went to new data in sequel server
at it's a sequel so a table and we've
got and just shown they're the new data
coming across from sequel server into
oracle they go easy self-limited can
help you with your day to access for a
full range of odbc and jdbc drivers
bridges and gateways just visit nbc.com
and we just let us know if you have any
comments or questions about what you see
today or anything else about data access
until next time thanks again i will see
you again very soon