Ethnobots who have ate the fruit reported no effects.
It was reportedly added to mild corn beer, that may be where its perception as intoxicating came from. Although there may be a more likely reason for its inclusion...
Among the Opuntia and Cylindropuntia species the flowers as well as the fruits are often used as hangover cures or preventatives. Adding opuntia family fruits and flowers to corn beer might just be an attempt to wake up the next morning without feeling like you've been dragged face down through the desert sand

Has anyone trialed Opuntias for hangovers?
My O. engelmanii really looks medicinal to me, but I dont drink...