Connect to Oracle from SQL Server

Question

We are migrating an application from Oracle to SQL Server. The vendor is responsible for the majority of the migration efforts, but there is one piece that was developed in-house that I'm having trouble with.

The old application on Oracle has a procedure that collects specific data and then another procedure inserts that data into a staging table in our revenue system (a different Oracle server). This is an Oracle procedure via an Oracle Public Database Link.

In SQL Server, I'm trying to rewrite this process using SQL Server's Linked Servers. I've created the Linked Server connection to the revenue system and it can connect via that link.

From SQL*Plus, I can issue this statement as that user:

select * from rs.rs_myapp;

And I get results. (rs is the revenue system user, not the user I connect as. rs_MyApp table is the staging table for our data in their system.)

But if I attempt to do this in SQL Server:

select * from [rstest-Link]..rs.rs_myapp

I get this message:

Msg 7314, Level 16, State 1, Line 50 The OLE DB provider "OraOLEDB.Oracle" for linked server "rstest-Link" does not contain the table ""rs"."rs_myapp"". The table either does not exist or the current user does not have permissions on that table.

I can do this, however:

select * from openquery([rstest-Link], 'select count(*) from rs.rs_myapp')

And that gets results back, showing that the link does work.

However, I cannot complete the SQL Server procedure, because of that 7314 error. The procedure is really basic, it has exactly one line of code in it:

Insert Into [rstest-Link]..rs.rs_myapp (ACCOUNT_NUMBER, FROM_DATE, TO_DATE, 
        Data1, Data2, SAMPLE_DATE, Content1, Content2) 
    select ACCOUNT_NUMBER, FROM_DATE, TO_DATE, MyData1, MyData2, SAMPLE_DATE, MyContent1, MyContent2 
    FROM dbo.RS_INTERFACE;

But without resolving the 7314 error, there's no way to create the procedure.

So how do I do this? Am I going to have to use a cursor and issue some sort of code for each row, like:

INSERT OPENQUERY ([rstest-Link], 'SELECT ... FROM ...')  
VALUES ('...'); 

Answer 1

Oracle uses a case-sensitive catalog, and hides that ugly fact from you by silently converting non-quoted identifiers to all caps, both in DDL and DML.

The name of your table is actually RS.RS_MYAPP, and SQL Server may be sending the identifier to Oracle as "rs"."rs_myapp", which would fail at Oracle.

So try

select * from [rstest-Link]..RS.RS_MYAPP

Answer 2

First: I'll attempt to answer the question itself: When you reference the ORACLE tables directly, it's [LinkedServerName]..[Schema].[Table] Is that the method you're using?

If so, ensure that the user that's logging in via the Linked Server is set up on the ORACLE side to use RPC Out. (via the linked server settings)

^^Edit: Ignore the above. David was spot on with his answer.

Now some thoughts:

I've been working with SQL Server --> ORACLE via linked server for a few years now, and I can definitely say that for pure ease of use and stability, writing the query out in ORACLE SQL / PL/SQL and using OPENQUERY() has worked much better for me than using the linked server's tables and writing the query in T-SQL.

Why?

1) Well, when you write it in ORACLE SQL and pass it through the OPENQUERY, it's the ORACLE engine that's doing the heavy lifting, and SQL Server isn't having to fight its way through Oracle's optimizer. SQL Server just waits for the query to complete and accepts the results.

2) You can optimize the query on the ORACLE side and just use it on the SQL Server side when you're done. SQL Server's optimizer just tells me 100% - Remote query and leaves it at that.

3) Issues (with permissions, optimization, etc.) are much easier to diagnose if you don't have to sift through T-SQL.

4) You can still manipulate the results using T-SQL when they're returned (in the outer SELECT statement. This means you can use BOTH Oracle AND T-SQL Native functions to process your query. TRUNC, SUBSTR, INSTR, CHARDINDEX, they're all available.

I was only well versed in T-SQL when I first started having to use it to query from ORACLE, and let me tell you, as soon as I learned PL/SQL I started using it in OPENQUERY statements in SQL Server instead. The separation of duties concept holds true for these as well. They're much happier when they're not fighting.