Checking the Input value is Number or not in oracle-CodePudding

I have below procedure having varchar as input parameter

Create or Replace procedure p1(brand_code varchar2)
a varchar2(22);
Begin
SELECT  DESCRIPTION into a from UDA_VALUES
WHERE CODE =brand_code;
end;

In the above procedure I am facing invalid number error because the input parameter brand_code contains the value 'ALL' but the data type of code in the UDA_VALUES table is Number data type. So how can we check the brand_code contains the numeric value?. So that I can validate and pass the brand_code in the select statement.Is there any method apart from regexp to validate the variable contains the numeric value?

CodePudding user response：

A regular expression is one way to solve it:

Create or Replace procedure p1(brand_code varchar2) AS
  a varchar2(22);
Begin
  SELECT DESCRIPTION into a from UDA_VALUES
    WHERE CODE = brand_code AND
          REGEXP_LIKE(DESCRIPTION, '[A-Za-z] ');
end p1;

You can get around using a regular expression with:

Create or Replace procedure p2(brand_code varchar2) AS
  a varchar2(22);
  n number;
Begin
  SELECT DESCRIPTION into a from UDA_VALUES
    WHERE CODE = brand_code;

  BEGIN
    n := TO_NUMBER(a);
  EXCEPTION
    WHEN OTHERS THEN
      n := NULL;
  END;
end p2;

CodePudding user response：

You can also just trap the error with an exception hander.

Create or Replace procedure p1(brand_code varchar2)
as
  a varchar2(22);
Begin
  begin
   SELECT  DESCRIPTION into a from UDA_VALUES
    WHERE CODE =brand_code;
  exception
    when others then
      null;
  end;

  --continue without a value for `a` if the SQL above failed
end;

CodePudding user response：

From 12c, TO_NUMBER supports DEFAULT ... ON CONVERSION ERROR:

WHERE code = TO_NUMBER(brand_code DEFAULT NULL ON CONVERSION ERROR);