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 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 Error: Connection reset by peer: socket write error
Process exited.

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 localdb and dblink1 databases have 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

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.

Look for the file alert_[name].log in this directory: 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.

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.

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.

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

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 especially if you can avoid opening many cursors to a remote database.

Answer 2

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

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:

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;
/