Experimental: ROWID-based LOB writes for oracle-enhanced (ruby-oci8 only)

oracle_enhanced_rowid_lob_fix.rb

# frozen_string_literal: true

# ╔══════════════════════════════════════════════════════════════════════╗
# ║  WARNING: ruby-oci8 ONLY                                           ║
# ║                                                                     ║
# ║  This patch uses cursor.rowid, a ruby-oci8 API. It is NOT          ║
# ║  compatible with JDBC (JRuby). For a cross-platform fix that       ║
# ║  handles composite primary keys only, see:                         ║
# ║  https://gist.github.com/andynu/508ed5b16f348d474db377ab1e870778   ║
# ║                                                                     ║
# ║  ROWID is stable within a transaction but can change during table   ║
# ║  reorganization (ALTER TABLE MOVE, partition maintenance, etc.).    ║
# ║  Wrap inserts in a transaction to ensure the ROWID remains valid    ║
# ║  between the INSERT and the LOB write callback.                    ║
# ╚══════════════════════════════════════════════════════════════════════╝
#
# Monkey patch: ROWID-based LOB writes for tables without primary keys
#
# Fixes write_lobs to handle:
#   1. Composite primary keys (builds multi-column WHERE clause)
#   2. Tables with no primary key (uses ROWID captured after INSERT)
#
# The stock implementation assumes a single-column primary key. With a
# composite key, attributes[array_key] returns nil and quote_column_name
# stringifies the array. With no PK at all, the WHERE clause is nonsense.
#
# Applies to: activerecord-oracle_enhanced-adapter ~> 7.2 / 8.0
#
# NOTE: exec_insert is copied in full because the ROWID capture must
# happen after exec_update but before cursor.close. If the adapter's
# exec_insert changes upstream, this patch may need updating.
#
# Usage:
#   # config/initializers/oracle_enhanced_rowid_lob_fix.rb
#   require_relative "path/to/oracle_enhanced_rowid_lob_fix"

module OracleEnhancedRowidLobFix
  # Override exec_insert to capture ROWID after INSERT.
  # Copied from DatabaseStatements with ROWID capture added.
  def exec_insert(sql, name = nil, binds = [], pk = nil, sequence_name = nil, returning: nil)
    sql, binds = sql_for_insert(sql, pk, binds, returning)
    type_casted_binds = type_casted_binds(binds)

    log(sql, name, binds, type_casted_binds) do
      cached = false
      cursor = nil
      returning_id_col = returning_id_index = nil
      with_retry do
        if binds.nil? || binds.empty?
          cursor = _connection.prepare(sql)
        else
          unless @statements.key?(sql)
            @statements[sql] = _connection.prepare(sql)
          end

          cursor = @statements[sql]
          cursor.bind_params(type_casted_binds)

          if /:returning_id/.match?(sql)
            returning_id_index = binds.size
            cursor.bind_returning_param(returning_id_index, Integer)
          end

          cached = true
        end

        cursor.exec_update
      end

      rows = []
      if returning_id_index
        returning_id = cursor.get_returning_param(returning_id_index, Integer).to_i
        rows << [returning_id]
      end

      # === ROWID CAPTURE ===
      # Grab ROWID while cursor is still open. Used by write_lobs
      # as a fallback for tables without a primary key.
      if cursor.respond_to?(:rowid)
        @last_insert_rowid = cursor.rowid
      end

      cursor.close unless cached
      build_result(columns: returning_id_col || [], rows: rows)
    end
  end

  # Override write_lobs to handle composite PKs and ROWID fallback.
  def write_lobs(table_name, klass, attributes, columns)
    pk = klass.primary_key

    where_clause = if pk.nil? && @last_insert_rowid
      "ROWID = #{quote(@last_insert_rowid)}"
    elsif pk.nil?
      if columns.any? { |col| attributes[col.name].present? }
        logger&.warn "Cannot write LOB columns for #{table_name} - table has no primary key " \
                     "and ROWID is not available. LOB data may be truncated. " \
                     "Use prepared_statements: true to avoid this."
      end
      return
    elsif pk.is_a?(Array)
      pk.map { |col| "#{quote_column_name(col)} = #{quote(attributes[col])}" }.join(" AND ")
    else
      "#{quote_column_name(pk)} = #{quote(attributes[pk])}"
    end

    columns.each do |col|
      value = attributes[col.name]
      next unless value
      value = klass.attribute_types[col.name].serialize(value)
      next unless value
      uncached do
        sql = "SELECT #{quote_column_name(col.name)} FROM #{quote_table_name(table_name)} " \
              "WHERE #{where_clause} FOR UPDATE"
        unless lob_record = select_one(sql, "Writable Large Object")
          raise ActiveRecord::RecordNotFound, "statement #{sql} returned no rows"
        end
        lob = lob_record[col.name]
        _connection.write_lob(lob, value.to_s, col.type == :binary)
      end
    end

    @last_insert_rowid = nil
  end
