DECLARE
  v_test_nbr NUMBER;
  v_check_exists NUMBER;
BEGIN
  BEGIN
    v_test_nbr := to_number(:P6_TEXT_FIELD);
  EXCEPTION
  WHEN OTHERS THEN
    -- or catch 1722 (invalid number) and 6502 (char to number conversion error)
    v_test_nbr := NULL;
  END;

  IF v_test_nbr IS NOT NULL
  THEN
  -- if v_test_nbr is not null then the field should be numerically valid
  -- if it isn't then this code would be skipped and this validation
  -- will not throw an error.
  -- However, the previous validation will still fail when text is entered, 
  -- so this shouldn't matter.
    BEGIN
      SELECT 1
      INTO v_check_exists
      FROM my_table
      WHERE column_name = :P6_TEXT_FIELD;
    EXCEPTION 
    WHEN no_data_found THEN
      v_check_exists := 0;
    END;

    IF v_check_exists = 1 
    THEN
      RETURN 'A record with this key already exists';      
    END IF;
  END IF;

  RETURN NULL;
END;