Thanks everbody for all the insight.

I have posted my issue in oracle forum as well and am yet to receive any reply.

Unfortunately we have no control over the third party sql server DB.

So might not be able to request them to either change all the columns to varchar or create additional views.

Will update here on the progress.

Thanks.