From: "Berger, Daniel" <Daniel.Berger@...>
Date: 2005-06-07T23:33:32+09:00
Subject: Re: DBI Oracle error

> -----Original Message-----
> From: Graham [mailto:gandalfmeister@gmail.com] 
> Sent: Tuesday, June 07, 2005 8:25 AM
> To: ruby-talk ML
> Subject: DBI Oracle error
> 
> 
> I've written a small upload utility to take a 10k+ line log 
> file and upload it to a database. It works fine (on the 
> whole) except that I get
> 
> 
> Error code: 1722
> Error message: ORA-01722: invalid number
> 
> every so often. The data rows around the offending line are 
> 01/06/05,00:43:39,0,6.67,30.3,-99.9,-99.9,-99.9,
> 01/06/05,00:43:49,0,6.67,30.3,-99.9,-99.9,-99.9, <- this line 
> fails 01/06/05,00:43:59,0,6.67,30.3,-99.9,-99.9,-99.9,
> (i.e. virtually identical data)
> 
> I'm using a prepared statement and my only thoughts are that 
> there is an issue with the driver somehow. e.g. after a 
> certain number of inserts or memory usage the driver fails.
> 
> Any ideas how I debug this, as inserting "broken" rows 
> manually via SQL*Plus succeeds every time (so it isn't the 
> data). Anyone have problems with the DBI functionality?
> 
> require 'dbi'
> dbh = DBI.connect('DBI:Oracle:tpdev', 'username', 
> 'password'); sth = dbh.prepare("INSERT INTO EFFLUENT_LOG 
> (datetime, suspect, flow, temp, ph) VALUES(to_date(?, 
> 'DD/MM/YY HH24:MI:SS'), ?, ?, ?, ?)"); .... some loop stuff.. 
> sth.execute((date + " " + time), val, flow.to_f, temp.to_f, 
> ph.to_f) ... more loop stuff
> 
> Not rocket science.. but I'm confused. Is it a driver issue? Graham

My (possibly wrong) initial guess would be a precision error of some
sort in conjunction with Ruby's .to_f.  Double check the the column
types (presumably a NUMBER) and see what the precision is set to.  The
column precision *is* set, right?  You may need to tinker a bit.
Stepping through the debugger and manually inspecting the float values
might be in order as well.

HTH.

Dan