View difference between Paste ID: f15df5519 and
SHOW: | | - or go back to the newest paste.
1-
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;