Unable to make oracledb_session work at all

(GitHub community redirected issue; https://github.com/inspec/inspec/issues/3594)

:control_knobs: Description

Again, I need to ask for assistance. After many hours of trying I can't get this to work at all.

My env is this:

Machine A -> Machine B -> Machine C

A is running the inspec tests. It connects to B which, in turn, can access C and has Oracle tools installed and working. A is RHEL, B & C are Windows machines. I am running this, based on the example in the docs, which also fails:

  sql = oracledb_session(user: 'system', password: 'sys', host: '<C's name>', service: '<service>')

 describe sql.query('SELECT NAME FROM v$database;').rows do
    its('count') { should eq 1 }
  end

inspec always returns:

  []
     x  count should eq 1

     expected: 1
          got: 0

     (compared using ==)

Which suggests strongly to me that it isn't connecting to the DB.
I have tried everything I can find (which is almost nothing) and think of. The query works if I run it directly. The user is fine. The details are correct. I tried connecting directly to C, the DB server. No change. I tried various additional parameters: sqlplus_bin , as_db_role . I tried the host IPs instead of names. I've tried other users. I've tried several queries.

Warning and error --log-level output show nothing. debug output shows nothing that looks like a problem to me. The query it shows will run if I run it manually. The login string it generates works on the command line on B. I'm out of ideas. What am I missing here?

:earth_africa: InSpec and Platform Version

inspec 3.0.12

(latest comment from OP before issue close)

I finally found one approach that works. Assuming that you have a sql script on the target server i.e. the one from which sqlplus is being run (Machine B in the flow shown above), containing the query

SELECT NAME AS VALUE FROM v$database;

this works:

  describe command('sqlplus -s System/sys@pdborcl @E:\temp\script.sql') do
    its('stdout') { should include 'ORCL' }
  end
     [PASS]  Command: `sqlplus -s System/sys@pdborcl @E:\temp\script.sql` stdout should include "ORCL"

It means that I have to ship the sql script there by some other means and I don't have the row and column etc. control but at least I can test something, now.