Re: Diagnostic Query to avoid ORA-01722 Error

tsu_at_landacorp.com (Tom Urbanowicz) writes:

> I have a utility that selects the maximum value from a VARCHAR2
> column. Obviously, if there is a non-numeric value in the column I
> encounter the expected an ORA-01722 error.
>
> Is there a "select count(*)" query I can use to first identify if
> non-numeric values exist in the column? I could then issue this
> diagnostic query before issuing my query for the maximum value? If the
> column has non-numeric values in it, I can skip selecting the maximum
> value from the column.

You could create a plsql function which attempts to format the varchar2 field as a number inside a block with an exception handler. If you get thrown into the exception, return false (or whatever) and if you don't, return true. Your max "utility could then use this function to first check the value is numberic and only include it if it is, or you could return the number if the formatting (to_number()) works and return a null or zero if it doesn't.

-- 
Tim Cross					E-Mail: tcross_at_pobox.une.edu.au