Depends on the CPU. Most of Intel's desktop CPUs are putting out a lot of heat these days, so you may need more than a XP120 for competative overclocking(water cooling, etc). That being said, and of Intel's 6xx line can be overclocked quite a bit if you have the right board. Good cooling and good case airflow are a must.
If you're feeling really adventurous, you can OC a Pentium D, but be prepared for hideous heat output.
AMD's current CPUs, both single and dual-core, are much easier to overclock on air. Many of the low-end and mid-range CPUs OC well on stock cooling. The X2 3800+, A64 3000+, A64 3200+, and Sempron 2800+ are all great overclockers on stock cooling, and it gets better with aftermarket HSFs. The 3700+ San Diegos are also pretty good, though probably not worth it now that the X2 3800+ is available.