{"id":544,"date":"2011-10-14T12:17:56","date_gmt":"2011-10-14T12:17:56","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=544"},"modified":"2011-10-14T12:17:56","modified_gmt":"2011-10-14T12:17:56","slug":"","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=544","title":{"rendered":"Make use of exceptions"},"content":{"rendered":"

While I was browsing the supportforum<\/a> of PL\/SQL Developer<\/a> by Allround Automations<\/a> I came across a thread<\/a> 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<\/a>.<\/p>\n

\n

<\/p>\n<\/div>\n

The original code is as follows:<\/p>\n

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