Sat. Sep 18th, 2021

Requirement: I want to validate if the user-provided value is in Numeric format.

Solution: For the solution, of the above requirement let us create an Oracle Function is_number. The complete code for the is_number is as below:

CREATE OR REPLACE FUNCTION is_number (
     p_string IN VARCHAR2
 ) RETURN VARCHAR2 IS
     v_new_num NUMBER;
 BEGIN
     v_new_num := to_number(p_string);
     RETURN 'Provided value is numeric';
 EXCEPTION
     WHEN no_data_found THEN
         RETURN 'Provided value is non-numeric';
     WHEN OTHERS THEN
         RETURN 'Provided value is non-numeric';
 END;

Now, let us validate the result of the above function.

For that let us first pass the value as or “101.1“, or “101”, and check the result.

SELECT is_number('101.1') result FROM dual;

SELECT is_number('101') result FROM dual;

And the result of one of the above SQL Query is as below:

Now, let us first pass the value as “101SusantoTech”, and check the result.

SELECT is_number('101SusantoTech') result FROM dual;

And the result of the above SQL Query is as below:

Hence, the solution to our requirement.

If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.

Thanks & Regards,
Susanto Paul

 491 total views,  1 views today

By Susanto Paul

Susanto is an Oracle ACE, a qualified MCA, MBA, and a highly-skilled Senior Oracle Specialist. He is an enthusiastic Blogger and YouTuber who helps learners to solve their complex problems more efficiently. He has 9+ years of experience in multiple technologies like AWS, Oracle ADF, Oracle APEX, Oracle JET, Oracle VBCS, Oracle IDCS, Oracle PL/SQL, Oracle Integration Cloud, Java, JDBC, Servlets, JSP, Spring, Hibernate, HTML5, CSS3, JavaScript, TypeScript, NodesJS, Angular, MySQL, Oracle WebLogic Server, JUnit, JMeter, Selenium Web Driver, etc. He is a certified: Oracle Certified Professional Java SE 6 Programmer, Oracle ADF 11g Certified Implementation Specialist, Oracle Cloud Platform Application Integration 2020 Certified Specialist, Oracle Cloud Infrastructure Foundations 2020 Certified Associate, and Oracle Cloud Infrastructure Developer 2020 Certified Associate

Leave a Reply

Your email address will not be published. Required fields are marked *