Verifing the data type 1

[carlavieira]

Hi,

I'd like to know how can I verify if the variable contains only the data type that I'm expecting for. In SQLSERVER we have the IS_NUMERIC, IS_CHAR... functions. Is there something similar in ORACLE? How can I use it?

Thanks.

 

[lewisp]

Write your own function like so:

FUNCTION Is_Number(p_str IN VARCHAR2)
RETURN BOOLEAN IS
l_test NUMBER;
BEGIN
l_test := To_Number(p_str);
Return TRUE;
EXCEPTION
WHEN OTHERS
THEN
Return FALSE;
END;

Then call it in other PL/SQL blocks or in a select:

SELECT x FROM y WHERE Is_Number(z);

 

[carlavieira]

Thanks lewisp.

Carla