I think the game has a lot of the stuff in place already to make this fun.
Basic idea:
An oath is a trade with a god. You promise the good to do X, the god rewards you with Y to help you achieve X, but if you fail the got punished you with Z.
X can be stuff like "kill 10 goblins in a week", "kill 3 rocs in a year", "build 100 statues", "get fort value to [value] within a year".
If you want a stat boost before you fight a roc, you should not be able to swear just to fight this roc, so it would be a free lunch. You'd have to swear some more things, that should be designed to be more than what the player wants to do. Maybe like "kill 3 rocs within a year" is the "minimum oath".
It could potentially create some interesting race-against-the clock situations, with the player trying to fulfill his roc kill quote before the clock runs out and his legs burst into flame.
Also possible to create minor fun stuff. Like you take an oath never to eat fish for some small bonus (maybe a drowning protection) but then forget about it and eat a fish and then kaboom! (That oath would work great for fortress mode as well.)
Y can be stat bonuses, special items, special powers (i.e. an adventurer can turn invisible or can't drown, in fortress mode sleep need is halved, dwarves cannot drown, stuff like that). I think it can be taken from effects already in the game, example immunity to pain.
Z should be taken from stuff already in the game, like effects of poison. So it could be the effect from some snake poison inflicted on you every morning, it can be some of the undead stuff like you get inflicted with rot, or your ears become magma.
In fortress mode the Zs can be the effects from evil biomes (but underground) or that a bunch of dwarves get an individual effect like they get poisoned out of nowhere once a month or it could be that a bunch of bad creatures spawned in random locations of the map or your buried dead become hostile zombies.
An oath is sworn by one of the gods that exist in the world.
So the Y Z should if possible be related to the god's sphere.
If it makes no sense with Y Z in the sphere (i. e. you swear by the god of lust to kill orcs), they could be generic effects instead. I.e you swear by the god of lust to kill goblins, Y is a dodge bonus and Z is snake poison every morning for the rest of your sorry life. It would be way cooler if the Y and Z were sphere bases. So you swear by the god of rot and both the Y and Z will be effects that are rot-related.
Fortress mode - several uses.
Honestly not sure if oaths would be a fun addition to fortress mode. But throwing up some ideas anyway.
Anyway, I think in fortress mode there should be fort wide oaths, where the fort as a community takes an oath binding for all citizens.
(This is similar to the oathbreakers in Lord of the rings who become the army of the dead because they break their common oath.)
Fort wide oaths should give a fort-bonus and the penalty should be fort wide.
The player should decide to generate and take oaths. This would accomplish the same as in adventure mode; to force some extra goals upon you. Maybe you'd like a stat bonus to digging, and the only oath avalable is 100 z-levels. Then you'd be given some extra goal.
You could add game generated oaths, such as the monarch out of nowhere pledges that the fort will dig down 100 z levels or build a glass tower or whatever.
I'm not sure this would be fun though, as it would be disruptive to the game if mega projects were suddenly trust on you. It's something to ponder about if a game would be enjoyable if the player's goals were sometime randomly reset. It might be fun, I don't know other games that does this, so it would be rather unique and give a chaotic feel to it that in the middle of your project of building a moat or whatever you have to scramble to construct a glass tower. On the other hand, the goals of the oath would be set by the game, and probably the game can't check if a glass tower exists in the fort by a given date - so it would be more boring stuff like put 100 statues below 20 z levels and such.
You could also have dwarfs spontaneously making individual oaths. This is akin to moods.
It could range from stuff like "I swear I will never go outside again" to "I swear to protect my spouse to the death" to "I swear I will face an elf in combat." The dwarf would then get a bonus related to the oath, and would get severely penalized for screwing up.
So you might get stuff with a dwarf randomly bursting into flames or getting turned into a random creature.
Example: The game makes the doctor swears by a god of fire, "I will not let any patients unattended". The doctor would get a stat bonus, and burst into flames if he failed.
Example: A dwarf swears he will never be rained upon again. This gives him a bonus regarding focus so he's always focused, but if you forget about it and he gets rained upon anyway, one of his arms fall off.