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