连接到oracle的sql server链接服务器，当数据存在时未返回任何数据 [英] sql server linked server to oracle returns no data found when data exists

问题描述

我在SQL Server中有一个链接服务器设置来访问Oracle数据库.我在SQL Server中有一个查询，该查询使用点表示法联接到Oracle表上.我从Oracle收到找不到数据"错误.在Oracle方面，我正在命中一个表(而不是视图)，并且不涉及任何存储过程.

I have a linked server setup in SQL Server to hit an Oracle database. I have a query in SQL Server that joins on the Oracle table using dot notation. I am getting a "No Data Found" error from Oracle. On the Oracle side, I am hitting a table (not a view) and no stored procedure is involved.

首先，当没有数据时，我应该只得到零行而不是错误.
其次，在这种情况下实际上应该有数据.
第三，我仅在PL/SQL代码中看到过ORA-01403错误；从来没有在SQL中使用.

First, when there is no data I should just get zero rows and not an error.
Second, there should actually be data in this case.
Third, I have only seen the ORA-01403 error in PL/SQL code; never in SQL.

这是完整的错误消息:
链接服务器"OM_ORACLE"的OLE DB提供程序"OraOLEDB.Oracle"返回消息"ORA-01403:找不到数据".
消息7346，第16级，状态2，第1行 无法从链接服务器"OM_ORACLE"的OLE DB提供程序"OraOLEDB.Oracle"获取行的数据.

This is the full error message:
OLE DB provider "OraOLEDB.Oracle" for linked server "OM_ORACLE" returned message "ORA-01403: no data found".
Msg 7346, Level 16, State 2, Line 1 Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "OM_ORACLE".

这里有更多详细信息，但由于您没有我的表格和数据，因此可能并不意味着什么.
这是有问题的查询:

Here are some more details, but it probably does not mean anything since you don’t have my tables and data.
This is the query with the problem:

select *
   from eopf.Batch b join eopf.BatchFile bf
                 on b.BatchID = bf.BatchID
          left outer join [OM_ORACLE]..[OM].[DOCUMENT_UPLOAD] du
                 on bf.ReferenceID = du.documentUploadID;

我不明白为什么会收到找不到数据"错误.下面的查询使用相同的Oracle表，不返回任何数据，但我没有收到错误-我没有返回任何行.

I can’t understand why I get a "no data found" error. The query below uses the same Oracle table and returns no data but I don’t get an error - I just get no rows returned.

select * from [OM_ORACLE]..[OM].[DOCUMENT_UPLOAD] where documentUploadID = -1

下面的查询返回数据.我只是从联接中删除了一个SQL Server表.但是删除批处理表不会更改从batchFile返回的行(在两种情况下均为271行– batchFile中的所有行都有一个批处理条目).它仍应将相同的batchFile行连接到相同的Oracle行.

The query below returns data. I just removed one of the SQL Server tables from the join. But removing the batch table does not change the rows returned from batchFile (271 rows in both cases – all rows in batchFile have a batch entry). It should still be joining the same batchFile rows to the same Oracle rows.

select *
from eopf.BatchFile bf
   left outer join [OM_ORACLE]..[OM].[DOCUMENT_UPLOAD] du
      on bf.ReferenceID = du.documentUploadID;

并且此查询返回5行.它应该与原始查询的5相同. (我不能使用它，因为我需要批处理和batchFile表中的数据).

And this query returns 5 rows. It should be the same 5 from the original query. ( I can’t use this because I need data from the batch and batchFile table).

       select *
   from [OM_ORACLE]..[OM].[DOCUMENT_UPLOAD] du
   where du.documentUploadId
   in
   (
   select bf.ReferenceID
   from eopf.Batch b join eopf.BatchFile bf
                 on b.BatchID = bf.BatchID);

有人遇到此错误吗?

推荐答案

我遇到了同样的问题. 解决方案1:将Oracle数据库中的数据加载到临时表中，然后加入该临时表中-这是

I've had the same problem. Solution1: load the data from the Oracle database into a temp table, then join to that temp table instead - here's a link.

来自此帖子

From this post a link you can find out that the problem can be with using left join. I've checked with my problem and after changing my query it solved the problem.

这篇关于连接到oracle的sql server链接服务器，当数据存在时未返回任何数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！