The Gemara in Masechet Berachot (31) discusses the story told in the Book of Shemuel (chapter 1) of Hanna, who uttered an impassioned prayer for a child in the Mishkan, while the Kohen Gadol (high priest), Eli, observed. Eli thought that Hanna was intoxicated, and scolded her: "Until when will you be drunk? Remove your wine from upon you!" Hanna then explained to Eli that she was not inebriated, and her intense emotion was sincere. The Gemara notes that although Eli was mistaken in thinking that Hanna was intoxicated, his exhortation to Hanna demonstrates that one may not pray in a state of intoxication. The Shulhan Aruch codifies this Halacha in Orah Haim 99:1.
At what point of inebriation does it become forbidden to pray?
The Shulhan Aruch writes (listen to audio recording for precise citation) that if a person drank a Rebi’it (approx. 3 oz.) of wine in a single gulp, then he may not pray until he becomes sober. If he nevertheless prayed while under the intoxicating influence of wine, then his prayer is valid only if he is a condition where he is capable of speaking properly before a prominent person. If he is intoxicated to the point where he cannot speak coherently and respectably, without stammering or slurring his words, then his prayer is not valid, and is considered a "To’eba" ("abomination"). He must therefore repeat the prayer upon regaining sobriety. And if he does not become sober until after the final time for reciting that prayer, then he must recite an extra Amida during the subsequent prayer service. For example, if a person recited Shaharit while in a state of intoxication whereby he could not speak properly before an important person, and he did not repeat the prayer before the final time for Shaharit, he must recite an extra Amida prayer at Minha.
Interestingly enough, the Kaf Ha’haim (Rav Yaakob Haim Sofer, Baghdad-Israel, 1870-1939) cites the Tur (Rav Yaakov Ben Asher, Germany-Spain, 1270-1340) as noting a Biblical allusion to this Halacha. The Torah in Parashat Shemini establishes the prohibition forbidding the Kohanim from performing the Aboda (service in the Mishkan) in a state of intoxication: "Yayin Ve’shechar Al Tesht…Be’bo’achem El Ohel Mo’ed" ("Do not drink wine or intoxicating beverages…when you enter the Tent of Meeting" – Vayikra 10:9). The letters of the words "Ve’shechar Al Tesht" may be read as an acrostic representing the phrase, "V’im Shata Ke’deh Rebi’it Asur Le’hitpalel; Tefilat Shikor To’eba" ("And if one drank the amount of a Rebi’it, it is forbidden to pray; the prayer of a drunkard is an abomination").
It should be noted that these Halachot apply only if a person drank wine or other intoxicating beverages by themselves, and not in the context of a meal. When one drinks during a meal, the wine has less of an intoxicating effect because it is ingested together with food. Nevertheless, one must be responsible and ensure not to drink wine during a meal to the point of inebriation, as this would disqualify him from praying afterward until he regains his sobriety.
Summary: It is forbidden to pray while one is intoxicated; even if one drank only 3 oz. of wine in one shot, he may not pray until he becomes sober. If one is intoxicated to the point where he cannot speak in a coherent, respectable manner, then his prayer is invalid and considered an "abomination."