Multi tool use

I want to export data from oracle database to csv, and I am putting a number filter on a varchar column. It thows ORA:01722 error. Please suggest

Select count(*) from table where loc between 300 to 400.


loc

Edit from comment:

loc contains values less than 300, more than 400, and alphanumeric like 'GT' , '3KT1'

number/integer

varchar

varchar2

varchar2

'40'

'3000'

2 Answers
2

loc is a varchar column.
[From comment] The Loc column has char type value also like GJ, 3KT1

loc

LOC contains values which are not convertible to numbers. This matters because your WHERE clause predicates are defined as numbers, so Oracle applies an implicit to_number(loc) to the query. This is why using proper data types is best practice: it doesn't help you now but please learn the lesson, and use NUMBER columns for numeric data.

LOC

to_number(loc)

In the meantime you have several options to deal with your shonky data model.

If you're lucky enough to be using Oracle 12c R2 you can use the new VALIDATE_CONVERSION() function to exclude values of loc which can't be cast to numbers. Find out more

VALIDATE_CONVERSION()

loc

If you're using an earlier version of Oracle you can build your own function:

create or replace function is_number
(p_str in varchar2) return number
is
n number;
rv number;
begin
begin
n := to_number(p_str);
rv := 1;
exception
when invalid_number then
rv := 0;
end;
return rv;
end;


The weakest option would be casting the predicates to strings. where loc between '300' to '400' would include '3000', '4' and various other values you probably don't want.

where loc between '300' to '400'

'3000'

'4'

Here is a LiveSQL demo (free Oracle Technet account required, alas).

Your current query is trying to compare a varchar to a number. So it tries to convert the varchar to a number on the fly. This is called implicit conversion.

You should make it compare a varchar to a varchar.

Use single quotes so that you are comparing to varchars, not numbers

Select count(*) from table where loc between '300' to '400'


Then go and read about implicit conversion

Based on the update to your question, this column is a legitimate varchar and should not be converted to a numeric data type.

However you do need to work out whether you are incorrectly storing different types of data in the same column

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.