Accessing Oracle From SQL Server

SQL Server supports linked server to execute SQL commands against another database or data file. With the help of Oracle SQL*Net, connection from SQL Server to Oracle is fairly easy (comparing to Oracle to SQL Server using Oracle Generic Connectivity -- I'll discuss it later).

Below shows the steps. The target Oracle database is named TEST and with sample schema HR, password HR.

1. You need to install Oracle SQL*Net on the SQL Server box. The simple way is to install the Oracle client.

2. Use Oracle Net Configuration Assistant to configure a SQL*Net local service name. Or you can simply add an entry into file $ORACLE_HOME/network/admin/tnsname.ora:

TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ORACLE_HOST)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORACLE_SID)
)
)

Replace ORACLE_HOST and ORACLE_SID with your oracle server name and SID or service name.

3. Add linked server in SQL Server. Login as sa:

p_addlinkedserver @server = 'ora_test'
, @srvproduct = 'Oracle'
, @provider = 'MSDAORA'
, @datasrc = 'test'

Where ora_test is the linked server you're going to created (or you can name it whatever you want), test is the local service name of SQL*Net. Keep product and provider Oracle and MSDAORA respectively.

4. Map login on SQL Server, this will give the login permission to access the linked Oracle server.

sp_addlinkedsrvlogin @rmtsrvname = 'ora_test'
, @useself = false
, @locallogin = 'analyst'
, @rmtuser = 'hr'
, @rmtpassword = 'hr'

The example above gives analyst (a SQL Server login) permission to access hr schema in Oracle.

5. Test

select * from ora_test..HR.EMPLOYEES

You'll see:

100 Steven King SKING 515.123.4567 1987-06-17 00:00:00.000 AD_PRES 24000.00 NULL NULL 90
...

The object names in Oracle are in upper case. So you'll need to spell the schema and object names in upper case unless they're defined in lower or mixed case. In the example above, if you want select * from ora_test..HR.employees, you'll be informed that such table does not exist. Because SQL Server will translate the SQL to: select * from ora_test..HR."employees".

Trailing spaces in VARCHAR

I just noticed that many people are misunderstanding the trailing spaces in VARCHAR field. The truth is they're not trimmed automatically. At least in the databases I know, Oracle, Sybase, SQL Server, DB2, MySQL, etc.

Many know that when you save "abc" into char(5), two trailing spaces will be padded. And when you save "abc" into varchar(5), no space will be added.

How about save "abc " into varchar(5)? Well, the database will keep the trailing space. This sounds reasonable. Many make mistakes when copying data from char field to varchar field or changing the char type to varchar type. Remember, you'll need to trim, at least right trim them.