 The opera that Picard is listening to is Berlioz "Les Troyens". The  song is "Hylas Song" from the beginning of Act V. Hylas is a  homesick young sailor being rocked to sleep by the sea as he dreams  of the homeland he will never see again.