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.
我在SQL Server中有一个链接服务器设置来命中Oracle数据库。我在SQL Server中有一个查询,它使用点表示法连接到Oracle表。我收到Oracle的“No Data Found”错误。在Oracle方面,我正在访问一个表(不是视图),并且不涉及存储过程。
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.
首先,当没有数据时,我应该只获得零行而不是错误。其次,在这种情况下实际上应该有数据。第三,我只看到PL / SQL代码中的ORA-01403错误;永远不会在SQL中。
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".
这是完整的错误消息:链接服务器“OM_ORACLE”的OLE DB提供程序“OraOLEDB.Oracle”返回消息“ORA-01403:未找到数据”。消息7346,级别16,状态2,行1无法从OLE DB提供程序“OraOLEDB.Oracle”获取链接服务器“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;
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.
我无法理解为什么我发现“没有找到数据”错误。下面的查询使用相同的Oracle表并且不返回任何数据但我没有收到错误 - 我只是没有返回任何行。
select * from [OM_ORACLE]..[OM].[DOCUMENT_UPLOAD] where documentUploadID = -1
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.
下面的查询返回数据。我刚刚从连接中删除了一个SQL Server表。但是删除批处理表不会更改从batchFile返回的行(在两种情况下都是271行 - batchFile中的所有行都有批处理条目)。它仍应将相同的batchFile行连接到相同的Oracle行。
select *
from eopf.BatchFile bf
left outer join [OM_ORACLE]..[OM].[DOCUMENT_UPLOAD] du
on bf.ReferenceID = du.documentUploadID;
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).
此查询返回5行。它应该与原始查询中的5相同。 (我不能使用它,因为我需要批处理和batchFile表中的数据)。
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);
Has anyone experienced this error?
有没有人遇到过这个错误?
5 个解决方案
#1
1
Today I experienced the same problem with an inner Join. As creating a Table Valued Function suggested by codechurn or using a Temporary Table suggested by user1935511 or changing the Join Types suggested by cymorg are no options for me, I like to share my solution.
今天我在内部加入时遇到了同样的问题。由于创建由codechurn建议的表值函数或使用user1935511建议的临时表或更改cymorg建议的加入类型对我来说没有选择,我想分享我的解决方案。
I used Join Hints to drive the query optimizer into the right direction, as the problem seems to rise up from nested loops join strategy with the remote table locally . For me HASH, MERGE and REMOTE join hints worked.
我使用Join Hints将查询优化器推向了正确的方向,因为问题似乎是从嵌套循环连接策略与本地远程表一起提升的。对我来说,HASH,MERGE和REMOTE联接提示有效。
For you REMOTE will not be an option because it can be used only for inner join operations. So using something like the following should work.
对于您REMOTE将不是一个选项,因为它只能用于内部联接操作。所以使用类似下面的东西应该有效。
select *
from eopf.Batch b
join eopf.BatchFile bf
on b.BatchID = bf.BatchID
left outer merge join [OM_ORACLE]..[OM].[DOCUMENT_UPLOAD] du
on bf.ReferenceID = du.documentUploadID;
#2
1
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.
我遇到了同样的问题。 Solution1:将Oracle数据库中的数据加载到临时表中,然后加入到临时表中 - 这是一个链接。
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.
从这篇文章的链接中,您可以发现使用左连接可能会出现问题。我已经检查了我的问题,并在更改我的查询后解决了问题。
#3
0
In my case I had a complex view made from a linked table, 3 views based on the linked table and a local table. I was using Inner Joins throughout and this problem manifested. Changing the joins to Left and Right Outer Joins (as appropriate) resolved the issue.
在我的例子中,我有一个由链表创建的复杂视图,基于链表和本地表的3个视图。我一直在使用内部连接,这个问题表现出来了。将连接更改为左侧和右侧外部连接(视情况而定)解决了该问题。
#4
0
I got the same problem and resolved it by avoiding the '=' operator try using
我遇到了同样的问题并通过避免'='运算符尝试使用来解决它
select * from [OM_ORACLE]..[OM].[DOCUMENT_UPLOAD] where documentUploadID < 0
Instead.
Thanks
#5
0
Another way to work around the problem is to pull back the Oracle data into a Table Valued Function. This will cause SQL Server to go out and retrieve all of the data from Oracle and throw it into a resultant table variable. For all intent and purpose, the Oracle data is now "local" to SQL Server if you use the resultant Table Valued Function in a query.
解决该问题的另一种方法是将Oracle数据拉回到表值函数中。这将导致SQL Server外出并从Oracle检索所有数据并将其抛出到结果表变量中。出于所有意图和目的,如果在查询中使用结果表值函数,则Oracle数据现在是SQL Server的“本地”。
I believe the original problem is that SQL Server is trying to optimize the execution of your compound query which includes the remote Oracle query results in-line. By using a Table Valued Function to wrap the Oracle call, SQL Server will optimize the compound query on the resultant table variable returned from the function and not the results from the remote query execution.
我认为最初的问题是SQL Server正在尝试优化复合查询的执行,其中包括远程Oracle查询结果。通过使用表值函数来包装Oracle调用,SQL Server将优化从函数返回的结果表变量的复合查询,而不是远程查询执行的结果。
CREATE function [dbo].[documents]()
returns @results TABLE (
DOCUMENT_ID INT NOT NULL,
TITLE VARCHAR(6) NOT NULL,
LEGALNAME VARCHAR(50) NOT NULL,
AUTHOR_ID INT NOT NULL,
DOCUMENT_TYPE VARCHAR(1) NOT NULL,
LAST_UPDATE DATETIME
) AS
BEGIN
INSERT INTO @results
SELECT CAST(DOCUMENT_ID AS INT) AS DOCUMENT_ID, TITLE, LEGALNAME, CAST(AUTHOR_ID AS INT) AS AUTHOR_ID, DOCUMENT_TYPE, LAST_UPDATE
FROM OPENQUERY(ORACLE_SERVER,
'select DOCUMENT_ID, TITLE, LEGALNAME, AUTHOR_ID, DOCUMENT_TYPE, FUNDTYPE, LAST_UPDATE
from documents')
return
END
You can then use the Table Valued Function as it it were a table in your SQL queries:
然后,您可以使用表值函数,因为它是SQL查询中的表:
SELECT * FROM DOCUMENTS()
SELECT * FROM DOCUMENTS()
#1
1
Today I experienced the same problem with an inner Join. As creating a Table Valued Function suggested by codechurn or using a Temporary Table suggested by user1935511 or changing the Join Types suggested by cymorg are no options for me, I like to share my solution.
今天我在内部加入时遇到了同样的问题。由于创建由codechurn建议的表值函数或使用user1935511建议的临时表或更改cymorg建议的加入类型对我来说没有选择,我想分享我的解决方案。
I used Join Hints to drive the query optimizer into the right direction, as the problem seems to rise up from nested loops join strategy with the remote table locally . For me HASH, MERGE and REMOTE join hints worked.
我使用Join Hints将查询优化器推向了正确的方向,因为问题似乎是从嵌套循环连接策略与本地远程表一起提升的。对我来说,HASH,MERGE和REMOTE联接提示有效。
For you REMOTE will not be an option because it can be used only for inner join operations. So using something like the following should work.
对于您REMOTE将不是一个选项,因为它只能用于内部联接操作。所以使用类似下面的东西应该有效。
select *
from eopf.Batch b
join eopf.BatchFile bf
on b.BatchID = bf.BatchID
left outer merge join [OM_ORACLE]..[OM].[DOCUMENT_UPLOAD] du
on bf.ReferenceID = du.documentUploadID;
#2
1
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.
我遇到了同样的问题。 Solution1:将Oracle数据库中的数据加载到临时表中,然后加入到临时表中 - 这是一个链接。
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.
从这篇文章的链接中,您可以发现使用左连接可能会出现问题。我已经检查了我的问题,并在更改我的查询后解决了问题。
#3
0
In my case I had a complex view made from a linked table, 3 views based on the linked table and a local table. I was using Inner Joins throughout and this problem manifested. Changing the joins to Left and Right Outer Joins (as appropriate) resolved the issue.
在我的例子中,我有一个由链表创建的复杂视图,基于链表和本地表的3个视图。我一直在使用内部连接,这个问题表现出来了。将连接更改为左侧和右侧外部连接(视情况而定)解决了该问题。
#4
0
I got the same problem and resolved it by avoiding the '=' operator try using
我遇到了同样的问题并通过避免'='运算符尝试使用来解决它
select * from [OM_ORACLE]..[OM].[DOCUMENT_UPLOAD] where documentUploadID < 0
Instead.
Thanks
#5
0
Another way to work around the problem is to pull back the Oracle data into a Table Valued Function. This will cause SQL Server to go out and retrieve all of the data from Oracle and throw it into a resultant table variable. For all intent and purpose, the Oracle data is now "local" to SQL Server if you use the resultant Table Valued Function in a query.
解决该问题的另一种方法是将Oracle数据拉回到表值函数中。这将导致SQL Server外出并从Oracle检索所有数据并将其抛出到结果表变量中。出于所有意图和目的,如果在查询中使用结果表值函数,则Oracle数据现在是SQL Server的“本地”。
I believe the original problem is that SQL Server is trying to optimize the execution of your compound query which includes the remote Oracle query results in-line. By using a Table Valued Function to wrap the Oracle call, SQL Server will optimize the compound query on the resultant table variable returned from the function and not the results from the remote query execution.
我认为最初的问题是SQL Server正在尝试优化复合查询的执行,其中包括远程Oracle查询结果。通过使用表值函数来包装Oracle调用,SQL Server将优化从函数返回的结果表变量的复合查询,而不是远程查询执行的结果。
CREATE function [dbo].[documents]()
returns @results TABLE (
DOCUMENT_ID INT NOT NULL,
TITLE VARCHAR(6) NOT NULL,
LEGALNAME VARCHAR(50) NOT NULL,
AUTHOR_ID INT NOT NULL,
DOCUMENT_TYPE VARCHAR(1) NOT NULL,
LAST_UPDATE DATETIME
) AS
BEGIN
INSERT INTO @results
SELECT CAST(DOCUMENT_ID AS INT) AS DOCUMENT_ID, TITLE, LEGALNAME, CAST(AUTHOR_ID AS INT) AS AUTHOR_ID, DOCUMENT_TYPE, LAST_UPDATE
FROM OPENQUERY(ORACLE_SERVER,
'select DOCUMENT_ID, TITLE, LEGALNAME, AUTHOR_ID, DOCUMENT_TYPE, FUNDTYPE, LAST_UPDATE
from documents')
return
END
You can then use the Table Valued Function as it it were a table in your SQL queries:
然后,您可以使用表值函数,因为它是SQL查询中的表:
SELECT * FROM DOCUMENTS()
SELECT * FROM DOCUMENTS()