Posts Tagged ‘pl/sql’

Oracle ‘isnumeric’ function

Thursday, November 12th, 2009

Hello,

To me a simple, yet glaring, omission from the Oracle PL/SQL language, which is in many ways excellent, is a function to test to see if a string represents numeric data as can be found in many other languages (e.g is_numeric in PHP, isNumeric in Java and .Net).

A possible implementation is to simply try to assign the string to a number and catch any exceptions.  If something goes wrong then it’s not a number!

  create or replace function isnumeric(p_value in varchar2) return number
  as
    l_ret number;
  begin
    -- Try to cast to a number
    begin
      l_ret := to_number(p_value);
      -- if the line above worked then just reset to 1
      l_ret := 1;
    exception
      when others then
        -- Something, anything, went wrong
        l_ret := 0;
    end;
    -- Return
    return l_ret;
  end isnumeric;

e.g

  select isnumeric('10.1.1') from dual
  select isnumeric('10.1') from dual
  select isnumeric('-10.1') from dual

We hope this helps someone.

Tags: ,
Posted in Oracle | No Comments »

Extracting XML information using PL/SQL

Monday, June 2nd, 2008

Since version 9, Oracle has had support for XML data built into the heart of the database.  Sometimes however, it is not immediately obvious how to get data from an XML string, particularly if, like me, it’s not something you do that often!

Here is a small code fragment which, given an XML ’string’ (xml_char) extracts either some attribute values or node text :

DECLARE
  xml_char VARCHAR2(1024);
  xml      xmltype;
  v_type   VARCHAR2(32);
  v_server VARCHAR2(32);
  v_port   VARCHAR2(32);
BEGIN
  -- create dummy XML document
  xml_char := '<?xml version="1.0"?>' ||
              '<CONNECTION>' ||
              '   <MAPSERVER type="Arcims">' ||
              '      <HOST>uranus</HOST>' ||
              '      <PORT>5300</PORT>' ||
              '   </MAPSERVER>' ||
              '</CONNECTION>';
  -- convert to XML from char type
  xml := xmltype.createxml(xml_char);
  -- extract some elements
  v_type   := xml.EXTRACT('/CONNECTION/MAPSERVER/@type').getstringval();
  v_server := xml.EXTRACT('/CONNECTION/MAPSERVER/HOST/text()').getstringval();
  v_port   := xml.EXTRACT('/CONNECTION/MAPSERVER/PORT/text()').getstringval();
  -- display them
  DBMS_OUTPUT.PUT_LINE('Type   = ' || v_type);
  DBMS_OUTPUT.PUT_LINE('Server = ' || v_server);
  DBMS_OUTPUT.PUT_LINE('Port   = ' || v_port);
END;

Firstly an XML ‘object’ is created from the xml_char XML string, we can then call the ‘EXTRACT’ method to get the relevant attribute or node value.

Tags: , , ,
Posted in Oracle | No Comments »