There can be a scenario where the datatype of the column is VARCHAR or CHAR but values that are suppose to be in there are numeric values in that column.

In the case when that happens and you would want to detect the row with that issue.

SQL> desc test;
Name Null? Type
—————————————– ——– ————–
X VARCHAR2(10)

SQL> select * from test;

X
———-
1
2
N/A
2A
10

Using one of the methods below you can identify the row(s).
Method 1: Works in 10g and up
SQL> select rowid, x
from test
WHERE x is not null
and NOT regexp_like(x, '^[0-9]+$');

ROWID X
—————— ———-
AAE3GaAAEAAAQOYAAC N/A
AAE3GaAAEAAAQOYAAD 2A

Method 2: Works in 10g and up
select rowid, x
from test
WHERE x is not null
and NOT regexp_like(x, '^[[:digit:]]+$');

ROWID X
—————— ———-
AAE3GaAAEAAAQOYAAC N/A
AAE3GaAAEAAAQOYAAD 2A

Method 3: Works in all version
SQL> set serveroutput on
SQL> declare
cursor l_cursor is select rowid, x from test;
l_num number;
begin
for l_rec in l_cursor
loop
begin
l_num := to_number(l_rec.x);
exception
when others then
dbms_output.put_line('Row id: ' || l_rec.rowid || ' ' || l_rec.x);
end;
end loop;
end;

Row id: AAE3GaAAEAAAQOYAAC N/A
Row id: AAE3GaAAEAAAQOYAAD 2A

• Datatypes
• General DBA