locating row with invalid number from table with few million rows
anthosFeb 12 2009 — edited Feb 13 2009We need to query the a table or a view of same to obtain a id of type VARCHAR with bind value being NUMBER. Any attempts in this regard I try seem to result ORA-01722: invalid number. Once case is below here
SQL> select TEXT_ID FROM ATT_TABLE where to_number(TEXT_ID) = 9411956;
select TEXT_ID FROM ATT_TABLE where to_number(TEXT_ID) = 9411956
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P010
ORA-01722: invalid number
1. TEXT_ID column is meant to have numeric values in VARCHAR type. It is possible that some record / row is not complying. How can I locate the non-complying records ?
2. What would be a VIEW definition that would return only records with valid numeric data in TEXT_ID column.
Edited by: bonjonbovi on Feb 13, 2009 3:58 PM