I have a function that checks if a phone NUMBER is valid (is_valid_phonenumber), which is working well.
I like to use this function and wrap it with the code to create a valid phone number and return it.
Something like this. I know the psuedo code is incorrect. I was hoping someone can show me how to make this work.
While(1)
{
SELECT DISTINCT FLOOR(DBMS_RANDOM.VALUE(1e9, 1e10)) AS phone_number
FROM DUAL
If is_valid_phonenumber = 'Y'
RETURN PHONE_NUMBER
/* All digits
** Length = 10
** First and fourth characters can't be 0 or 1
*/
CREATE OR REPLACE FUNCTION is_valid_phonenumber( p_str IN VARCHAR2 )
RETURN VARCHAR2
IS
BEGIN
IF REGEXP_LIKE(p_str, '^[2-9][0-9]{2}[2-9][0-9]{6}$') THEN
RETURN 'Y';
ELSE
RETURN 'N';
END IF;
END;
/
}