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:

The Migration Workbench can do much more than migration data

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.)

'Icky' code in, excellence out! #EvilLaugh

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

Preview your Oracle code, explain it, or run it!
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.

I had to also declare v_SQL as a varchar2()
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

10 Comments

    • yes, but not free, although I know customers have used it successfully, this isnt an endorsement or recommendation by me

  1. 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

  2. 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.

  3. 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()?

    • JeffS

      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.

  4. Pingback: Oracle Vs SQL Server

  5. 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

    • JeffS

      Don’t tell anyone, but I didn’t know it was there either. The joys of learning a new IDE 🙂

Reply To Ansley Cancel Reply