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?
2018 Update
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.
10 Comments
There are more advanced methods, for example, Ispirer toolkit, which can convert stored procedures, functions or triggers from various databases into Oracle – https://www.ispirer.com/products/oracle-migration
yes, but not free, although I know customers have used it successfully, this isnt an endorsement or recommendation by me
Hi Jeff,
The scratch editor from DB2 to Oracle doesn’t seem to work and throws the below error in version 4.1.3.20
/**SQLDEV:FAILED TO TRANSLATE
..
**/
Any resolution please..
Thanks!
Selvakumar Nagulan
Yeah I see the same thing, looks like a bug
Using SQL Developer 3.2.09.23 and I do not see the Translation Scratch Editor under the Tools | Migration submenu. Did they remove it? I was able to find info on TSE in Help so I know I’m looking in the right location.
That’s very nice. These little differences can turn out to be quite tricky to remember, when one doesn’t have much experience with a new platform. Just curious, since Sybase T-SQL can also be converted to PL/SQL. Does SQL Developer correctly translate a Sybase “TOP .. START AT ..” clause into PL / SQL with nested selects filtering on ROWNUM or ROW_NUMBER()?
Good question. I would assume ‘yes’, but since I don’t have a Sybase ASE instance handy, I can’t say for sure. I’ll find out and let you know.
Pingback: Oracle Vs SQL Server
Jeff,
That’s pretty sweet! I had no idea this tool was available in SQL Developer. I could’ve probably saved myself many hours over the last few years with it.
Ansley
Don’t tell anyone, but I didn’t know it was there either. The joys of learning a new IDE 🙂