Newbie: Invalid number dealing with Identity columns in Oracle

Hi all,

I wonder how can I solve in RoR the problem of accessing a simple table
because the URL

http://localhost:3000/customers/show/2.0

is raising an Oracle error OCIError: ORA-01722: invalid number.

The reason of this error is because the SELECT statement generated by
ActiveRecord tries to invoke the id column as a VARCHAR :

"... FROM customers WHERE customers.id = '2.0' "

How this can be solved?

Thank you in advance for your help.

Gregorio

It looks like you are trying to match an integer (INT) with a text
string (VARCHAR) and that is what Oracle is complaning about. You
should drop the quotes in the select statement.

/Remko.

gpatino wrote:

Hi Remko,

But the problem is that I am using the Rails scaffold, so I don't have
control of the ActiveRecord statements, and I am pretty sure that my
id column is a number (and my table, columns are named as the RoR
documentation suggests).

This is my first experience with RoR and Oracle, and I would like to
know if someone, trying to implement a basic CRUD application using an
Oracle Database, a simple table and a sequence, have solved.

When did the params[:id] before a float ???
Shouldn't that be;
http://localhost:3000/cusomters/show/2
not
http://localhost:3000/customers/show/2.0

Hi Bruce,

You’re right, It’s supposed to be

http://localhost:3000/cusomters/show/2

But, I am just trying to implement my first RoR application against an Oracle DB.

So, I don’t know why the Rail scaffold is generating my application implementing my Id as a VARCHAR, it should be a NUMBER. My question is how to solve the problem within my generation process ( I mean the only things I have made are: installing the oci8 gem, generate my app and then use the rails scripting to get my customers CRUD…)

Thanks again for your help…

Are you saying that then you ran your migration code, rails created
the id column with a type of varchar ?

Hi all,

It was solved. It was and NLS issue, but it could happen to you if you
are not located in the US.

We have to be sure that the way that RoR is generating the numbers,
dates and so, are compliant with the NLS settings of the target
database.

Thank you all for your time and help.