I just had my "foundation year" practicum (not sure if terminology is the same at all schools) at a cancer center. I worked in an outpatient center, and I've learned that inpatient vs. outpatient can make a big difference in the type of work that you do on a daily basis. In my experience, I didn't find there to be much connecting to patients. But that is the nature of outpatient and how my center operates. To compare, a fellow student at my site said she had worked on an inpatient hemacology oncology floor and she described the work to be similar to what you describe.
In my personal opinion, if you really want to have a job with a thorough mix of health and mental health -- you might want to look at any transplant services. Each patient has to have a full biopsychosocial intake session with a LCSW and you generally then have to work with them throughout the whole transplant process. I followed a bone marrow transplant social worker as part of the experience with the cancer center, and the duties he performed were a lot more involved/complex than the case management things that both the general oncology social workers (inpatient and outpatient) both performed.
Also, this might be a little bit harder to locate a job in, but there are some parts of the country that are really starting to focus on the "medical home" model as well as outcome based healthcare. As part of a different program, I've been working in a PCP setting using the medical home model and it has been incredible. We work as a team evaluating patients, the social workers are there to evaluate the social aspects of the patients issues and help find solutions to barriers that may be impeding healthcare. I have learned soooooooo much about other professionals and their role as well as mine!
Basically if you are looking for a mental challenge -- find the most complicated medical specialty. If the social workers there are used properly (not as overpaid case managers) you will find that your duties increase in complexity as well.
Another note, I only got into these type of practicums as a first year student because I had experience working in the medical field (biller and coder) and I already knew the terminology and was very experienced working in the healthcare setting (I didn't just work in a back office away from everyone). I was always the only first year student out of the bunch, so I'd say my experiences were the exception rather than the rule.
Also, I have heard RUMORS, can't verify because I've never worked there, that in some VA settings social workers have to know a lot about both because of all the morbidities that veterans deal with. Also VA hospitals have both medical and mental health social workers, which I'm sure both positions have to deal with a lot of cross over. I've heard though that in some VA's the social workers are solely used for case management type duties.