Isn't there already an initiative in Bethesda to get mods into console versions of Skyrim? It worked with UT3, I can't imagine why an eminently moddable game like Skyrim couldn't manage the same thing. Naturally they'll be issues, but that's why you have an "unmoddable" portion of the game (the menus prior to loading the game), where you can safely activate and deactivate the mods. Like in UT3.
Yeah it should work fine as long as they mean what they say in wanting to get it working. I play Oblivion on PC but I don't mod a lot. I like Midas Magic and OOO, and the community patch is good. I also use a darkness enhancing mod (Xepheras?), so darkness is properly dark. I find a lot of mods sacrifice quality so much that it's another jarring aspect to the immersion, and I just don't like that unpolished amateur finish in my games. I think very highly of OOO though, it's really an excellent mod.
I'll be playing a couple of one hundred plus hour vanilla runs happily on PS3, but I do hope we see mod conversion by January 2012. It just isn't infeasibly difficult any longer, the engine is very adaptable, the new one that is. It has been developed with this kind of flexibility in mind, so proprietary hardware is easy to push to it's limits efficiently.