So you’re in Tweetdeck and someone mentions you in Portuguese or Swedish. You have no idea what it says, but you’re pretty sure it’s not a compliment. Thank goodness there’s the one-click ‘translate’ feature to bail you out. It’s often saved me from my elementary Spanish.
How many times have you used Google’s translation services for help getting something from Korean to English? I have many times.
Now, how nice would it be if you had some reports written for Teradata or SQL Server that you needed translated to Oracle?
It’s called the Translation Scratch Editor
Is it perfect? No, but neither is any translator out there. For SQL Server it’s not so good with SQL 2008 yet, keyword being ‘yet.’ But for your basics it shouldn’t be so bad. I’ve previously done a series of posts comparing SQL Server to Oracle, which I’ve now retired. Being an ‘Oracle’ guy I figure that’s only fair.
But, it’d be a shame not to let you folks know about this wicked cool feature. You might need an ad hoc query or two pulled from one database platform for your Oracle stuff. Of course if you want to migrate your database and application over to Oracle, then that’s a whole other topic.
To access it, go to the Tools menu:
You’ll need to select your translation scheme, then input your SQL to be converted.
I know, I know – we ONLY help you move stuff TO Oracle. It’s all part of that ‘make money’ evil corporate agenda that we’re bound to honor (that’s a joke for you legal folks.)
Let’s look at the code I’ve asked it to translate:
SELECT top 100 (brewery + ', ' + city) AS Beer_Place, isnull(tried, 'Who Knows') FROM Person.beer;
Oracle will have a few problems trying to run this statement.
- the TOP command
- the ‘+’ concatenation
- the isnull function
- the Person schema
Let’s see how SQL Developer translates that to Oracle
SELECT (brewery || ', ' || city) Beer_Place , NVL(tried, 'Who Knows') FROM beer WHERE ROWNUM <= 100;
- The ‘+’ gets written as ‘||’ – and the wordy AS alias gets wiped for good mesaure too
- isnull goes to NVL
- the best we can do for TOP is use the pseudo-column ROWNUM, which we move to the WHERE
- we strip out the schema. notation — you can always add it back!
Of course you might want to actually test this conversion before just sucking it into your new APEX app or webpage. Feel free to get an EXPLAIN PLAN or even run the converted statement live in the scratchpad. Just be sure to assign the editor to a database connection.
I’ve played around with some AdventureWorks views and they all seem to come over nicely. But don’t expect to get very tricky with T-SQL here and have it always work. And, if you want something more than just ad-hoc translations, then I suggest you check out our Migration series of tutorials over on the Oracle Learning Library.
Would You Like to Know More?
This feature is still around – and it works pretty much the same way.
I used it to help this person on StackOverflow just this morning.
I did have to add a line to the declaration section, but otherwise it seemed to work.