type binary (blob, clob, nclob in oracle) doesn't get saved

[saslani]

• Database adapter: oracle_enhanced
• Rails 6 stable
• Ruby 2.6.3
• gem "activerecord-oracle_enhanced-adapter", git: "https://github.com/rsim/oracle-enhanced.git", branch: "release60"
• gem "ruby-oci8", "2.2.7"

Migration: any column with type binary (oracle: blob), text (oracle: clob) or ntext (oracle nclob) gets saved as an empty string.

[jkowens]

Hi, could you add some details to this issue report? Info about how you are using this library would be useful. Also I assume you must be using the activerecord-import-oracle_enhanced extension?

[saslani]

I wasn't using "activerecord-import-oracle_enhanced extension"

Here is my Gemfile:

# frozen_string_literal: true

source "https://rubygems.org"
git_source(:github) { |repo| "https://github.com/#{repo}.git" }

ruby "2.6.3"
gem "activeresource", "5.1"
gem "audited", "4.9"
gem "bootsnap", "1.4.4", require: false
gem "jwt", "2.2.1"
gem "puma", "4.0.1"
gem "rack-cors", "1.0.3"
gem "rails", github: "rails/rails", branch: "6-0-stable"
gem "rufus-scheduler"
gem "sentry-raven", "2.11.0"

group :development do
  gem "brakeman", "4.6.1"
  gem "bundle-audit", "0.1.0"
  gem "listen", "3.1.5"
  gem "rubocop-performance", "1.4.1"
end

group :development, :test do
  gem "awesome_print", "1.8.0", require: "ap"
  gem "byebug", "11.0.1", platforms: %i[mri mingw x64_mingw]
  gem "dotenv-rails", "2.7.5"
  gem "pry", "0.12.2"
  gem "pry-byebug", "3.7.0"
end

group :development, :staging, :test do
  gem "activerecord-import"
  gem "database_cleaner", "1.7.0"
  gem "factory_bot_rails", "5.0.2"
  gem "faker", "2.1.0"
end

group :development, :qa, :production do
  gem "activerecord-oracle_enhanced-adapter", git: "https://github.com/rsim/oracle-enhanced.git", branch: "release60"
  gem "ruby-oci8", "2.2.7"
end

group :test do
  gem "rspec-rails", "3.8.2"
  gem "shoulda-matchers", "4.1.1"
  gem "webmock", "3.6.2"
end

Migration

class CreateEvent < ActiveRecord::Migration[5.2]
  def change
    create_table :event do |t|
      t.string :name
      t.string :status
      t.binary :tags
      t.timestamps
    end
  end
end

In RDS Oracle, that translates to

name VARCHAR2(255 CHAR)
status VARCHAR2(255 CHAR)
tags BLOB

database.yml

oracle_default: &oracle_default
  adapter: oracle_enhanced
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000
  encoding: unicode
  username: <%= ENV['DATABASE_USER'] %>
  password: <%= ENV['DATABASE_PASSWORD'] %>
  database: <%= ENV['DATABASE'] %>
  schema: <%= ENV['DATABASE_SCHEMA'] %>

development:
  <<: *oracle_default

Event creation - scenario 1: create individually and it works fine

events.each do |event|
  create({name: "event_name", status: "active", tag: "the blog"})
end

Event creation - scenario 2: bulk import using active-record import. all fields except BLOB, CLOB, and NCLOB (type text and binary) gets saved. BLOBs and CLOBs get saved as empty string

details = []
events.each do |event|
  details << {name: "event_name", status: "active", tag: "the blog"}
end

Event.import(details)

[jkowens]

ActiveRecord-Import only supports postgresql, mysql, and sqlite "out of the box". Try adding the activerecord-import-oracle_enhanced extension. See: https://github.com/keeguon/activerecord-import-oracle_enhanced

[jkowens]

If you can capture the SQL that activerecord-import is generating, that would be helpful too. Can you share that?

[toao]

@jkowens I just stumbled upon this issue, it's still there and activerecord-import-oracle_enhanced unfortunately does not fix it.

Example SQL looks like this:

INSERT INTO "FOO" ("BAR","BAZ","CREATED_AT","UPDATED_AT") WITH names ("BAR","BAZ","CREATED_AT","UPDATED_AT") 
AS (SELECT 'foobar1337',empty_clob(),'2022-01-21 14:22:39.719127','2022-01-21 14:22:39.719127' FROM DUAL
union all SELECT 'baz42',empty_clob(),'2022-01-21 14:22:39.719127','2022-01-21 14:22:39.719127' FROM DUAL)

The BAZ column should contain the clob data, but instead of the actual content empty_clob() is passed instead.