Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Oracle Link

By Adrian Small,

LINKED SERVERS AND DISLOCATED THINKING

BACKGROUND

I decided to write this article after a few days of agony trying to connect a SQL Server instance to an Oracle database. This is not intended to be a technically definitive solution to the issue of linking SQL Server to Oracle but the article is intended to reflect my experiences, some Gotchas & what I did to rectify them in order to get our databases talking to each other. I have read a number of articles, which detail some of the problem & solutions I arrived at below (OPENQUERY & OPENROWSET) but there seem to be subtleties within some of these problems that readers may not be aware of. If nothing else, I hope that this article might save other people repeating the same pain that I had to endure.

STRUCTURE OF THE ARTICLE

This article has been divided into two main parts:

  1. some general notes about setting up the linked server
  2. the GOTCHAS that I found and the solutions

My Environment

The Operating system, Oracle & SQL Server databases are as follows in my testing:

  1. Oracle: AIX Version 5.2 & Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
  2. SQL Server: Windows 2000 Advanced Server (5.00.2195) Service Pack 4 & SQL Server Enterprise Edition 8.00.194

SETTING UP

The initial setting up consisted of:

  • adding an entry to the hosts file in Windows on the SQL Server machine so that the name of the Oracle database server could be resolved against the IP address.
  • Installing the Oracle client tools on the SQL Server machine and checking that I could establish a connection into the Oracle database via both Oracle Net Manager & SQL Plus.

This established that I had connection from the SQL Server database machine into the Oracle database. The next step would be to add Oracle as a linked server and to query the Oracle tables. Again, this was comparatively straightforward, once Books Online had been read fairly thoroughly and the examples read and understood.

GOTCHAS AND LESSONS LEARNT

This next section deals primarily with the Gotchas that I encountered and the solutions that I arrived at to fix them.

Case Sensitivity

Having successfully created the linked server with no error messages, I tried to query dbatest_table table in the dbatest_schema:

select * from dbatest_server..dbatest_schema.dbatest_table

Which returned the error:

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'dbatest_server ' does not contain table '"dbatest_schema"."dbatest_table"'.
The table either does not exist or the current user does not have permissions on that table.

After some more pondering, I remembered that Oracle stores all its table name in upper case. Out of curiosity, we tried to use the ‘upper’ function but that didn’t solve the problem either:

Server: Msg 155, Level 15, State 1, Line 1
'dbatest_table' is not a recognized OPTIMIZER LOCK HINTS option.

However, the following query worked perfectly:

select *
 from dbatest_server..DBATEST_SCHEMA.DBATEST_TABLE

Lesson Number 1: write all Oracle table names in UPPER CASE.

2. What’s in a Registry?

Books Online mentioned that the client's registry must be modified by running a registry file from a command line. The file is to be found in: “C:\Program Files\Common Files\System Files\OLE DB”. However, I did not find that this was causing us a problem and I am still using the Registry settings for an Oracle 7 database against an Oracle 9.2 database. The application is not yet running live and so may yet cause us plenty of problems but I have nonetheless managed to establish a connection from SQL Server to Oracle using old Registry keys.

Oracle Client

Microsoft Windows 2000

7.x

[HKEY_LOCAL_MACHINE\SOFTWARE [HKEY_LOCAL_MACHINE\SOFTWARE \Microsoft\TransactionServer Microsoft\MSDTC\MTxOCI] \Local Computer\My Computer] "OracleXaLib"="xa73.dll" "OracleXaLib"="xa73.dll" "OracleSqlLib"="SQLLib18.dll" "OracleSqlLib"="SQLLib18.dll" "OracleOciLib"="ociw32.dll"

Lesson Number 2: the Registry settings might be a factor but for us they weren’t.

3. Not all roads lead to Rome: problems with datatypes

First, NUMBER, precision & scale…………….. and the Four-Part Query. The DBATEST_TABLE was created on the Oracle database with the following column definitions:

Name	                                  Null?    Type
----------------------------------------- -------- ----------------------------
INTID		                          NOT NULL VARCHAR2(10)
FILEID		                          NOT NULL CHAR(1)
PROCID	                                  NOT NULL VARCHAR2(10)
STATUS NUMBER

Running the following four-part query……….

select * from dbatest_server..DBATEST_SCHEMA.DBATEST_TABLE

…returned the following error:

Server: Msg 7356, Level 16, State 1, Line 1
OLEDB provider 'MSDAORA' supplied inconsistent metadata for a column.
Metadata information was changed at execution time.

