I'm not always fond of the moving camera either. Like in Spyro, the camera can go behind the wall you're standing near, so you can't even see Spyro anymore. Heck, when the camera is near any wall, it gets harder and harder to move, so you have to run away from the wall in order to look around.
I have tried to play Resident Evil 4 but the over-the-shoulder was so horrible (in my opinion) that I had to put it down. I like the fixed-camera angle like in Dino Crisis (I assume that's how RE was too). I admit, it took a little while to get used to, but I'll be honest. I would rather have RE4 in fixed-camera than the dreaded over-the-shoulder. No offense.
Sorry if I'm being too harsh on over-the-shoulder, but I can't help but dislike it. I mean, the way that it's often angled, it feels like the character is always obscuring part of the view. In RE4's case, if I remember correctly, the character is obscuring the view to the left of the character, and the aiming, in my opinion, is awkward. What was wrong with a first-person view, fixed-camera, or even something more like Spyro's gameplay mechanics?