drop table t23ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
create table t23 (id number generated always as identity, loc varchar2(10))Table created.
insert into t23 (loc) values ('300')1 row(s) inserted.
insert into t23 (loc) values ('3000')1 row(s) inserted.
insert into t23 (loc) values ('4')1 row(s) inserted.
insert into t23 (loc) values ('320')1 row(s) inserted.
insert into t23 (loc) values ('NO WAY')1 row(s) inserted.
insert into t23 (loc) values ('3NO WAY')1 row(s) inserted.
insert into t23 (loc) values ('200')1 row(s) inserted.
insert into t23 (loc) values ('500')1 row(s) inserted.
insert into t23 (loc) values ('400')1 row(s) inserted.
insert into t23 (loc) values ('401')1 row(s) inserted.
select * from t23
where loc between '300' and '400' | ID | LOC |
|---|---|
| 1 | 300 |
| 2 | 3000 |
| 3 | 4 |
| 4 | 320 |
| 6 | 3NO WAY |
| 9 | 400 |
using validate_conversion() function
select * from t23
where validate_conversion(loc as number) = 1 | ID | LOC |
|---|---|
| 1 | 300 |
| 2 | 3000 |
| 3 | 4 |
| 4 | 320 |
| 7 | 200 |
| 8 | 500 |
| 9 | 400 |
| 10 | 401 |
with cte as (
select /*+ materialize */ * from t23
where validate_conversion(loc as number) = 1
)
select * from cte
where to_number(loc) between 300 and 400 | ID | LOC |
|---|---|
| 1 | 300 |
| 4 | 320 |
| 9 | 400 |
Hand-rolled equivalent of validate_conversion()
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 others then
rv := 0;
end;
return rv;
end;
/with cte as (
select /*+ materialize */ * from t23
where is_number(loc) = 1
)
select * from cte
where to_number(loc) between 300 and 400