CODEWITHSUNDEEP
sqloracle-databaseinteger
user2999593
user2999593

Reputation:

Checking if the value inserted is a Varchar or an Integer

I'm using SQL Developer/Oracle and I need to check in a trigger if the the value inserted on a specific field is an integer or just a "string".

I tried isnumeric(), but this function doesn't exist in Oracle. What's the equivalent in Oracle since I can't find it anywhere?

Upvotes: 1

Views: 2818

Answers (2)

Nick Krasnov
Nick Krasnov

Reputation: 27251

From your last comment to the @Ben's answer

'1234' should be considered as a string and fail

it seems like you want to do a data type checking of a literal upon insertion and allow to insert only literals of numeric data types. Oracle does an implicit data type conversion when it makes sense. For example, you have a column of number data type in your table. When you try to insert a character literal '123' to that column, the operation will succeed despite the fact that the literal is of character data type(char, simple character literals are of CHAR data type not varchar2 by default), because Oracle takes a look at the data type of a column, then at data type and elements of the character literal and decides 'Yes, it makes sense to convert that character literal to a number.' and does it. As @Ben said , it probably would be better to let your application do the checking whether a value you are trying to insert into a table is of number or character data type.

Having said that, the probably simplest method to do a data type checking and allow to insert only literals or variables of numeric data types would be creating a package with one overloading function, say isnumber(). First version of the function has a formal parameter of varchar2 data type and its overloaded version has formal parameter of number data type. Depending on a data type of actual parameter Oracle will choose appropriate version of the function:

SQL> create or replace package Util1 as
  2    function isnumber(p_val in varchar2)
  3      return varchar2;
  4    function isnumber(p_val in number)
  5      return number;
  6  end;
  7  /
Package created

SQL> create or replace package body Util1 as
  2    function isnumber(p_val in varchar2)
  3      return varchar2 is
  4    begin
  5      raise_application_error(-20000, 'Not a numeric data type');
  6    end;
  7  
  8    function isnumber(p_val in number)
  9       return number is
 10    begin
 11      return p_val;
 12    end;
 13  end;
 14  /
Package body created

When you call util1.isnumber() function with actual parameter of numeric data type it simply returns it back, and when the function is called with an actual parameter of a character data type exception will be raised.

SQL> create table t1(col number);
Table created

SQL> insert into t1(col) values(util1.isnumber(123));
1 row inserted

SQL> commit;
Commit complete

SQL> insert into t1(col) values(util1.isnumber('123'));

ORA-20000: Not a numeric data type
ORA-06512: at "HR.UTIL1", line 5

SQL> insert into t1(col) values(util1.isnumber('12345f'));

ORA-20000: Not a numeric data type
ORA-06512: at "HR.UTIL1", line 5

Note This approach wont work in a trigger because of implicit data type conversion. In trigger you would have to do the following: 

 :new.col_name := util1.isnumber(:new.col_name)

As col_name is of number data type, Oracle will always call version of isnumber() function with formal parameter of number data type and insert will succeed even if actual value being inserted is (say) '123'.

Upvotes: 3

Ben
Ben

Reputation: 52893

I'm using an if. This means if the value inserted is varhchar I will raise an application error, otherwise I will do nothing.

When you insert a character into a numeric field you'll get an "invalid number" exception raised (ORA-01722). This makes your choice easier; don't test to see if the string you're inserting is a character. Capture the raised exception when a user inserts a character into a numeric field and re-raise as an application error (if you really feel you have to).

There's then no need to test at all.

For example:

create table test ( a number );

begin
   insert into test values ('a');
exception when INVALID_NUMBER then
   raise_application_error(-20000, 'The number wasn''t a number');
end;
/

It's worth noting that you could also test if something's a number in your application code (if it's an application). You wouldn't have to do the round trip to the database then.

Upvotes: 0

Related Questions