This would satisfy your sample data, but this would have to be adjusted to accommodate additional suffixes:

Code:
SQL> select name
  2     , regexp_substr(name, '[^ ]+') first
  3     , case when lower(replace(sf, '.')) in ('iii','jr','sr') and sp = 3
  4        then trim(regexp_substr(name, ' [^ ]+ '))
  5        when lower(replace(sf, '.')) not in ('iii','jr','sr') and sp = 2
  6        then trim(regexp_substr(name, ' [^ ]+ '))
  7       end middle
  8     , case when lower(replace(sf, '.')) not in ('iii','jr','sr') and sp is not null
  9        then regexp_substr(name, '[^ ]+$')
 10        when lower(replace(sf, '.')) in ('iii','jr','sr') and sp = 2
 11        then trim(regexp_substr(name, ' [^ ]+ '))
 12        when sp = 3
 13        then regexp_substr(name, '[^ ]+', 1, 3)
 14       end last
 15     , case when lower(replace(sf, '.')) in ('iii','jr','sr')
 16             then regexp_substr(name, '[^ ]+$')
 17       end suffix
 18  from (
 19     select replace(name, ',') name
 20             , regexp_substr(name, '[^ ]+$') sf
 21             , length(regexp_replace(name, '[^ ]')) sp
 22     from names)
 23  ;

NAME                 FIRST           MIDDLE          LAST            SUFFIX
-------------------- --------------- --------------- --------------- ------
NIKIRA               NIKIRA
Nancy J. Schroeder   Nancy           J.              Schroeder
Hammontree Sr.       Hammontree                                      Sr.
Alan BROWN           Alan                            BROWN
Steve Sarvanidis III Steve                           Sarvanidis      III
W MCMILLIAN          W                               MCMILLIAN
Robert F. Seen Jr    Robert          F.              Seen            Jr

7 rows selected.
Nevertheless, PL/SQL would probably be a better alternative.