SHARE
TWEET

Giorgio Arata

a guest Jan 9th, 2008 1,548 Never
  1. CREATE OR REPLACE FUNCTION is_numeric(number_str VARCHAR2) RETURN BOOLEAN IS
  2.  x_is_numeric PLS_INTEGER := 0;
  3. BEGIN
  4.  SELECT XMLELEMENT ("ROWSET",
  5.  XMLAGG (
  6.  XMLELEMENT("ROW", xmlattributes(ROWNUM AS "num"),
  7.  XMLELEMENT ("STRING_VAL", REPLACE(number_str, ',', '.'))
  8.  ))
  9.  ).transform(XMLTYPE.createXML(
  10.      '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">' ||
  11.      '<xsl:output method="text"/>' ||
  12.      '<xsl:template match="ROW">' ||
  13.      '<xsl:variable name="str"><xsl:value-of select="STRING_VAL"/></xsl:variable>' ||
  14.      '<xsl:variable name="num">' ||
  15.      ' <xsl:if test="number($str)=number($str)">' ||
  16.      '  <xsl:value-of select="''X''"/>' ||
  17.      ' </xsl:if>' ||
  18.      '</xsl:variable>' ||
  19.      '<xsl:value-of select="string-length($num)"/>' ||
  20.      '</xsl:template>' ||
  21.      '</xsl:stylesheet>')).getStringVal() AS RES
  22.  INTO x_is_numeric
  23.  FROM dual;
  24.  RETURN x_is_numeric > 0;
  25. END is_numeric;
RAW Paste Data
Top