问题描述

我的程序是这样的:

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;

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

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 数据库 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

推荐答案

解决No more data to read from socket"错误的一般建议.

这些错误通常是由另一个严重错误引起的，例如 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.

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