No more data to read from socket

Question

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 the 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
... (A few "There is no more data to read from the socket")

I / O error: connection reset by peer: socket write error

The process is completed.

Interestingly, when I comment on the entire inner loop, the procedure runs without errors. Therefore, I know that something is wrong with the inner loop (I tried to comment only on the insert statement inside the inner loop and got the same error).

Both localdb and dblink1 databases have the same version:

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

Answer 1

General troubleshooting guidelines for "Read no more socket data."

These errors are usually caused by another serious error, such as the ORA-600 error. The problem is so serious that the server process crashed and could not even send the correct error message to the client. (Another common cause of these errors is a network outage caused by SQLNET.EXPIRE_TIME or another process that kills old sessions.)

Look at the warning log for the original error message.

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

Once you find a specific error message and detailed information, go to support.oracle.com. Use the ora-600 tool, and then find the first number after the ORA-600 message.

Typically, for one type of ORA-600 error, there is usually one or more entries. Use the exact version and platform to narrow down the list of possible errors. (But don't be surprised if the "Versions affected" in this article are incorrect. Oracle's "fixed in version xy" requirements are not always true.)

Articles typically explain how this problem occurred, possible workarounds, and a solution that usually includes a fix or update.

In practice, you rarely want to solve these problems. The “typical” tip is to contact Oracle Support to make sure that you really have the same problem, get the patch, get permission and hide the environment (s), and then apply the patch. And then, probably, understand that the patch is not working. Congratulations, you just spent a lot of time.

Instead, you can usually avoid the problem of subtlely modifying a request or procedure. There are many possibilities in Oracle, there is almost always another way to do this. If the code turns out to be a little strange, add a comment to warn future programmers: "This code looks strange to avoid an X error that needs to be fixed in version Y."

Specific tip for this code

If this is really your 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 something in SQL, if possible. Especially if you can avoid opening many cursors. And especially if you can avoid opening many cursors in a remote database.

Answer 2

As mentioned in jonearles, you should write this in a single SQL statement.

If you insist on using PL / SQL: you work too much, declare variables, open cursors, loop, assign variables. Consider this 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; /