I have been cath'd twice in my life. Both times at A+E, so neither were pleasant experiences.
The first time I was cath'd was when I was about 9 yrs old. As a child (and occasionally even now), I suffered from recurrent UTIs. One week I had been starting to develop another infection, so was taking antibiotics as usual. However, by Friday evening I was starting to feel worse. After speaking to the doctor he suggested leaving it to the next morning to see how I was (had only been on antibiotics a couple of days, so thought they hadn't kicked in properly) and advised plenty of fluid.
Next morning I didn't feel any better, and deperately needed to urinate but was unable to. This resulted in mum taking me to A+E. Fortunately I was seen fairly quickly, where a quick exam determined that my bladder was indeed distended. As I still couldn't urinate, it was decided to catheterise me. When Dr turned up witht the catheter I was frieghtened by the size of it - it seemed massive, but he said it was correct size.
He tried to insert it, but it was too painful and he couldn't even get it into the tip of my penis. After several attempts to insert it he gave up and went to get another Dr. By this time, despite the pain, I had developed a rather full and painful erection, which obviously wasn't helping. When the 2nd Dr eventually managed to get a smaller catheter into my penis I thought the worst was over. However, getting it into the bladder was another matter. Eventually he managed and the release of the urine was a great relief. I then spent the weekend in hopsital until the infection settled and I was able to urinate freely.
The other time I was catheterised was after I was knocked off my bike when I was 25. Due to the pain and swelling in the pelvic area, I was again catheterised. I don't remember much about this instance as I had decent pain relief. I was later informed that they were worried I had pelvic / spinal injuries as I had little sensation in my legs and had suddenly developed an erection, which apparently can be a sign of spinal injury. Fortunately, I did not have any serious injuries but severe bruising / swelling which seemed to take forever to disappear.