As I remember it, that was O'Reily, on the Enterprise, and he wasn't drunk, but fantasizing that he was a great Irish tenor. They had flown through a space warp or were infected by a space virus or some such, and the affected crew members were deluded into living their fantasy on board. As I remember, Ensign Sulu had a samurai fantasy. O'Reily locked himself in the communications center and treated the entire crew to several hours of I'll Take You Home Again Kathleen, apparently the only song he knew, and he effectively slaughtered it. I'd like to see that episode again, because years ago I didn't have the interest in the music.