ThatJeffSmith

SQL Developer: Give me a Google Translator for SQL!

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?’

–MSFW, mostly safe for work.