Make use of exceptions

While I was browsing the supportforum of PL/SQL Developer by Allround Automations I came across a thread about the special copy function of PL/SQL Developer. In this thread a function was written to determine if a sent in parameter (VARCHAR2) was numeric or not. I took this implementation and created my own depending on the exceptions thrown by Oracle.

The original code is as follows:

   1:  CREATE OR REPLACE FUNCTION is_numeric(parameter_ IN VARCHAR2) RETURN NUMBER IS
   2:   
   3:    char_         VARCHAR2(1);
   4:    char_counter_ INTEGER;
   5:    is_numeric_   NUMBER;
   6:   
   7:  BEGIN
   8:   
   9:    is_numeric_   := 1;
  10:    char_counter_ := 0;
  11:    WHILE (char_counter_ <= length(parameter_)) LOOP
  12:      char_counter_ := char_counter_ + 1;
  13:      char_         := substr(parameter_, char_counter_, 1);
  14:      IF (instr('0123456789', char_, 1) = 0) THEN
  15:        is_numeric_ := 0;
  16:        EXIT;
  17:      END IF;
  18:    END LOOP;
  19:   
  20:    RETURN(is_numeric_);
  21:  END is_numeric;

What this code does is check every character to see if it’s a numeric character. What happens if you send in a negative number? Or if you prefix a positive number with a plus sign (+)? The current code will flag this as NOT numeric while it actually is.

I figured I should use what is there and let Oracle do the heavy lifting. Rely on what the guys in the PL/SQL team have come up with.

My code looks like this:

   1:  CREATE OR REPLACE FUNCTION is_numeric_pba(parameter_in IN VARCHAR2) RETURN NUMBER IS
   2:    l_is_numeric NUMBER;
   3:    l_number     NUMBER;
   4:  BEGIN
   5:    BEGIN
   6:      -- try to convert the parameter to a number
   7:      l_number := to_number(parameter_in);
   8:      -- if it works then it's a number
   9:      l_is_numeric := 1;
  10:    EXCEPTION
  11:      WHEN OTHERS THEN
  12:        -- if it fails then it's not a number
  13:        l_is_numeric := 0;
  14:    END;
  15:    RETURN(l_is_numeric);
  16:  END is_numeric_pba;

This code lets Oracle try to convert the characters into a number. If it succeeds in doing so, it’s a number. If it fails, then an exception will be thrown so it is not a number. This code also works for negative numbers and signed numbers. Heck, it even works for calculations.

It is even faster than the original.

The test code:

   1:  clear screen
   2:  set serveroutput on size 9000000
   3:  set timing on
   4:  declare
   5:    l number;
   6:  begin
   7:    for i in 1 .. 100000 loop
   8:      l := (is_numeric(i));
   9:    end loop;
  10:  end;
  11:  /
  12:  declare
  13:    l number;
  14:  begin
  15:    for i in 1 .. 100000 loop
  16:      l := (is_numeric_pba(i));
  17:    end loop;
  18:  end;
  19:  /
  20:   

Some results:

   1:  Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
   2:  Connected
   3:   
   4:  PL/SQL procedure successfully completed
   5:   
   6:  Executed in 0.375 seconds
   7:   
   8:  PL/SQL procedure successfully completed
   9:   
  10:  Executed in 0.094 seconds

(I had to run it a lot of times to see any difference ;-))

Seems that Oracle internally does a better and faster job than you can with your own code :-).

One thought on “Make use of exceptions

  1. Hi Patrick,

    I am kind of surprised that someone on the plsql developer support forum built something awful like the function you described. I thought that trying to convert a string-to-number using exception handling was lesson number 1 (ah well, lesson 5 🙂 ) in the pl/sql manual…


Leave a Reply

Your email address will not be published.