From: Neil Kohl <nakohl@...>
Date: 2006-01-18T05:14:06+09:00
Subject: Re: Populating 'long' data column with Ruby OCI8 (trying again, need a solution badly)

On 1/17/06, Gennady Bystritsky <Gennady.Bystritsky@quest.com> wrote:

> Is there any way to insert a big chunk of data (say, 100K) into
> a column of type long with Ruby OCI8 or by any other Ruby means?

You need to do it in chunks. See documentation for OCI8::BLOB#write().
Here's an example that works for me loading data from a file. You'll
need to adapt to load from a variable.

require 'oci8'

conn = OCI8.new(user, passwd, sid)

# test.txt is a file that's > 100K
name = "test.txt"

# create the row with an empty blob
cursor = conn.parse("INSERT INTO nkrb_test (name, data) VALUES(:name,
EMPTY_BLOB())")
cursor.exec(name)

# now load blob column with file contents
conn.exec("SELECT name, data FROM nkrb_test") do |name, data|
  chunk_size = data.chunk_size
  File.open(name, 'r') do |f|
    until f.eof?
      data.write(f.read(chunk_size))
    end
    data.size = f.pos
  end
end
conn.commit

Schema for nkrb_test is:

create table nkrb_test (
	id    INT,
	name  VARCHAR(255),
	data  BLOB,
	CONSTRAINT nkrb_test_pk PRIMARY KEY (id)
);

> UPDATE: It is not possible to read (with OCI8) columns of type long if
> they contain large data chunks (100K). Reported error is:
> `fetch': ORA-01406: fetched column value was truncated

Try setting OCI8::BLOB#truncate() to longer than the longest data you
expect to see in a row, or use OCI8::BLOB#read() to read row data in
chunks -- again see docs for example.

If you haven't figured it out yet already, LOBs are a real pain to deal with.

--
Neil Kohl
nakohl@gmail.com