Opera (on WinXP at least) automatically searches "C:\Program Files\VideoLAN\VLC" for the mozilla npvlc.dll plug-in.
If Opera finds it, it will show up under tools -> advanced -> plug-ins and Opera will be able to use it.
In Opera, you may also have to go to tools -> preferences -> advanced -> downloads -> uncheck "hide file types" and edit application/x-ogg, application/ogg ogg, application/x-vlc-plugin and application/x-google-vlc-plugin to "Use-plugin".
If Opera doesn't detect npvlc.dll in that directory, copy it to Opera's plugins directory and restart Opera.
For example,
this works in the
latest weekly of Opera.
Also, on a side, Opera has been working on
video element support. It will allow Opera to play ogg theora videos natively with no need for a plug-in. Look for that some time in the future once the video element spec becomes stable.