end

# Expose cursor.rowid on the OCI connection wrapper
module OracleEnhancedCursorRowid
  def rowid
    @raw_cursor.rowid
  end
end

ActiveSupport.on_load(:active_record) do
  if defined?(ActiveRecord::ConnectionAdapters::OracleEnhanced::OciConnection)
    cursor_class = ActiveRecord::ConnectionAdapters::OracleEnhanced::OciConnection::CursorWrapper
    cursor_class.prepend(OracleEnhancedCursorRowid) unless cursor_class.method_defined?(:rowid)
  end

  ActiveRecord::ConnectionAdapters::OracleEnhanced::DatabaseStatements.prepend(OracleEnhancedRowidLobFix)
end

oracle_enhanced_rowid_lob_writeup.md

Experimental: ROWID-based LOB writes for oracle-enhanced

Background

The activerecord-oracle_enhanced-adapter has a two-phase LOB write mechanism. When prepared_statements is disabled (which happens automatically in Rails 8 development with query logging), Oracle can't bind LOB data inline. Instead:

1. INSERT with empty_clob() / empty_blob() placeholders
2. SELECT the row back FOR UPDATE using the primary key, then write the LOB data via the locator

The stock implementation of step 2 assumes a single-column primary key:

id = quote(attributes[klass.primary_key])
# ...
WHERE #{quote_column_name(klass.primary_key)} = #{id} FOR UPDATE

This breaks for:

• Composite primary keys — primary_key returns an Array, attributes[array] returns nil, quote_column_name(array) produces garbage. Result: StatementInvalid.
• Tables with no primary key — primary_key returns nil, WHERE clause is nonsense. Result: RecordNotFound, LOB data silently lost.

The ROWID Approach

After an INSERT, Oracle's cursor holds the ROWID of the inserted row. ROWID uniquely identifies any row in any table regardless of key structure. By capturing it right after exec_update, we can use it in the subsequent SELECT FOR UPDATE:

SELECT "DESCRIPTION" FROM "MY_TABLE" WHERE ROWID = 'AAAFo1AABAAAMk1AAA' FOR UPDATE

This works because:

• The after_create callback fires immediately after INSERT on the same connection
• ROWID is valid for the lifetime of the row (stable within a transaction)
• No dependency on primary key structure

The monkey patch captures ROWID via cursor.rowid (ruby-oci8 API) in exec_insert, then uses it as a fallback in write_lobs when no primary key is available.

Priority Order

The patched write_lobs tries these strategies in order:

Priority	Condition	WHERE clause
1	Composite PK (pk.is_a?(Array))	col_a = x AND col_b = y
2	Single PK	pk = id (original behavior)
3	No PK + ROWID captured	ROWID = 'xxx'
4	No PK + no ROWID	Log warning, bail

Caveats

This is experimental and has known limitations:

• ruby-oci8 only — cursor.rowid is a ruby-oci8 API. JDBC cursors don't expose ROWID the same way. The patch uses respond_to?(:rowid) to degrade gracefully, but JDBC users with no-PK tables won't benefit.
• ROWID stability — ROWIDs can change during table reorganization (ALTER TABLE MOVE, partition operations, etc.). This is safe within a transaction but not across them. Since the callback fires immediately after INSERT on the same connection, this is fine in practice.
• Instance variable on connection — @last_insert_rowid is stored on the connection adapter instance. In theory, if something else runs an INSERT between exec_insert and the after_create callback, it could overwrite the ROWID. In practice, ActiveRecord's callback chain runs synchronously.

For the composite primary key fix alone (no ROWID, works on both platforms), see: https://gist.github.com/andynu/508ed5b16f348d474db377ab1e870778

Affected Versions

• activerecord-oracle_enhanced-adapter 7.x and 8.x
• Only manifests when prepared_statements: false
• ROWID capture requires ruby-oci8 (CRuby); composite PK fix works everywhere

Usage

Drop into an initializer:

# config/initializers/oracle_enhanced_rowid_lob_fix.rb
require_relative "../../lib/patches/oracle_enhanced_rowid_lob_fix"