Charles Stross wrote, in an introduction to a book, about the difference between James Bond and real spies, and pointed out that they are on the complete opposite side of any given spectrum.

Nobody wants to pretend they're a spy. Everyone wants to pretend they're James Bond.

Robert Baer's book "See No Evil" (http://www.amazon.com/See-No-Evil-Soldier-Terrorism/dp/14000...) also does a good job of this. Yes, he did get a crash course in arms and explosives while at The Farm (if I remember it correctly, he actually characterizes it as "terrorist school"). But what CIA case officers actually do is convince other people to spy for them - they don't do the spying themselves. They can't, because everyone knows they're American, since their cover is usually that they work for the US State Department at the embassy.

Baer also points out that a CIA case officer's job is unique in government, in that their explicit job is to convince someone else to break the law. Not US law, of course, but still: their job is to convince someone else to commit treason.

In the previous decade, the CIA has been expanding it's paramilitary officers too though. Less convincing people to spy, and more blowing stuff up. More of the action and cool gadgets part of James Bond.