So stop your cheap comment, 'Cause we know what we feel...
Prophecies in fantasy games have always been problematic. A basic assumption of RPG is the promise an open-ended freedom of movement and decision to the players, especially concerning their characters. Prophecies seem to circumscribe that - if not eliminate it altogether. If the DM "predicts the future" for a character, it's not much fun for the character's player. If the player "predicts the future" for their character, it can screw up the DM's campaign goals. Not to mention that predicting the future is a tricky business in a game (not to mention real life). It might work in a novel or film, where the future is controlled. But having it work in a game is difficult, at best. For these reasons, my experience in RPGs has been that prophecies seldom play a significant role in any aspect of the game except for campaign dressing. There's also a continuous conflation of prophecy and divination - that is, using "divination magic" to learn something often is equated to being prophetic.
First, let's take a quick look at how prophecies and oracles have appeared in Dragon through the versions.
The first significant attention to the topic appears as Prophet Proofing or How To Counter Foretelling Spells in Dragon (Issue 21 - Dec 1978). Here, prophecy is directly equated to divination. Prophetic spells are enumerated as Clairaudience, Clairvoyance, Wizard Eye, ESP, and X-Ray Vision. And the "prophet" utilizes these spells to, essentially, determine what's on the "other side of the door". Thus fore-armed, the party is able to prepare tactically. The article suggests various methods the mean-spirited Dungeon Master can use to discourage this type of behavior.
This use, approach, and discouragement is repeated twenty-six years later in Smoke and Mirrors - Divinations in D&D in Dragon (Issue 316 - Feb 2004). As with the prior article, here divination is equated to prophecy and the same comments, notes, and suggestions are repeated in a more refined and expanded article. From Dungeons & Dragons (Original Edition) to Dungeons & Dragons (3rd Edition), apparently not much changed.
In Dragonmarks - Fragments of the Prophecy, Dragon (Issue 358 - Aug 2007), a chunk of Eberron detail is provided, including notes on a prophecy with major influence in the setting. Here we find, finally, a significant use of prophecy to drive the campaign narrative. But it functions at a very high, "meta", level to craft the setting, history, and so forth. We're still lacking the sort of oracular proclamation or prophetic vision that might influence characters directly.
So let's take a look at a bunch of scenarios that ostensibly feature prophecy as a substantive component.
A very early (and for the time lengthy) series of modules was called the "Prophecy of Brie". This series originally was published as RPGA3 - 8. RPGA3: The Forgotten King and RPGA4: The Elixir of Life were published as standalone products. RPGA5 - 8 were published as inserts in Polyhedron Magazine: The Riddle of Dolmen Moor: An AD&D adventure for 10 PCs of levels 4-7, The Incants of Ishcabeble: An AD&D adventure for 10 PCs of levels 4-7, Llywelyn's Tomb, and ...And the Gods Will Have Their Way: An AD&D Adventure for 10 PCs of levels 4-7. As you can imagine, the scenarios are very difficult to find, and expensive to own. However, they were edited and reworked and then republished as C4: To Find a King and C5: The Bane of Llywelyn. Actually the reworked products make a little more sense and are a little easier to use, if a little less cool. However, they are all still scenarios designed for convention "competition" play and, thus, pretty much suck. Despite the dominant theme of prophecy, it is little more than window dressing to initiate the action. While it will influence the character party, it won't do so much more than, say, a hobbit selling a treasure map in a bar.
Dungeon (Issue 48 - Jul 1994) provides The Oracle at Sumbar. The Oracle is real, but the adventure is not much more than a treasure hunt.
Dungeon (Issue 55 - Sep 1995) provides Umbra, the "child of prophecy". Prophecy here is used to drive the action and set up events, but is little more than a narrative plot device.
Dungeon (Issue 75 - Jul 1999) provides Non-Prophet Organization. Again, not much meaningful influence here.
There's nothing really wrong with using prophecy or oracular information in this way - in fact, it's by far the most common method employed in FRPGs. The prophecy that guides the campaign, builds the setting, or initiates events. It works, and it works well. It delivers flavor and style to a campaign, and makes the prepared Dungeon Master seem even more prepared. It works well to provide credible motivation for a variety of non-player characters, also. There was a minor but present thread of this style of prophecy that ran throughout the Living Greyhawk organized play seasons.
Let's take a look:
URC2-06: Servant of the Prophet - here, the big bad evil dude is convinced he is the figure of prophecy.
ONW3-01: The Prophet - here, the "prophet" is just a cleric.
CORS4-02: Here There Be Dragons - here, Dragons take action to try to fulfill a prophecy.
KET4-05: Oracle - here, the party is sent to consult an "Oracle" for a sage. Humorously, the oracle - said to be an "ancient inimical being", turns out to be a "specially altered" undead aboleth.
YEO5-04: Prophecies of Ash - here, the action is initiated by a prophetic dream.
SHE7-07: Prophecies - here, prophecies from the past initiate the narrative plot.
It seems that decades of prophecy in D&D has not lead to any suitable methodology for incorporating a significant prophetic strategy into the core games. It works fine as ancient history, fine as the far future, and fine as campaign dressing. It works well for nations, continents, and perhaps even the odd non-player character (especially kings or wannabe kings). But it doesn't work, it seems, for the plain old player character.
I only found one significant attempt to incorporate oracular prophecy into the core rules in the literature. That was an excellent article by Andrew Dewar, presented as The Oracle: When he talks, everybody listens in Dragon (Issue 53 - Sep 1981). A major component of the article is the Oracle class, limited to non-player characters (more about this in a moment). The class is quite intriguing, with good spell-casting ability, limited to divination type spells. This makes obvious sense. The other significant ability of the class is oracular pronouncement. The implementation is unique and intriguing. Instead of having an Oracle specialize in this- or that-type of fortune-telling, they get them all. At every level, they add a tool to their toolbox, starting out with simple dowsing and ending up with the ability to view the past, present, and future. Their oracluar pronouncements are said to be either correct (if they refer to the past or the present), or inevitable (if they refer to the future). I suppose this infallibility is what limits the class to non-player characters. After all, you (apparently) can't have a player character dictating the future of the campaign.
I strongly recommend this article - it pretty much sums up all the possibility, and notes all the pitfalls, of having prophecy.