不再需要从套接字读取数据 [英] No more data to read from socket

问题描述

我的过程如下:

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;

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

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

不再需要从套接字读取数据
不再需要从套接字读取数据
不再需要从套接字读取数据
不再需要从套接字读取数据
不再需要从套接字读取数据
不再需要从套接字读取数据
不再需要从套接字读取数据
不再需要从套接字读取数据
...(很少有'没有更多的数据要从套接字读取')

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错误:对等连接重置:套接字写入错误
进程已退出.

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

有趣的是，当我注释掉整个内部循环时，该过程将运行而没有错误.因此，我知道内部循环有问题(我尝试仅在内部循环中注释掉insert语句，并得到了相同的错误).

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).

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

Both my localdb and dblink1 databases have same version:

Oracle Database 11g企业版11.2.0.1.0版-64位生产

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

PL/SQL版本11.2.0.1.0-生产

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0生产

CORE 11.2.0.1.0 Production

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

TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

NLSRTL版本11.2.0.1.0-生产

NLSRTL Version 11.2.0.1.0 - Production

推荐答案

用于解决不再需要从套接字读取数据"错误的通用建议.

这些错误通常是由另一个严重错误引起的，例如ORA-600错误.这个问题非常严重，服务器进程崩溃了，甚至无法向客户端发送正确的错误消息. (造成这些错误的另一个常见原因是由SQLNET.EXPIRE_TIME或其他导致旧会话中断的进程引起的网络断开连接.)

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.)

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

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

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

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

找到特定的错误消息和详细信息之后，请转到support.oracle.com.使用"ora-600工具"，然后在ORA-600消息后查找第一个数字.

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.

通常会有一篇或多篇有关该特定类型的ORA-600错误的文章.使用确切的版本和平台来缩小可能的错误列表. (但是，如果本文中的受影响的版本"错误，请不要感到惊讶.Oracle的"x.y版本已修复"的主张并不总是正确的.)

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.)

文章通常会更详细地说明问题的发生方式，可能的解决方法以及通常涉及补丁或升级的解决方案.

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

实际上，您很少想解决这些问题. 典型"建议是与Oracle支持部门联系，以验证您确实存在相同的问题，获取补丁程序，获取许可并降低环境，然后应用补丁程序.然后可能意识到该补丁不起作用.恭喜，您只是浪费了很多时间.

In practice you rarely want to solve these 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中有很多功能，几乎总是有另一种方式来实现.如果代码最终看起来有点怪异，请添加一条注释以警告将来的程序员:此代码看起来怪异，以避免错误X，该错误应在版本Y中修复."

Instead, you can usually avoid the 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."

如果这确实是您的整个过程，则应将其替换为以下内容:

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;

通常，如果可能的话，您应该始终在SQL中执行操作.特别是如果您可以避免打开许多游标.还有尤其是，如果您可以避免向远程数据库打开许多游标.

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

这篇关于不再需要从套接字读取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！