oracle-enhanced: Strange error: OCIError: ORA-01008: not all variables bound

Steps to reproduce

first request after application start or restart.

Expected behavior

After migrating the application from Rails 6.1 to 7 I get this error

Actual behavior

The application use a legacy DB, so Arel is used most time to do complex SQL queries. If the first query after start is a query with UNION ALL, this query fail. If before that you run a simpler one, all is working as expected.

example of query (use ArelHelpers::QueryBuilder):

    def cdpcomponents(neturef = nil, nveretu = nil, start_datetime = nil, include_transferred = nil, nhut = nil)
      left_query = cdp_components1(neturef, nveretu, start_datetime, include_transferred, nhut)
      right_query = cdp_components2(neturef, nveretu, start_datetime, include_transferred, nhut)
      # crete a new relation
      ::Cmdmary::Etudprix.all.select(Arel.star)
                         .from("(#{left_query.query.to_sql} UNION ALL #{right_query.query.to_sql})")
                         .order('nhut, cmat')
    end

it fail with:

OCIError: ORA-01008: not all variables bound
Array (163 elements)
stmt.c:265:in oci8lib_300.so
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/ruby-oci8-2.2.11-x64-mingw32/lib/oci8/cursor.rb:131:in `exec'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:146:in `exec'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:41:in `block (2 levels) in exec_query'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:276:in `block in with_retry'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:433:in `with_retry'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/oci_connection.rb:101:in `with_retry'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:275:in `with_retry'
D:/program/dvlt/ruby/Ruby30-x64/lib/ruby/gems/3.0.0/gems/activerecord-oracle_enhanced-adapter-7.0.2/lib/active_record/connection_adapters/oracle_enhanced/database_statements.rb:26:in `block in exec_query'

During debuging, the ‘left_query.query.to_sql’ generate this error and the SQL string in the error is:

SELECT /*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.2') */ cols.column_name AS name,
 cols.data_type AS sql_type,
 cols.data_default,
 cols.nullable,
 cols.virtual_column,
 cols.hidden_column,
 cols.data_type_owner AS sql_type_owner,
 DECODE(cols.data_type, 'NUMBER', data_precision, 'FLOAT', data_precision, 'VARCHAR2', DECODE(char_used, 'C', char_length, data_length), 'RAW', DECODE(char_used, 'C', char_length, data_length), 'CHAR', DECODE(char_used, 'C', char_length, data_length), NULL) AS limit,
 DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale,
 comments.comments as column_comment
 FROM all_tab_cols cols,
 all_col_comments comments
 WHERE cols.owner = :owner
 AND cols.table_name = :table_name
 AND cols.hidden_column = 'NO' 
 AND cols.owner = comments.owner 
 AND cols.table_name = comments.table_name 
 AND cols.column_name = comments.column_name
 ORDER BY cols.column_id

and the binds Array is correctly populated.

The log contain

Cmdmary::Etudprix Load (2.4ms)  SELECT "ETUDPRIX".* FROM "ETUDPRIX" WHERE ROWNUM <= :a1 /* loading for inspect */   [["LIMIT", 11]]
  ↳ (eval):5:in `block in print_variable'

repeated 4 or more time.

The strangest thing: With rubymine if I put a breakpoint on the ::Cmdmary::Etudprix.all.select(Arel.star) … line,

  • left_query.query.to_sql fail with the error but
  • left_query.query don’t fail and after that
  • left_query.query.to_sql return the correct sql! and the application work correctly until restart

If the first DB request is a more simpler SQL it also work and the fulll application is working as expected.

** the request use a connection to a shard (implemented wit a around_action block)

If i revert back to

rails (6.1.4.4)
activerecord-oracle_enhanced-adapter (6.1.5)
composite_primary_keys (13.0.3)
ruby-oci8 (2.2.10)

it’s working.

I don’t known how to continue to try to resolve this, so I request some help about this problem.

System configuration

Rails version: rails 7.0.2.3

Oracle enhanced adapter version: 7.0.2

Ruby version: ruby 3.0.3

Oracle Database version: oracle 10.2.0.1.0, instanclient 10.2.0.1 ** and arel-helpers (2.14.0), composite_primary_keys (14.0.4)

About this issue

  • Original URL
  • State: open
  • Created 5 years ago
  • Reactions: 1
  • Comments: 18 (5 by maintainers)

Most upvoted comments

Done. Thank you akostadinov.

A friend has my same versions of the related gems but oracle instant client 21.7. I may try downgrading instant client instead of the gems.

We fixed it using this monkey patch:

module ArelSqlPatch
  def to_sql
    arel # <= This is the workaround, we prebuild arel before using it in unprepared_statement
    super
  end
end

ActiveRecord::Relation.prepend ArelSqlPatch

arel would have been called anyways later on and is cached in @arel so there is no performance loss.

I was surprised to find that there already exists a cache busting method in the schema loading code: select_values_forcing_binds(arel, name, binds).

A fix for this (in our case) was to just add one for select_all as well:

module ActiveRecord
  module ConnectionAdapters
    class OracleEnhancedAdapter
      def column_definitions(table_name)
        (owner, desc_table_name) = @connection.describe(table_name)

        select_all_forcing_binds(<<~SQL.squish, "SCHEMA", [bind_string("owner", owner), bind_string("table_name", desc_table_name)])
          SELECT /*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.2') */ cols.column_name AS name, cols.data_type AS sql_type,
                 cols.data_default, cols.nullable, cols.virtual_column, cols.hidden_column,
                 cols.data_type_owner AS sql_type_owner,
                 DECODE(cols.data_type, 'NUMBER', data_precision,
                                   'FLOAT', data_precision,
                                   'VARCHAR2', DECODE(char_used, 'C', char_length, data_length),
                                   'RAW', DECODE(char_used, 'C', char_length, data_length),
                                   'CHAR', DECODE(char_used, 'C', char_length, data_length),
                                    NULL) AS limit,
                 DECODE(data_type, 'NUMBER', data_scale, NULL) AS scale,
                 comments.comments as column_comment
            FROM all_tab_cols cols, all_col_comments comments
           WHERE cols.owner      = :owner
             AND cols.table_name = :table_name
             AND cols.hidden_column = 'NO'
             AND cols.owner = comments.owner
             AND cols.table_name = comments.table_name
             AND cols.column_name = comments.column_name
           ORDER BY cols.column_id
        SQL
      end

      def select_all_forcing_binds(arel, name, binds)
        deleted_object_id = prepared_statements_disabled_cache.delete(object_id)
        select_all(arel, name, binds)
      ensure
        prepared_statements_disabled_cache.add(deleted_object_id) if deleted_object_id
      end
    end
  end
end