Hello, I suffer from working with a database with not enough constraints. I have a varchar2(50) field that holds a date. In theory, the 1st 4 characters of this field are the year. If some clown hasn't entered his or her data properly, I end up with a ORA-01722 Invalid number error. Please withhold comments about holding a date in a varchar2.
Anyhoo, I have this function that I found that checks if those characters are actually numbers, but if I limit my output to only valid years, Oracle doesn't seem to care. It checks the whole column for the implicit cast and gives me ORA-01722. I even went so far as to limit the original table in a separate view (see below). Any ideas?
create or replace function ISNUMBER(in_var in varchar2)
v_number := in_var;
v_number := 0; -- Number...
when value_error then
v_number := 1; -- Not a number..
return( v_number );
CREATE OR REPLACE VIEW VALID_DATE_VW AS
SELECT PRIMARY_ID, FORIEGN_ID, CRAPPY_DATE FROM TABLE_A
WHERE ISNUMBER(SUBSTR(CRAPPY_DATE,1,4)) = 0;
CREATE OR REPLACE VIEW A_COUNT_PRE_1900 AS
select count(primary_id) primary_count, foreign_id from table_a
WHERE SUBSTR(CRAPPY_DATE,1,4) < 1900
group by foreign_id;
I am not clear as to what you are attempting to do.
Are you simply trying to pull out bad data? Is the issue, 2 digit year versus 4 digit year? If the issue is simply 2 digit versus 4 digit and remainder of data is the same. Pull out the data where length is should be length - 2.
Without know what data is being stored, simply looking for first digit of 0 or 1 meet your requirement for date validation?