They are useful for those times when you absolutely want the subject to know you are taking a picture, unlike a telephoto where you can shoot anonymously. ;-)
Seriously, fisheyes have a number of real applications:
They allow coverage of large fields of view, with a distortion that has a fairly simple mathematical model. They were invented for research imagery of the whole sky for measurement of cloud coverage (see wikipedia). Since the distortions are easily modeled, it is possible to make a quantitative measurement of the sky coverage from analysis of single exposures.
If used with a tripod or similar stable mounting point and rotated about the effective entrance pupil, one can capture a full sphere panorama with only two exposures. Fewer exposures allows the panorama to be captured faster, and more than one exposure makes it possible for the photographer and their gear to not be in the finished picture.
They allow documenting a confined space with very limited incursion, often just barely leaning in the window is sufficient.
They allow coverage of large, sweeping landscapes without resorting to panorama adapters and stitching.
They don't really require much aim, so they can be used for stealthy or automated captures where it is more important to catch some kind of image of the subject at all, than it is to have an artistic image.
I carried a borrowed NIKKOR 10.5mm f/2.8G ED on a recent tour of Australian tourist spots. It stayed on the D90 for much more of the trip than I expected as I found opportunities to take pictures that would not have been possible with a more "normal" lens. I'd strongly suggest borrowing or renting on and trying it out on a casual outing. You might be surprised at the results.
Edit: Let me add a couple of notes about nodal points, the entrance pupil, and panorama stitching.
One definition of the entrance pupil is the following, from Wikipedia:
The geometric location of the entrance
pupil is the vertex of the camera's
angle of view and consequently its
center of perspective, perspective
point, view point, projection centre
or no-parallax point. This point is
important in panoramic photography,
because the camera must be rotated
around it in order to avoid parallax
errors in the final, stitched
panorama.
According to Wikipedia in the section on the Cardinal Points,
the nodal points are locations along the optical axis that have the
property that a light ray intersecting the front nodal point (and
entering the lens) will leave the lens as if it originated at the
rear nodal point.
The section goes on to identify some common misconceptions:
The nodal points are widely
misunderstood in photography, where it
is commonly asserted that the light
rays "intersect" at "the nodal point",
that the iris diaphragm of the lens is
located there, and that this is the
correct pivot point for panoramic
photography, so as to avoid parallax
error. These claims generally arise
from confusion about the optics of
camera lenses, as well as confusion
between the nodal points and the other
cardinal points of the system.
In short, multiple images destined for stitching into a panorama should all be taken with the entrance pupil in a fixed location, and only changing the direction the optical axis points.
Unfortunately, one of the interesting properties of a practical fish-eye lens is that the entrance pupil is at a changing distance along the optical axis as you vary the angle of the incident ray makes with the axis. You can see this effect by holding the lens with its rear element facing a bright wall (making the entrance pupil more visible at all) and moving your head around the front. As you move from on-axis to off-axis you can clearly see the pupil move from deep inside the lens to far in front of the rim of the front element. In order for rays to enter the lens from more than 90 degrees off axis, the pupil has to be visible to those rays. The surprise is that the pupil doesn't stay in that position as you move to smaller angles.
This effect does not happen with a normal rectilinear lens design. In rectilinear prime lenses, the entrance pupil is at a fixed location. You can identify that location by carefully constructing tests for parallax error. One of the compromises in a zoom lens design is that the position of the entrance pupil moves as the focal length changes. This is one of the reasons that the f-number of the lens varies with the zoom.
This makes it difficult to pick a proper point of rotation for a fisheye lens used for panorama photography. One answer is to just live with the natural field of view. After all, 180 degrees or more is pretty darn panoramic compared to a normal lens. Another answer is to use the approximate average position of the entrance pupil in the region where the individual images overlap. If you minimize the overlap, then you can calibrate the result fairly well. With a full-circle fisheye with a 190 degree or better field of view, you can cover the whole sphere with just two pictures, but you need to rotate the camera around a point that is very close to the front face of the front element for least parallax error in the overlapping band.