CAST(expr AS data-type) converts expr to a value of the indicated data type.
CAST(MULTISET (subquery) AS type-name) transforms the result set of the subquery to a collection type.
CAST(expr AS data-type DEFAULT val ON CONVERSION ERROR) evaluates to val if expr cannot be converted to data-type.
Compare the default … on conversion error clause with the validate_conversion function.
The indicated precision of the number (here: 4.1) is not respected when casting:
select
cast('42.1' as number(4,1) default -1 on conversion error) x,
cast('foo' as number(4,1) default -1 on conversion error) y,
cast('12345.678' as number(4,1) default -1 on conversion error) z
from
dual;
--
-- X Y Z
-- ---------- ---------- ----------
-- 42.1 -1 12345.678
See also
The DEFAULT val ON CONVERSION ERROR clause can also be applied in the more specific conversion functions