ORA-01704 string literal too long

rubyonrails-talk
#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 = []

#2

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;
}

#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 Mcleod 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