Darker Than Black has an elderly woman who mainly serves as the focal point of the surveillance spectres for Astronomics. She's silent and comatose for the first half of the city, but once sunspot activity starts picking up and the circumstances that led to the Heaven's Gate incident start occurring again, she starts mumbling vague statements about how she can "hear" the stars and that a great disaster's going to befall the Contractors.
The old man from Paranoia Agent. After he dies, he passes the role on to Maniwa. The series ends with Maniwa finishing the calculations the old man started and pulling back in shock, though we don't learn why.
Assumedly, it has to do with the Vicious Cycle he describes post-credits.
To some degree, Ran from Texhnolyze, who can see one of the many possibilities of the immediate future.
She's never exactly mad though, although her powers apparently include the ability to Drive People Mad From The Revelation. On the contrary, she's disturbingly deadpan, putting on her fox mask whenever she's in danger of actually showing any emotion.
Frodo is one of these in the Bagenders prequel episode set in Troy.
Azael in the Ice Age fic "The Foreteller", who not only sees Manny, Sid and Diego's futures, but also events in the whole "Child of Time" series.
In George Weasley And The Computational Error, George finds it convenient to claim that he's one instead of admitting that he is from the future. For bonus points, he says his becoming a Mad Oracle was a product of the Ministry trying and failing to make an artificial seer.
CrossGen's The First has Orium, whose Meaningful Name is a portmanteau of "oracle" and "delirium." He's a creepy old god who was given the gift of true sight by Altwaal after the latter was disappointed with his vision as a leader.
Dr. Manhattan in Watchmen, the explanation being that his powers (including the ability to see the future) have made him so out of touch with humanity he has forgotten how to act 'normal'. The future-sight is a major part of this, as he sees several times at once and can get confused between them.
In Captain Britain, the precognitive Cobweb ends up like this when the Fury arrives in the 616 universe and Mad Jim Jaspers starts taking over the world.
Shouldn't be here... pattern broken... there was a Crooked Man and he... white wine turning red... white and red, like blood and bone, like chessmen... the board's askew... the gamer's hands are scorched and blackened... all strategies are shredded in the random wind... nothing is certain now...
Dajjal of Supergod can see all futures and people think of him as crazy but he sees sanity as a social norm and he's beyond society.
Delirium in The Sandman is occasionally shown to have insights into the future even Destiny doesn't have. Since she's the Anthropomorphic Personification of insanity, it can be assumed she's an oracle because she's mad, rather than the other way round.
The Thirteenth Warrior. The protagonists go to consult an old woman who supposedly knows how to defeat the 'demons' who are attacking them. A local girl mentions that she's quite mad, whereupon one of the Vikings replies sarcastically: "The perfect advisor."
6 in the movie Nine, but 1 refuses to believe him and just keeps him in a corner with a curtain over it, where he spend all his time drawing what he sees in his head.
The deleted scenes also show once he finally saw what was in his head, he got a new vision, and apparently heard/saw in his head the monster made out of 2's body. "Huh..2? 2..is...coming?"
12 Monkeys. Such oracles throughout history are implied to be time travelers who have gone insane.
Donnie Darko. The ability to see the future takes a heavy toll on Donnie's already unbalanced emotional and mental state.
Pirates Of The Caribbean. In some ways, Tia Dalma is like this. While not really mad, she takes pleasure in speaking in riddles and making people wonder what she really meant.
Mad Medium variation from the crazy homeless guy in Always, who can hear the voice of Richard Dreyfuss 's ghost and repeat what Dreyfuss is saying. The message gets a little lost in the man's general rambling, making him a less-than-ideal communication device — Dreyfuss' attempt to discourage a young hotshot pilot from pursuing his widow comes across as just the opposite.
Mitsuo Hori, the "Super Psychic" in Noroi: The Curse. His house and his clothing are covered with tinfoil, he mutters, fidgets terribly, is obsessed with "Ectoplasmic Worms" and is prone to get violent when he receives psychic impressions/information. However, he provides very useful information to the investigation.
In Suspect Zero, long term use of the remote viewing abilities O'Ryan's former FBI unit is said to have possessed, combined with the lack of an "off" switch for the abilities themselves, is shown to lead to severe mental breakdowns. Many among the unit eventually were driven to suicide, or suffered psychotic breaks.
The novel Final Destination: Dead Reckoning, a spin-off of the film series. Throughout the book an unnamed vagrant, who apparently experienced the same vision of a nightclub collapsing as the Final Girl Jess, appears, usually right before Death kills someone, to offer cryptic, borderline non-sensical advice, all the while throwing in random references to mice and the moon.
One of the Young Wizards books features an intelligent Magical Computer which does this, speaking in triads. (Its predictions turn out to be accurate, though, once the meanings of cryptic names like "The Hesper" are sorted out.)
The Dresden Files kind of tweaks this. Harry says the reason Oracles all talk in riddles and goofy parables and weirdnesses is that they while they see snatches of possible futures, they may not know exactly what events lead to them from the present. Similarly, the tendency towards really vague prophecies is a way of avoiding paradoxes.
In addition, a number of "oracles" in the past (the Oracle of Delphi is specifically mentioned) who were also mad were actually that time's current incarnation of the Archive, a repository of all human knowledge, who were driven mad by said knowledge and yet were able to make accurate predictions of the future through simply analyzing what they possessed and drawing accurate conclusions from it.
Ophelia from Hamlet is often portrayed like this in the mad scenes. Many researchers believe that her madness gives her an ability to sense everything (Unfortunately, she's unable to express her thoughts properly, which makes her a Cassandra-like character).
The prophet from the Mrin river in the Belgariad. (Particularly notable as when he wasn't speaking prophecy, he could only speak in animal noises.)
All the prophets in the Belgariad. It's later related that after the Mrin prophet's predictions had started coming true, everyone rushed out to write down the ravings of every lunatic they could find. Not all of them were actually seers.
The guiding consciousness of all creation points out that it's not the ability to prophecy that drove the prophets mad. Rather those people were already mad, and it's just a lot easier to take over the mind of a madman and make them deliver your message that it is to do the same to a perfectly rational person.
Even for those prophets that weren't actually all that crazy, the fact that prophecy works by one half of the guiding consciousness of all creation speaking through you (and on their schedule) means that they'd seem crazy — one minute you could act perfectly normal, and then something could set you off into a rant about such strangeness as Childs of Light and the like.
The Clayr of the Old Kingdom trilogy seem to be a clan built of Mad Oracles. When their power to See is concentrated into a set number of people, they can receive clear visions of the future or present — assuming there is a future to See. However, in most circumstances, their Sight is divided among the hundreds of Clayr, granting each of its members with random fragments of possible futures. This also gives the Clayr a rather incoherent sense of time and causality (which might explain the number of one-night stands they have.) The trope apparently grows more pronounced with age, to the point that the oldest Clayr - who can apparently live to be a hundred and fifty years old - have to retire to special "Dreaming Rooms" when they become too disjointed in time.
In Perdido Street Station and Iron Council by China Miéville, there's the Weaver, a giant multi-dimensional spider who can see the strands of past and future and always speaks in never-ending streams of free verse. This isn't that bad for the Weaver, since it was never human to begin with, but the human characters in the book find its advice disconcerting and incomprehensible. Plus, it can kill you with a wave of its pedipalp if it doesn't like you (or just thinks the weave of the universe will be prettier that way).
Forgotten Realms, The Erevis Cale Trilogy. Erevis and companions visit Sephris Dwendon, the chosen of the God of Knowledge, seeking information on the MacGuffin. Initially Sephris is only a little cracked, yet after being raised from the dead, not because he wanted to be, but because of a sense of duty Sephris becomes more than a little crazy. Bitter, cyncial, carving mathematical formulae into his flesh.
The Sibyl at Orm in A Gathering of Gargoyles turns out to be mad Doona from the house in which Aeriel grew up as a slave. Subverted in that Doona is actually not the Sibyl. She killed her and took her place.
The Mad Arab Abdul Alhazred, writer of the Necronomicon. Technically not an oracle, as he probably couldn't see into the future (although the contents of the Necronomicon are too vague to say for sure), but other than that he fits this trope very well. Strange visions, cryptic texts, being completely off his rocker etc.
Raymond Feist's The Riftwar Cycle contains an oracle which is mad because it shares a body with a god like being granting her its powers.
In the Circle of Magic books, the character Zhegorz is introduced as a madman, apparently a schizophrenic. It eventually turns out that he's actually able to scry on the wind, both sights and sounds, a very rare ability, which is why everyone assumed he was hallucinating... so unexplained visions + insistence from all sides that he must be mad + commitment to a Bedlam House = all the symptoms of paranoid schizophrenia, only based in reality.
Tsinga from David Clement-Davies' The Sight is a pretty darn good example of this.
The Fool in King Lear is sometimes played this way, depending on the production.
In Eric van Lustbuder's Science Fantasy series The Pearl Saga, this is mentioned as happening to all oracles, which is why the prophecies were written by dragons rather than oracles. So when Giyan is forced by cirumstances to unlock her latent oracular powers, it signals the beginning of her descent into madness as she grows more and more tormented by fragments of possible futures.
Mrs. Tachyon from the Terry Pratchett novel Johnny and the Bomb. Her dementia isn't due to SEEING through time so much as TRAVELING through it—- and constantly losing track of whether she's speaking to someone from the past, present, or future....
Similarly, Old Mother Dismass in Discworld: perfectly coherent, but having a conversation in a different timezone than everyone else. Described as having "a detached retina in her second sight".
Patchface in A Song of Ice and Fire: while he's generally humored by those around him, treated as just an insane fool, his short ditties are not only prophetic, but aptly disturbing at that.
Paul Atreides/Muad'Dib/the Kwizatch Haderach ... and later Leto II, his son ... are widely regarded as this. The reader sees enough of their inner thoughts to know they're not exactly mad per se, but it does seem to be a huge burden to sift through all the might-bes and work out which of the infinitely many possible paths leads to the desired outcome, and since Paul and Leto actually care about this, it makes them do things that sometimes seem pretty random to other people. Like disappearing from the Padishah Throne and becoming a desert vagabond, or arranging for your own death ... ish ... sort of thing.
In Alfred Bester's story The Push of a Finger, the Stabilization Bureau's Prog building doesn't contain a human oracle, but a mechanical one; it can't go mad, but it does illustrate how a human one could: one of the techs working it explains it as a sort of probabilistic machine that does millions of predictions and kind of "averages" them into a likely future, and if you were human and happened to get the one with the hyperintelligent lobster people, then that would pretty quickly either drive you actually mad or make people think you were.
Dalek Caan (from the Doctor Who episode "The Stolen Earth"; see the Quotes page for this trope) is actually a mild subversion. He's crazy and oracular, but his craziness by Dalek standards makes him act fairly sensibly.
He saw all of space and time at once. He also saw what the Daleks truly were and that caused a Heel Realization in him. So he didn't tell anyone that the Doctor would stop them to save all of reality.
Then there's the Visionary from "The End of Time".
And now Idris, the Crazy Awesome "bitey mad lady." She's got good reason to what's going to happen, because as the living soul of the TARDIS, she's experiencing the whole timeline at once.
A notable subversion in the Doctor Who New Adventures. The Pythias were the seers who ruled Gallifrey before Rassilon came to power. The last Pythia goes mad because she's losing her oracular abilities, and can no longer see the future clearly.
Invoked in "The Fires of Pompeii". Donna threatens to warn the people of Pompeii about the oncoming volcano eruption, if the Doctor won't save them all. The Doctor says that no one will believe her, and instead think she's a "mad old soothsayer". Later, two oracles (an older man and a young woman) make a number of correct predictions about the Doctor and Donna, but they say it in such a way that the woman's family can't make heads or tails of it. And one of the predictions ("Daughter of London, you have something on your back") makes no sense to any of them until a later episode.
The Hybrids in the reimagined Battlestar Galactica, who only say things like "Mists of dreams drip along the nascent echo and love no more" and "Intruders swarm like flame, like the whirlwind; Hopes soaring to slaughter all their best against our hulls."
Drusilla is a cross between this and Waif Prophet. Unlike many such prophets, her madness can make her, not just other people, unable to act on them properly. In "Redefinition" she sees a vision of Angel setting her and and Darla on fire, but doesn't really understand it and takes in only how pretty the fire is. When it actually happens she's a lot less happy about it.
Dyson Frost: Most oracles are, you know? They see the future and the knowledge... ends up destroying them.
Frankie Howerd used a mad soothsayer in Up Pompeii (Senna the Soothsayer) and a similar character, a mad beggar (Derti Dhoti) in Whoops Baghdad.
Pythia, more commonly known as the Oracle of Apollo at Delphi. The chamber where she gave her predictions was supposedly filled with vapors that rose from the ground. Since she spent so much time in the chamber, it drove her into a frenzied state. This is the reason why most of her predictions were so cryptic, she was loopy from the fumes. Some sources even state she needed an interpreter to translate. Thus making this yada yada...
The Real Life Oracle of Delphi was much more mundane. The Pythia was a middle aged local woman (young virgins having proven prone to kidnapping); she didn't rave or speak in tongues and those cool 'delphic' prophecies seem to be entirely fictional. The usual questions handled by the real Oracle concerned matters religion and ritual.
The fact that Delphi was, partly because of the Oracle, a major hub for gossip, meant that often the Pythia and the clergy were just well informed.
Cassandra is like this in some versions, either perceived or actual, due to never being believed. In her most recent portrayal, in Eric Shanower's Age of Bronze, she was very much this. Age of Bronze has a tendency to do in the wizard, so her origin story there is deeply ambiguous.
In Exalted, Abyssal Exalted can take a background known as Whispers. This allows them to directly consult their dead-but-not-gone Eldritch Abomination masters. Seeing as their minds are essentially human, characters with high Whispers are a little, um, odd.
In Hunter The Reckoning, members of the Hermit creed all have a direct line to the Powers That Be, giving them oracular insight at the cost of overloading their psyches, which forces them to withdraw from human contact (hence the name).
All members of the Malkavian Clan, from Vampire The Masquerade, are this. They all go insane as part of the Embrace (and that doesn't count the tendency to Embrace those who are already insane), but they become attuned to the "Malkavian Madness Network" (a sort-of Hive Mind that runs through the clan's Antediluvian) and thus know things that no one else does. In the PC version of the game Bloodlines, this is shown through, amongst other things, Malkavian specific dialog options that frequently reveal hidden information about people the moment you start talking to them. It's outright stated that a Malkavian character has absolutely no idea what any of the things he/she sees about people actually mean and can't help but utter them aloud.
Another Mad Oracle in Bloodlines is Rosa, one of the Thin-Bloods hiding on the beach near the Santa Monica Pier. She's more coherent than any given Malkavian, but does dip into incoherence due to unwanted glimpses into the future: nonetheless, for twenty dollars, she'll give you some cryptic riddles that seem like nonsense until you actually start encountering what they refer to in the game.
There's also a Goblin Contract (a Contract with some nasty side effects) in Changeling The Lost that serves as both ends of the trope. You can uncover anything you want to know about anything you've encountered... but you gain a derangement for the duration of the next day. And you only get the mild derangements if you roll high; if you roll low, you can look forward to 24 hours of schizophrenia. And once that wears off, you forget what you learned. Hope that you're lucid enough to write it down.
Also from Changeling is the College of Worms, an Entitlement that believes in the value of portents and omens. Unfortunately, for every three serious scholars of fate, there's one loon who thinks the College is the perfect place to express themselves.
Precognition is a fairly well-known power of psykers in Warhammer 40000, but carries with it The Dark Side. Aside from the Eldar, The Dark Side seems to win more often than not with would-be prophets.
The Contact Other Plane divination spell has a chance of causing severe (if temporary) mental lapses.
Kairos Fatereaver, the oracle of Tzeentch in the Chaos Daemons armies of Warhammer and Warhammer 40000 is this trope. Tzeentch is able to see into the future himself, but even he doesn't know which possible future will come to be. So he threw his vizier Kairos into the point where all timelines intersect, giving him the ability to see everything that has happened and will ever happen. The downside is that he came back unnaturally aged (daemons are immortal and normally don't age), with an extra head and completely off his rocker. To make things worse, whatever one of his heads says, the other contradicts, and there is no way of knowing which one is telling the truth at any given time.
City of Villains has Diviner Maros and Mender Lazarus, who can see through all of time and often confuse cause and effect and talk to you about adventures you haven't even taken yet. Maros even uses his gift to lampshade the way that other contacts often send you out to figure out where the next mission is on your own, as seen here.
"The Freakshow in the cult are going to try to kill you now, but since you don't yet know where to go to take the fight to them, they have the advantage. However, we can edit out all of the tedious searching for hideouts and interrogation. I will tell you where to go."
The Hand of Repose in the Exmortis games serves this purpose when allowed to speak; normally, he acts as a living gateway for the Exmortis demons to return to Earth through, but his position as an anthropomorphic wormhole has allowed him to see a little way into the future - resulting in the Prophecy of the Hand booklet given to you in the second game. And in a particularly interesting twist, the Hand is none other than the PC of the first game.
Runescape's Postbag from the Hedge pages sometimes have the Chaos Elemental give cryptic hints of future content.
Portal gives us 'The Unredeemed Turret'. Turrets in the game are sweet, sapient little robots designed to riddle everything moving with as many bullets as possible. Any that are different are placed on the 'redemption line' to die a horrible flaming death. At one point in the game you're able to rescue one who thanks you, and makes some rather strange statements, such as "get mad", "the answer is beneath us", "her name is Caroline", "Prometheus was banished by the gods for giving knowledge to man. He was sent to the bowels of the Earth...pecked at by birds", and finally, "don't make lemonade." It's all Foreshadowing. The turret's words referencing that beneath them are about Old Aperture, where the company was founded. Cave Johnson was the founder, and as expected (given Aperture Science) was quite insane, so much so that when he learned he was dying and recalled an aphorism about making lemonade from life's lemons he went on a bizarre rant about turning the lemons into bombs to blow up life's house. His assistant was Caroline, who was turned into GLaDOS. And GLaDOS does indeed get cast into the earth to be pecked at by birds.
In Mass Effect 1 on Eden Prime there is an apparently insane scientist who only gives terrified, cryptic statements and claims to be the Only Sane Man. When you learn of the Reapers, you'll realize exactly what he was talking about.
"No-Bark Noonan" of Novac appears to be a crazy conspiracy theorist, and he is, but his information holds surprising and important insights somewhere amidst all the nuttery.
Subject 16 is unstable at the best of times — and stark raving mad at the worst — but his ramblings are always far more relevant than they first appear. He does know what he's talking about. The problem, naturally, lies in figuring out what he's talking about.
As seen here, A Modest Destiny has Morris, who went insane when, after a Heroic BSOD, he woke up among numerous corpses and assumed he had killed them. He later gained prophetic powers when Black Bart, masquerading as a priest, picked an official-sounding passage from the bible - and christened Morris as the new pope.
"Dodie", the hobo oracle from Sea Of Insanity, is this trope in spades. His mind is described as "an empty shell, filled with prophesy". This does not make The Sibyl, the friendly neighbourhood seer, feel better about her chosen career.
Mac from the <3-Verse can see the future when he's not stoned, but he's stoned almost all the time. I wonder why...
Circe, in the Whateley Universe. Yes, that Circe. She's a couple thousand years old, and she's now a teacher at Whateley Academy. And when she predicts things, she tends to ramble weirdly. Okay, sometimes she rambles weirdly when she's not predicting stuff...
Subject Five of Unlikely Eden speaks in an oddly reversed poetic manner. Every one of her predictions is reputed to be inevitable. It's just that no one (including her) can be sure what they mean.
No Real Life Examples, Please!, but it is assumed that if anyone actually had precognitive abilities (most likely some sort of deja vu rather than any active ability), the complications of such ability (You'd have the ability to see the past, present, and future all at once; at the very least, this would skew your perception of time, and probably result in short term memory loss. This is not even considering the consequence of trying to change destiny and wondering whether it did any good ) would drive them completely mad.