The NF attended the Gush many years back and although I wouldn't consider myself a "Gushie" I am a huge fan of Rav Yehuda Amital's. I recall having heard in his name that by and large melodies are not mekabel tumah (If I'm misquoting Rav Amital, I kindly request that all the Gushies reading this to refrain from flaming me badly --- simply correct what I've mistated). In other words, there is nothing inherently wrong with singing the melody certainly from a secular song and even from a (non-Jewish) religious song. (In contrast, trying to make use of the words from a (non-Jewish) religious prayer could pose halachik problems.) Thus in theory, melodies from Jewish, secular or non-Jewish religious sources can be used in tephilla.
Now that doesn't mean that you HAVE TO or SHOULD use random melodies. Baalei tephila still need to think about whether the melody is appropriate. Will using the melody enhance davening/kavanah or will it distract? Lastly, can the baal tephilla pull off singing an unusal solo or will the melody go unrecognized as the guy flies off key?
As a kid I once heard the story of a chazzan in our little German shul in my hometown that for "ועל כולם סלח לנו" in the vidui of y"k one year in the 1960s, a melody taken from a Cabaret in Berlin from before WWII was used. The chazzan's argument was that by using a melody that would bring to mind a strip tease, he was helping the mitpalel focus on his sins. Needless to say the chazzan's contract was not renewed for the following year.
This past Shabbat, a guy, let's call him Yankel, got up there in NF's local shul and for kedusha of Shacharit used "O sole mio". This is how it went:
1. Yankel begins Mimkomcha rather shakely. After a few bars, a distinct melody is still yet to immerge.
2. No one is really sure where Yankel is going with this and there is very akward silence
3. Yankel actually hits some of the more difficult notes at the end of O sole mio and repeats the melody in the 2nd part of Mimkomcha
4. Members of the kahal are looking at one another nervously and silently mouthing - "WTF"
5. Yankel again hits some of the diffuclt notes and finishes kedusha. The kahal is relieved
6. As Yankel begins yismach moshe, members of the kahal try to figure out what the hell that was --- the English amongst us are convinced its the Cornetto advert. Some members start licking their talitot.
7. Members of the kahal go home after shul ends and note that the cholent has gone off, the sephirot are out of wack and they have this incredible urge to mix dance.
8. Members begin to pass thier children to molech and cohort with the daughters of Midian.
Yes, Yankel is guilty of using a melody that was inappropriate. Maybe Yankel innocently felt that an Italian love song could reflect his yearning for the geula (or an ice cream cone) and thus could bring the kahal to reach spiritual nirvana in that heimish sort of way. But what Yankel did was wrong, plain wrong. In some kehillot Yankel would be taken out and shot. But we are a kinder, gentler kehilla. Instead we'll just give him a warning and a massive GONG. Please Yankel, for the sake of our children, don't do it again.