SQL gets error when running a select query

Question

I ran this query:

Select * from users where user_id=20;

and got this error:

ORA-00997: illegal use of LONG datatype
00997. 00000 - "illegal use of LONG datatype"
*Cause: 
*Action:
Error at Line: 6 Column: 37

I want to know why this happened and the correct query for this.

Answer 1

You have created your table with user_id defined as a long data type. Unlike C/Java/etc., long is not a numeric type in Oracle:

LONG columns store variable-length character strings containing up to 2 gigabytes -1, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings.

You are getting the error when Oracle tries to implicitly convert the string value to a number to compare with the number 20. While that would work with a varchar2 column, the long data type has a lot of restrictions - and shouldn't be used even for long strings, which should be CLOBs.

If your column holds numeric values then it should be declared as number, and for an ID column probably as number(38) to make it an integer.