"I've adopted a system for clerical magic that involves a die roll. The target is based on the level of prayer or miracle you're trying to call forth; modifiers include class level, a bonus from Wisdom, and one or two others; but the big thing is that using "too many" spells makes it less likely that your next prayer will succeed."
I have a some issues with the idea, though I understand it is trying to impress additional "game" into the system and that can often have good consequences. I don't want to dwell on these issues, just speak them quickly and move onto a finer point.
First of all, it follows the basic route that got 3e into trouble. With recent research into scripts, I've realized that even if they work, they are soon very repetitive in structure. It isn't so much that the character has to roll a die to succeed the spell, its that the die has to be rolled every time ... which a lot of times, becoming too many times and finally ceasing to add anything to the game. The repetition of a script eventually produces a feeling of "meh" no matter what the roll is for.
Secondly, we're not making mages roll on account of faith, or fighters to see if they can swing at targets on account of faith. The only reason to add this particular requirement for a cleric from a game stance is because the cleric's power needs a reduction; and I don't see that in the cleric. The cleric is no an overbalanced character class. So why add a car-jack to the momentum here?
Finally, I'm not a big fan of fail/succeed (+/-) die rolls, no matter what they're based on. Combat rolls to hit work as a +/- frame because there are typically five or six rolls in a given round, and anywhere up to a hundred rolls scattered through a 10-round combat. The +/- works because who fails and when, against whom, creates a random sequence of events, where no one completely fails because hit points soak damage long enough to let the character fail this round yet succeed the next. The individual +/- rolls are largely inconsequential (except for the very few that aren't). But a single roll to see if a spell fails or succeeds? Too consequential to be ignored or compensated for.
Okay, getting that out of the way.
Let me talk for a moment about the post on faith. Suppose the orc in the example was a player character, and had chosen to take the dwarf's hand. How would I decide if that broke the cleric character's contract with his god or not?
We may suppose I'd decide one of two options: 1) that taking the dwarf's hand was the right decision, so the cleric's spell attempt succeeded; 2) that taking the dwarf's hand was wrong, that the character should have let go and perhaps trusted his god to save him; thus, the spell attempt fails.
Believing that either of these options is relevant is to misunderstand, in the extreme, how a cleric is judged. It does not matter which the player character decides. It matters why the player decided that option.
I'll try to explain. First, we'll rule that no matter what the orc physically does, the orc's god will remain steadfast in the orc's corner and the orc will remain a cleric able to cast spells. The larger point is this.
Suppose the player says, "I will let go, knowing with absolute faith that my god will save me." That is incredibly presumptive. Suddenly, the player has decided that the orc's god is a personal servant, with nothing better to do than run around catching orc clerics as they plummet to earth. Horseshit. Orc clerics die all the time. Perhaps this is the time for this orc cleric to die. Perhaps that's the plan. Presuming the plan, with the assumption that the orc cleric is going to get what he wants, that's the sin. Pride. Selfish pride. That orc cleric lets go, that orc is going to hit the ground and die. I would never, ever, ever, have the god intervene to catch such a selfish prideful cleric.
However ... if the orc cleric's people found the body and raised it, that orc would still be able to cast spells. Falling 500 feet and hitting the ground is lesson enough. Probably the orc will question the reliance on god's hands being there to catch him.
Now suppose the player says, "I will take the dwarf's hand, because I don't want to die. Wow. There's that pride again. Total dismissal of the dilemma. The only thing that matters to this cleric isn't what's right or wrong; the cleric is ready to snap up the first opportunity to live, whatever it takes. Still, it would matter how totally dismissive the player was, and how obviously willing the cleric was to jump to the most self-serving conclusion ~ and perhaps go on and on about it. Excessively. If excessive enough, yeah. That cleric is losing his spellcasting ability. At least for a little while, probably until a hurtful sacrifice was made. Like tossing his favorite +2 mace into the sea.
Of course the spell works.
What matters is not what the cleric does, but the manner in which the cleric does it, how it affects the cleric, how the cleric reconciles it with his or her perspective, what amends are made to the god after the fact and so on. The less the cleric gives a crap about all that stuff? The less that cleric deserves to be a cleric.
There is more than faith in a religion. There is duty too, and charity, and humility. No one gets a free ride on faith alone.