" 'Course I'd have given you an hour with her first."
Canon Facts:
1. Angels are capable of having sex while possessing a vessel, and some of them do.
- Gabriel (as the Trickster) [S02E15]
- Future!Hippy!Cas and his orgies [S05E04]
- Balthazar "This morning I had a menage a... what's French for twelve?" [S06E03]
2. Castiel is a virgin. He's never done any "cloud seeding" even as an angel. [S05E03]
3. S05E03 - Castiel agrees to go to the brothel with Dean, although he looks incredibly uncomfortable while he's there. He seems to be reluctantly going along with the plan to have him lose his virginity to a prostitute, until he makes the mistake of trying to connect with her (in his own special way) and scares her off instead.
4. S06E10 - Castiel watches porn and pronounces it "complicated". He also tries to make sense of the plot, and apparently becomes physically aroused. (He looks somewhat nonplussed to be having a physical reaction.)
5. S06E10 - When Meg kisses Castiel as a distraction to grab his angel sword (not a euphemism), he decides to kiss her back very forcefully (and is apparently a pretty good kisser, according to Meg).
Headcanon:
Note: In my headcanon, poor Jimmy Novak's soul moved on the first time Castiel got blown up by Raphael. Canon has never given a definitive answer one way or the other. Personally, I think if God decided to bring Cas back and put him back in his vessel (twice), hopefully He wouldn't force Jimmy back in there too. (For all we know God could have given Jimmy his body back the first time He brought back Cas, and Jimmy is now living happily ever after with his wife and daughter.) The vessel may still have some residual traces of Jimmy, like his love of hamburgers, but Castiel is alone in there.
So what was up with Castiel at the brothel in S05E03?
Cas is confident in every other area, so why the look of near-panic? If he was that afraid of the idea, why would he even go in the first place?
I think the fear might be because it's an area of human nature that's completely foreign and inscrutable to him. It involves letting the natural impulses of the human body take over, which might be a disturbing concept for him. He's already been told many, many times that he doesn't fit in as a human, doesn't understand humor, and behaves strangely, and that's when he's in control of his vessel. Letting go of that control and reacting as a human could be kind of scary. And that's just the physical stuff. He's probably also aware that there are emotional complexities involved, and he's not much better at comprehending human emotions.
As for why Cas would go along to the brothel, the obvious answer is because Dean wanted him to. By S5 it's pretty clear that Dean has become Cas' mentor when it comes to humanity. If Dean thinks this is important (and if Cas has observed Dean for any length of time, it's obvious sex is important to Dean) then it must be important to all humans. If Dean thinks Cas needs to experience it, then Cas will go along with it, even if he doesn't like the idea much.
What the heck was that with the porn and the demon-kissing in S06E10?
Honestly I think someone decided that since Cas and Meg admittedly had some chemistry during their accidental embrace in S05E10, why not try for more? I'm not really sure it worked, and the questions it raised about Cas and sexuality were completely dropped again after that. That's a meta answer though, so here's my headcanon answer.
1. It was an odd attempt at a distraction. As we find out later, Castiel has been in a secret partnership with Crowley for over a year. The Winchesters' mission in this episode is to track down and possibly kill Crowley. Cas is stuck in the middle. He wants to help his friends, but he needs to protect his secret partner. Maybe the porn & kissing was just to throw everyone off balance? (It's not a very good plan, but this is Cas we're talking about. He's not very good at deception.)
2. Castiel is more comfortable in his vessel now and he's more curious about human reactions, including sex. After all, Cas ended up as good as human at the end of Season 5. He experienced headaches, itchy insect bites, the need to sleep, and a few moments of near-fatal illness at the hands of Pestilence. After all that, maybe the idea of sex doesn't seem quite so daunting?
Would Castiel ever have sex?
Maybe. I think Cas is somewhat curious about human sexuality, but it's not important to him like it is to humans. If he did have sex, I think it would have to be with someone he really trusted, and it would probably only happen if the other person was the one who wanted it. I doubt he would be the one to initiate it.
Of course the person he trusts most is Dean. I think Castiel does love Dean, and if asked he would probably admit it without any reservation. But I don't think it's a sexual thing at all for Cas. Could it be if Dean wanted it to be? Probably. (Would Dean want it to be? That's a whole different essay and I'm not going there.)
As far as RP goes, I think it would be fun to play out, but I'm not sure it would happen in a game setting without a lot of setup and consideration.