Underground reserves of petroleum being exploited by dwarves to make fantastic flaming moats of doom strikes me as a bit... steampunky.
The vegetable oil suggestion does not, particularly when you consider most vegetable oils come from oil seeds, which Armok knows we have enough lying around by the time our fortress matures. I would rather like to see dwarves gathering up pigtail-seed and grinding it down into fuel for lanterns (when lighting becomes a requirement in the depths), oil-cauldron traps built over a hole in the floor (lever or plate activated), flaming arrows and furniture polishing. Of course this requires something of a 'fire arc' (including firefighting!).
True petroleum wouldn't be bad as a limited resource, like a magma pool, if you encountered it inside the underground layers. Dwarves exploring a watery cavern might notice one pool has a film of oil on it (causing mass hilarity fun when a torchbearing dwarf takes a drink), then dig around to find an absolute lake of the stuff. It could then be hauled or pumped like anything else. However I would caution against discussing 'flood the world' hillarity fun, as I doubt Toady would want that sort of thing cropping up again. I see the need for flammable oil or an alchemy and cooking ingredient, not refining operations and desiel-fired water boilers.