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 :-).
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…