oracle 没有更多的数据要从套接字读取

No more data to read from socket

提问by Aniket

My procedure looks like this:

我的程序是这样的：

Declare 
       cur_1        Sys_Refcursor;
       cur_2        Sys_Refcursor;
       v_1          VARCHAR2(30);
       v_2          VARCHAR2(30);
       v_3          VARCHAR2(30);
       v_4          VARCHAR2(30);
Begin
       OPEN cur_1 for Select * from tab1@dblink1;
       Loop
           Fetch cur_1 into v_1, v_2;
           EXIT WHEN cur_1%NOTFOUND;   
           OPEN cur_2 for Select * from tab2@dblink1 where col1 = v_1 and col2 = v2;
           Loop
               Fetch cur2 into v_3, v_4;
               Exit when cur_2%notfound;    
               INSERT INTO local.tab3 values (v_1,v_2, v_3, v_4);
           END Loop;
           close cur_2;
       End Loop;
       close cur_1; 
END;

The abobe procedure compiles, but when I run it I get following error:

abobe 程序可以编译，但是当我运行它时，出现以下错误：

No more data to read from socket
No more data to read from socket
No more data to read from socket
No more data to read from socket
No more data to read from socket
No more data to read from socket
No more data to read from socket
No more data to read from socket
...(Few more 'No more data to read from socket')

IO Error: Connection reset by peer: socket write error
Process exited.

没有更多的数据要从套接字读取
没有更多的数据要从套接字读取
没有更多的数据要从套接字读取
没有更多的数据要从套接字读取
没有更多的数据要从套接字读取
没有更多的数据要从套接字读取
没有更多的数据要从套接字读取
否要从套接字读取更多数据
......（不再有'没有更多数据要从套接字读取'）

IO 错误：对等方重置连接：套接字写入错误
进程已退出。

Interesting thing is when I comment out the entire inner loop the procedure runs without error. So I know something is wrong with the inner loop (I tried commenting only the insert statement inside the inner loop and got the same error).

有趣的是，当我注释掉整个内部循环时，程序运行没有错误。所以我知道内循环有问题（我尝试只评论内循环内的插入语句并得到相同的错误）。

Both my localdband dblink1databases have same version:

我的localdb和dblink1数据库都具有相同的版本：

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

Oracle 数据库 11g 企业版 11.2.0.1.0 版 - 64 位生产

PL/SQL 版本 11.2.0.1.0 - 生产

核心 11.2.0.1.0 生产

适用于 64 位 Windows 的 TNS：版本 11.2.0.1.0 - 生产

NLSRTL 版本 11.2.0.1.0 - 生产

回答by Jon Heller

Generic advice for troubleshooting "No more data to read from socket" errors.

对“无法从套接字读取更多数据”错误进行故障排除的一般建议。

These errors are usually caused by another serious error, such as an ORA-600 error. A problem so serious that the server process crashed and could not even send a proper error message to the client. (Another common reason for these errors is a network disconnection caused by SQLNET.EXPIRE_TIME or some other process that kills old sessions.)

这些错误通常是由另一个严重错误引起的，例如 ORA-600 错误。一个如此严重的问题，以至于服务器进程崩溃，甚至无法向客户端发送正确的错误消息。（这些错误的另一个常见原因是由 SQLNET.EXPIRE_TIME 或其他一些杀死旧会话的进程引起的网络断开连接。）

Look at the Alert Log to find out the original error message.

查看警报日志以找出原始错误消息。

Look for the file alert_[name].log in this directory: select value from v$parameter where name = 'background_dump_dest';

在此目录中查找文件 alert_[name].log： select value from v$parameter where name = 'background_dump_dest';

After you find the specific error message and details, go to support.oracle.com. Use the "ora-600 tool" and then lookup the first number after the ORA-600 message.

找到具体的错误消息和详细信息后，请访问 support.oracle.com。使用“ora-600 工具”，然后查找 ORA-600 消息后的第一个数字。

There will usually be one or more articles for that specific type of ORA-600 error. Use the exact version and platform to narrow down the possible list of bugs. (But don't be surprised if the "Versions affected" in the article are wrong. Oracle's claims of "fixed in version x.y" are not always true.)

对于特定类型的 ORA-600 错误，通常会有一篇或多篇文章。使用确切的版本和平台来缩小可能的错误列表。（但如果文章中的“受影响的版本”是错误的，请不要感到惊讶。Oracle 的“已在 xy 版本中修复”的说法并不总是正确的。）

The articles typically explain in more details how the problem happened, possible workarounds, and a solution that usually involves a patch or upgrade.

这些文章通常会更详细地解释问题是如何发生的、可能的解决方法以及通常涉及补丁或升级的解决方案。

In practice you rarely want to solvethese problems. The "typical" advice is to contact Oracle Support to verify you really have the same problem, get a patch, get permission and bring down the environment(s), and then apply the patch. And then probably realize the patch doesn't work. Congratulations, you just wasted a lot of time.

在实践中，您很少想解决这些问题。“典型”建议是联系 Oracle 支持以验证您是否确实遇到了同样的问题、获取补丁、获得许可并关闭环境，然后应用补丁。然后可能意识到补丁不起作用。恭喜你，你浪费了很多时间。

Instead, you can usually avoidthe problem with a subtle change to the query or procedure. There are a lot of features in Oracle, there's almost always another way to do it. If the code ends up looking a bit weird, add a comment to warn future programmers: "This code looks weird to avoid bug X, which should be fixed in version Y."

相反，您通常可以通过对查询或过程进行细微更改来避免该问题。Oracle 中有很多特性，几乎总有另一种方法来实现。如果代码最终看起来有点奇怪，请添加注释以警告未来的程序员：“这段代码看起来很奇怪，以避免错误 X，应该在版本 Y 中修复。”

Specific advice for this code

对此代码的具体建议

If that's really your entire procedure, you should replace it with something like this:

如果这真的是你的整个过程，你应该用这样的东西替换它：

insert into local.tab3(col1, col2, col3, col4)
select tab1.col1, tab1.col2, tab2.col1, tab2.col2
from tab1@dblink1 tab1
join tab2@dblink1 tab2
    on tab1.col1 = tab2.col1
    and tab1.col2 = tab2.col2;

In general, you should always do things in SQL if possible. Especially if you can avoid opening many cursors. And especiallyif you can avoid opening many cursors to a remote database.

通常，如果可能，您应该始终使用 SQL 执行操作。特别是如果您可以避免打开许多游标。而且特别是如果你能避免打开许多光标到远程数据库。

回答by Rob van Laarhoven

As jonearles mentioned you should write this in one SQL statement.

正如 jonearles 提到的，您应该将其写在一个 SQL 语句中。

If you insist on using PL/SQL : you are doing way too much work yourself, declaring variables, open cursors, looping, assigning variables. Consider this PL/SQL:

如果你坚持使用 PL/SQL：你自己做了太多的工作，声明变量、打开游标、循环、分配变量。考虑这个 PL/SQL：

begin
  for c1 in (select * from tab1@dblink1)
  loop
    for c2 in (Select * from tab2@dblink1 where col1 = c1.col1 and col2 = c1.col2)
    loop
      insert into local.tab3 values (c1.col1,c1.col2,c2.col1,c2.col2);
    end loop;
  end loop;
end;
/