There will be a need for pilots in the future however as technology improves the value for experienced pilots will continue to decline. In addition as UAV technology advances who knows what the future will bring in regards to civilian aviation. Whatever it is it is hard to imagine that it will be good to the profession.
With regards to career possibilities, it is possible that some very good and unique opportunities may present themselves because of advances in technology. Many people build and fly RC aircraft as a hobby. In the future that could be a very lucrative field.