The Microsoft website referred us to Knowledge Base article 251238 (http://support.microsoft.com/kb/251238)). Needless to say, following the steps in the article failed to reproduce the error message & the query worked perfectly! Dammit.

However, further attempts to query the Oracle database revealed that OPENROWSET & OPENQUERY both worked fine against DBATEST_TABLE and returned the correct data.

SELECT * FROM OPENQUERY(dbatest_server, 'SELECT * FROM DBATEST_TABLE')
GO
SELECT * FROM OPENROWSET('MSDAORA',
             'NNNN.NNNN.NNNN.NNN';'DBATEST_SCHEMA';'DBATEST_SCHEMA', 'SELECT *
                 FROM DBATEST_TABLE')
GO
('NNNN.NNNN.NNNN.NNN' = the top line from the TNSNAMES.ora file)

Thus, I was presented with a dilemma: why did OPENROWSET & OPENQUERY work but the four-part query fail? Further investigation revealed the problem to be the NUMBER column in Oracle. If the precision and scale were specified during the table creation, the four-part query works fine. No precision and scale resulted in errors.

Lesson Number 3: the four-part query can be unreliable. Use OPENQUERY & OPENROWSET in preference.

Next, looking at NUMBER, precision & scale……. and truncation. In Oracle, NUMBER datatypes are truncated to the right of the decimal point. In SQL Server, the precision & scale are shown in their entirety and there is no truncation. In the following example, the Oracle table was created with the precision & scale being specified (NUMBER 15,10). The Four-Part Query, OPENROWSET & OPENQUERY all returned the same results:

Oracle results

SQL Server results

4.01

4.0100000000

4.02

4.0200000000

4.03

4.0300000000

4.04

4.0400000000

Lesson Number 4: be prepared for data to be returned in SQL Server in a slightly different format to Oracle.



Third, let's look at NUMBER, precision & scale…………. and rounding. In the next example, the Oracle table was created without precision & scale being specified but with decimals being put into the column values. The Four-Part Query failed, as expected, but it was found that OPENROWSET & OPENQUERY rounded the values that they returned in some quite unexpected ways:

Oracle results

SQL Server results

4.01

4.0099999999999998

4.02

4.0200000000000005

4.03

4.0300000000000002

4.04

4.04

4.05

4.0499999999999998

4.06

4.0600000000000005

4.07

4.0700000000000003

4.08

4.0800000000000001

4.09

4.0899999999999999

However, when precision & scale were specified, the values returned by OPENROWSET & OPENQUERY were correct:

Oracle results

SQL Server results

4.01

4.0100000000

4.02

4.0200000000

4.03

4.0300000000

4.04

4.0400000000

4.05

4.0500000000

4.06

4.0600000000

4.07

4.0700000000

4.08

4.0800000000

4.09

4.0900000000

Lesson Number 5: always use precision and scale. The four-part query should work and there won’t be any strange rounding in SQL Server.

Lastly, NUMBER, precision & scale…………. and commits. In the final example, the Oracle table was created with precision & scale, values were entered and a query run against the table to check that the data had been successfully entered; they were all returned successfully. However, the same query when run against SQL Server only returned the column heading and no values.

Lesson Number 6: always remember to commit in Oracle. For people who always use SQL Server, this is easily over-looked but can lead to more unnecessary head-scratching.

CONCLUSION

I am sure that those who have reached the end of this article have realised that it not the most interesting subject. However, there are catches associated with linked servers and they can be quite confusing.

One of the main conclusions from this article is that the error messages returned by SQL Server can be misleading; plenty of reading around the subject is necessary in order to gain a good understanding of the potential pitfalls.

Secondly, in my experience of linked servers so far, there is no substitute for good configuration and, especially, good administration. Properly defining column and datatypes can prevent some extremely odd errors from occurring and will save a considerable amount of DBA pain trying to work out why an apparently perfectly sensible query is behaving so unpredictably.

Total article views: 17265 | Views in the last 30 days: 11
 
Related Articles
FORUM

Query about Linked Server to Oracle

Query about Linked Server to Oracle

FORUM

Linked Server to Oracle

Linked server to Oracle

FORUM

100-row limitation using Oracle Linked Server

Oracle Linked Server limits query resultsets to 100 rows

FORUM

Proble with oracle numbers

user oracle number datatype in ssis package..

FORUM

Quicken up a SQL 2005 to Oracle Query

Sql to Oracle seelct query

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones