Full Name search query
You have two options I can think of:


Index for a specific set of user searches

or
Full Text Search


For option 1, you need to convert your search string "Smit*, Bob" into something you are indexing on, eg: where upper(last_name) like 'SMIT%' and upper(first_name) like 'BOB%' with function based indexes on upper(last_name) and perhaps upper(first_name)