The ophicleide is actually a rather pleasant-sounding instrument with an interesting history.
Following the Battle of Waterloo, John Distin (Father of the famous Distin Family) was in the victory march through Paris, playing the Keyed Bugle in the Band of the Grenadier Guards. Grand Duke Constantine of Russia was very impresssed by this, and commissioned Jean Hilaire Aste (Halary, the instrument maker who is later credited with the invention of the Cornet) to copy the British instrument. Halary produced a whole family of keyed brass instruments, including alto, bass and contrabass versions shaped like Bassoons. He called these "Ophicleides", Greek for "Keyed Serpents", since they had a similar role to the old wooden serpent (the bass cornett). The early valved Bass horn also had a Bassoon like coiling. The famous painting of the Besses'th'Barn Band (1860) shows that the Ophicleide was present in the early days of the brass band. Ophicleide playing was not without its hazards, for in the 1860 Crystal Palace Band Competition, Staleybridge Band's chances of winning were ruined in the final when the ophicleide player got the keys of his instrument tangled in his waistcoat pocket.
If you want to know more, a good place to start is Raph T Dudgeon's excellent chapter "Keyed Brass" in that rather patchy book"The Cambridge Companion to Brass Instruments".
Dudgeon mentions the modern "Ophicleide Revival", and lists several modern players. As I posted recently, I saw one in the Orchestra in the performance of Berlioz's Damnation of Faust recently broadcast from the BBC Promenade Concerts by the Australian ABC.