Query hangs in fetch_one_row_as_array

[pascalbetz]

I've got some troubles reading from an Oracle DB using ruby-oci8 I have following code:

OCI8.properties[:recv_timeout] = 300 # seconds
o = OCI8.new( ... )
o.exec("SELECT * FROM sometable") do |r|
  # do something with data
end
...
...

After reading some hundred rows this blocks inside the exec block. And after the 300s i get:

stmt.c:282:in oci8lib_240.so: ORA-03113: end-of-file on communication channel (OCIError)
Process ID: 50205
Session ID: 270 Serial number: 25025
    from /usr/local/bundle/gems/ruby-oci8-2.2.5.1/lib/oci8/cursor.rb:544:in `fetch_one_row_as_array'
    from /usr/local/bundle/gems/ruby-oci8-2.2.5.1/lib/oci8/cursor.rb:156:in `fetch'
    from foo.rb:17:in `<main>'

Im using

ruby-oci8 2.2.5.1
ruby 2.4.2
instaclient/sdk/sqlplus 12.1.0.2.0 

Any Ideas how I can track this further?

[cjbj]

Check Oracle client and Database trace files (search for 'ADR'). Memory or resource issues? Network Issues?

[kubo]

@pascalbetz Could you confirm the Oracle client version by the following script just to make sure?

require 'oci8'
puts OCI8::oracle_client_version

Could you run the SQL statement without hangs on sqlplus when SQLNET.RECV_TIMEOUT is in sqlnet.ora?

Create $HOME/.sqlnet.ora, $TNS_ADMIN/sqlnet.ora or $ORACLE_HOME/network/admin/sqlnet.ora containing the following.

SQLNET.RECV_TIMEOUT=300

And then execute sqlplus.

$ sqlplus ...
SQL> select * from sometable;

[pascalbetz]

@cjbj, @kubo thanks for the suggestions. Will do that on monday. I don't have access to the DB right now. Enjoy the weekend.

[pascalbetz]

Quick status update:

require 'oci8' 
puts OCI8::oracle_client_version

confirms the client version: 12.1.0.2

Something I did not describe properly (but I hope does not make a difference): i'm querying a View, not a Table. The receive timeout is 500s because it takes quite some time to build this view (first row is usually after 300s) The view is about 120k rows and 100columns. With some CLOBs and large varchar fields. It seems that I can fetch more rows when selecting fewer columns and vice versa before it hangs.

I tried with sqlplus and this fails as well. Also I tried and tested with ruby-oci8 from a local Docker container (had to tunnel the DB access through several hosts, which slowed it down a bit) and this worked as well. So I guess it is not related to ruby-oci8. Thanks for the suggestion to check with sqplus directly. I'll keep on investigating network and other resources in the deployment docker env.

[pascalbetz]

Since this is not a problem of ruby-oci8 I'm closing this. Thanks for your help!

[pascalbetz]

@kubo FYI:

I've downgraded to client 11.2 and this seems to work. If someone can give any pointers how I could get it running with latest client i'd be grateful. I'll report back here if I find anything new.