# ORA-01704 string literal too long

**URL:** <https://www.ruby-forum.com/t/ora-01704-string-literal-too-long/183946>\
**Category:** Rails\
**Created:** [February 22, 2010, 10:58pm UTC](https://www.ruby-forum.com/t/ora-01704-string-literal-too-long/183946 "2010-02-22T22:58:06Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\
**Post date:** [February 22, 2010, 10:58pm UTC](https://www.ruby-forum.com/t/ora-01704-string-literal-too-long/183946/1 "2010-02-22T22:58:06Z")

</div>

Hello all,  
I’m stuck again. I’m in my 5th month of Ruby on Rails and getting  
better but…

I’m uploading a .csv file using Paperclip and FasterCSV, then processing  
the file after the upload is complete.

Small files work fine. I tested a larger file and found that one cell  
had 13,000 chars. needless to say the ORA-01704 was thrown.

When processing the file, I do line by line.

- controller -  
def proc\_csv  
@import = Import.find(params[:id])  
puts @import.csv.path  
lines = parse\_csv\_file(@import.csv.path)  
lines.shift #comment this line out if your CSV file doesn’t contain  
a header row  
if lines.size \> 0  
@import.processed = lines.size  
lines.each do |line|  
case @import.datatype  
when “irb”  
new\_irb(line)  
end  
end  
@import.save  
flash[:notice] = “CSV data processing was successful.”  
redirect\_to :action =\> “show”, :id =\> @import.id  
else  
flash[:error] = “CSV data processing failed.”  
render :action =\> “show”, :id =\> @import.id  
end  
end

private

def parse\_csv\_file(path\_to\_csv)  
lines = []  
#  
require ‘fastercsv’  
FasterCSV.foreach(path\_to\_csv) do |row|  
lines \<\< row  
end  
lines  
end

def new\_irb(line)  
params = Hash.new  
params[:irb] = Hash.new  
params[:irb][“irb\_number”] = line[0]  
params[:irb][“pi\_full\_name”] = line[1]  
params[:irb][“cr\_quest\_split”] = line[2]  
params[:irb][“cr\_and\_ct\_split”] = line[3]  
params[:irb][“old\_master\_list”] = line[4]  
params[:irb][“title”] = line[5]  
params[:irb][“status\_of\_irb”] = line[6]  
params[:irb][“expiration\_date”] = line[7]  
params[:irb][“review\_level”] = line[8]  
params[:irb][“category”] = line[9]

```
irb = Irb.new(params[:irb])
irb.save

```

end

The “title” is where the huge cell is.

I’ve done some research and I know that Oracle’s datatype ‘Clob’ will  
hold the size but only at 4,000 chars. at a time.  
I’m just wondering how to do it?

Thank you for any help with this.

JohnM

---

<div class="post-metadata">

**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\
**Post date:** [February 23, 2010, 1:34am UTC](https://www.ruby-forum.com/t/ora-01704-string-literal-too-long/183946/2 "2010-02-23T01:34:51Z")

</div>

On Mon, Feb 22, 2010 at 3:58 PM, John M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> I’ve done some research and I know that Oracle’s datatype ‘Clob’ will  
> hold the size but only at 4,000 chars. at a time.  
> I’m just wondering how to do it?

I’m guessing you’ll need to change the value into it’s ready-to-store  
binary format. Here’s how I do it in PHP, perhaps you can port the  
code:

function updateCLOBs( $table, $id, $keys, $values )  
{  
$count = count( $keys );

if( $count != count( $values ) )  
die( “key and value counts don’t match” );

for( $x = 0; $x \< $count; $x++ )  
{  
$sql = "  
UPDATE $table  
SET $keys[$x] = EMPTY\_CLOB()  
WHERE id = $id  
RETURNING $keys[$x] INTO :$keys[$x]  
";

```
$query = OCIParse( $GLOBALS['DBH'], $sql );

$lob = OCINewDescriptor( $GLOBALS['DBH'], OCI_D_LOB );

OCIBindByName( $query, ":$keys[$x]", $lob, -1, OCI_B_CLOB );

OCIExecute( $query, OCI_DEFAULT )
  or die( "Unable to execute query\n" );

if( !$lob->save( $values[$x] ) )
{
  OCIRollback( $GLOBALS['DBH'] );
    die("Unable to update lob\n");
}

OCICommit( $GLOBALS['DBH'] );

$lob->free();

OCIFreeStatement( $query );

```

}

return $id;  
}

–  
Greg D.  
[destiney.com](http://destiney.com) | [gregdonald.com](http://gregdonald.com)

---

<div class="post-metadata">

**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\
**Post date:** [February 23, 2010, 5:20pm UTC](https://www.ruby-forum.com/t/ora-01704-string-literal-too-long/183946/3 "2010-02-23T17:20:07Z")

</div>

Thank you for replying.

I don’t quite understand what you’re doing.

What do you mean by…

> I’m guessing you’ll need to change the value into it’s ready-to-store binary \>format."

John

---

<div class="post-metadata">

**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\
**Post date:** [February 23, 2010, 9:43pm UTC](https://www.ruby-forum.com/t/ora-01704-string-literal-too-long/183946/4 "2010-02-23T21:43:20Z")

</div>

John M. wrote:

> I’ve done some research and I know that Oracle’s datatype ‘Clob’ will  
> hold the size but only at 4,000 chars. at a time.  
> I’m just wondering how to do it?

According to what I can find on Oracle’s CLOB data type is that they can  
store up to 4 gigabytes. I would hope that the Ruby Oracle database  
adaptor would take care dealing with the CLOB field. You shouldn’t have  
to worry about that yourself.

Have you actually tired to store the 13 K byte string in a CLOB field?

---

<div class="post-metadata">

**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\
**Post date:** [February 23, 2010, 9:48pm UTC](https://www.ruby-forum.com/t/ora-01704-string-literal-too-long/183946/5 "2010-02-23T21:48:28Z")

</div>

Thanks for the reply.

> I would hope that the Ruby Oracle database  
> adaptor would take care dealing with the CLOB field.

I had the standard Oracle Adapter but decided to install the Oracle  
Enhanced Adapter.

> Have you actually tired to store the 13 K byte string in a CLOB field?

No, next thing to try.

Thanks again.

John

---

<div class="post-metadata">

**Author:** ![jmaenpaa](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/9de053/32.png) [@jmaenpaa](https://www.ruby-forum.com/u/jmaenpaa)\
**Post date:** [February 23, 2010, 10:03pm UTC](https://www.ruby-forum.com/t/ora-01704-string-literal-too-long/183946/6 "2010-02-23T22:03:56Z")

</div>

> > Have you actually tired to store the 13 K byte string in a CLOB field?

I did a simple copy/paste of the 13K data into the CLOB field and it  
worked fine.  
I checked the “show” view and the data is there.

John