Fix oracle-enhanced LOB writes with composite primary keys

composite_key_lob_monkey_patch.rb

# frozen_string_literal: true

# Monkey patch: Fix LOB writes for tables with composite primary keys
#
# The stock oracle-enhanced adapter's write_lobs method assumes a single
# primary key column. With a composite key, klass.primary_key returns an
# Array, which causes:
#   - attributes[[:col_a, :col_b]]  → nil  (Hash doesn't find Array keys)
#   - quote_column_name([:col_a, :col_b]) → garbage column name
#   - SELECT FOR UPDATE returns no rows → RecordNotFound / data loss
#
# This patch builds a proper multi-column WHERE clause for composite keys.
#
# Applies to: activerecord-oracle_enhanced-adapter ~> 7.2 / 8.0
# Works with: ruby-oci8 and JDBC (no platform-specific changes)
# Context: https://github.com/rsim/oracle-enhanced/issues/XXXX
#
# Usage:
#   # In an initializer (e.g. config/initializers/oracle_enhanced_lob_fix.rb):
#   require_relative "path/to/composite_key_lob_monkey_patch"

module OracleEnhancedCompositeLobFix
  # Writes LOB values from attributes for specified columns.
  # Overrides the stock method to handle composite primary keys.
  def write_lobs(table_name, klass, attributes, columns)
    pk = klass.primary_key

    where_clause = if pk.nil?
      # No primary key at all — can't locate the row for LOB write.
      # This is a pre-existing limitation; log and bail.
      if columns.any? { |col| attributes[col.name].present? }
        logger&.warn "Cannot write LOB columns for #{table_name} — table has no primary key. " \
                     "LOB data may be truncated. Use prepared_statements: true to avoid this."
      end
      return
    elsif pk.is_a?(Array)
      # Composite primary key — build AND'd conditions for each column
      pk.map { |col| "#{quote_column_name(col)} = #{quote(attributes[col])}" }.join(" AND ")
    else
      # Single primary key — original behavior
      "#{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
  end
end

ActiveSupport.on_load(:active_record) do
  ActiveRecord::ConnectionAdapters::OracleEnhanced::DatabaseStatements.prepend(OracleEnhancedCompositeLobFix)
end

oracle_enhanced_composite_lob_writeup.md

Fix: oracle-enhanced LOB writes with composite primary keys

The Problem

The activerecord-oracle_enhanced-adapter gem's write_lobs method assumes every table has a single-column primary key. When prepared_statements is disabled (which happens automatically in Rails 8 development with query logging), Oracle requires LOB data larger than ~4KB to be written in a two-phase process:

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

Step 2 uses the primary key to find the row. On a table with a composite primary key, klass.primary_key returns an Array like ["author_id", "book_id"], and the stock code does this:

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

This produces:

SELECT "DESCRIPTION" FROM "TEST_TABLE"
WHERE "[author_id, book_id]" = NULL FOR UPDATE

• attributes[["author_id", "book_id"]] — Hash lookup with an Array key returns nil
• quote_column_name(["author_id", "book_id"]) — stringifies to a garbage column name
• Result: StatementInvalid or RecordNotFound — LOB data is silently lost

The Fix

The monkey patch checks whether primary_key is an Array and builds a proper multi-column WHERE clause:

SELECT "DESCRIPTION" FROM "TEST_TABLE"
WHERE "AUTHOR_ID" = 1 AND "BOOK_ID" = 100 FOR UPDATE

It also adds a graceful warning for tables with no primary key at all (a pre-existing limitation).

Affected Versions

• activerecord-oracle_enhanced-adapter 7.x and 8.x
• Only manifests when prepared_statements: false (Rails 8 dev default with query logging, or explicit configuration)
• Works with both ruby-oci8 (CRuby) and JDBC (JRuby) — no platform-specific code

Usage

Drop the patch file into your app and require it from an initializer:

# config/initializers/oracle_enhanced_lob_fix.rb
require_relative "../../lib/patches/oracle_enhanced_composite_lob_fix"

Or inline in the initializer — it's ~40 lines of actual code.