ORA-01704 string literal too long

Rails
1

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

2

On Mon, Feb 22, 2010 at 3:58 PM, John M. [email protected]
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 | gregdonald.com

3

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

4

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?

5

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

6

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