I have a suite of applications that was designed against MSSQL. We have a new vendor which will involve installing our applications there, but we absolutely must use Oracle in that installation. Other customers being unaffected, we want to keep MSSQL everywhere else.
Question 1: What are the best practices relevant to this situation?
Question 2: Is there a way (SDK, etc) to convert TSQL queries to PSQL dynamically (without manually entering them into a UI, etc)?
I've seen some applications try to use a common subset of SQL that works across MSSQL and Oracle - they don't work out too good. (Plus you end up using none of the more advanced / non-standard features of either because the other can't support it.)
Unfortunately I think the best way to achieve what you're after is to write the queries again in PLSQL. How hard this will be will vary greatly depending on your number of queries and their complexity. I suspect this route will pay off over time, though and you'll end up with a more solid and/or performant solution.
To answer your second question, I've not come across any such tool.
How about using stored procedures? All the sql lives on the database and you only call the name of the procedure and pass the parameters.
Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.