Giorgio Arata

CREATE OR REPLACE FUNCTION is_numeric(number_str VARCHAR2) RETURN BOOLEAN IS
 x_is_numeric PLS_INTEGER := 0;
BEGIN
 SELECT XMLELEMENT ("ROWSET",
 XMLAGG (
 XMLELEMENT("ROW", xmlattributes(ROWNUM AS "num"),
 XMLELEMENT ("STRING_VAL", REPLACE(number_str, ',', '.'))
 ))
 ).transform(XMLTYPE.createXML(
     '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">' ||
     '<xsl:output method="text"/>' ||
     '<xsl:template match="ROW">' ||
     '<xsl:variable name="str"><xsl:value-of select="STRING_VAL"/></xsl:variable>' ||
     '<xsl:variable name="num">' ||
     ' <xsl:if test="number($str)=number($str)">' ||
     '  <xsl:value-of select="''X''"/>' ||
     ' </xsl:if>' ||
     '</xsl:variable>' ||
     '<xsl:value-of select="string-length($num)"/>' ||
     '</xsl:template>' ||
     '</xsl:stylesheet>')).getStringVal() AS RES
 INTO x_is_numeric
 FROM dual;
 RETURN x_is_numeric > 0;
END is_numeric;