Newbie: Invalid number dealing with Identity columns in Oracle

rubyonrails-talk
1

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

2

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:

3

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.

4

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

5

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…

6

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

7

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.