Toad World® Forums

Conversion of SQL scripts to ANSI Format

Hi Folks … I have a scenario where one of the database is moved from ORACLE DB
to SQL SERVER DB.

There are many sql scripts where (+) is used for outer joins.

My concern is does SQL SERVER understand (+) the same way as ORACLE dose.

Will I have to rewite all my scripts ?

If NO then dose TOAD have any tool/option where by these codes are run as is i.e
with (+) symbal and it runs fine against SQL SERVER database ?

Most probably by internally converting those codes in ANSI SQL format i.e. LEFT
/RIGHT/FULL outer join.

Regards,

Shiva

The (+) is Oracle specific syntax and your queries will need to be re-written.

Toad can help!

The Query Builder can re-write these using the proper ANSI syntax and I believe
there is an Action that can be used for doing this en masse.

The Toad SQL Query Builder can convert from/to ANSI join syntax for existing
statements – try it out J

Thanks Jeff … I found that option in Toad and it does Oracle to ANSI format (
and otherway ) conversion very nicely.

But it is very manual.

I have to copy each code in Query Builder and then hit the “ANSI Syntax” option.

Is there a way to convert these SQL files lying in a directly by some command ?

Much better if there was a tool like TOAD which would accept Oracle SCRIPTS (
with + ) and talk to SQL SERVER DB .

Regards,

Shiva

Utilities > Automation Designer

You can now build a Toad macro/widget/app/thingy that allows you to point to
multiple files and have them batch re-written in ANSI join syntax. Just pick the
output directory and you’re good to go.

If you put your code into the same file, you could run this via command-line as
well (after you define the action)

Toad.exe –a MyActionName

Jeff …this is wonderful.Thanks a bunch.

However I wish Toad had that option to accept ORACLE SYNTAX while talking to no
Oracle Database.

Maybe Toad team can have this option added in next version.

Regards,

Shiv a

Toad only connects to Oracle…or are you asking for Toad for Data Analysts
(which can connect to SQL or Oracle) to accept Oracle Join Syntax?

I am looking for any Tool which can connect to SQL SERVER ( DB other than Oracle
) but accept Oracle Join Syntax.

If Toad for Data Analysts does this then I am very happy.

This will save me from all this conversion when one of my DB is migrated from
Oracle to SQL Server.

Regards,

Shiv a

It is not the GUI that accepts the syntax but the server.

So The oracle join syntax only works with Oracle.

If you want a GUI that accepts Oracle syntax, CONVERTS it to SQL SERVER and then
sends it to SQL SERVER I wish you good luck.

I believe I saw something on the Toadbeta list about the latest version of Toad
(possibly the BETA) converting the (+) syntax to INNER/OUTER join syntax.

The Toad for Oracle query builder has had the capability to convert from + to
ANSI for a long time now.

As for the other question – our toad for data analysts supports a syntax
that works across all databases and it works out the platform specific details.
You’ll need to download, install and read-up on how it does that. For
example it can do a cross database join – i.e. join oracle table to sql
server table. So it knows all about the various syntaxes J

Hello! I am also looking for a toll for convering old-style MSSQL 2005 syntax to 2012 syntax

for example

RAISERROR @errno @errmsg

into

raiserror(@errmsg,@errno,1)

and

g.klas1_type_id =* a.klas1_type_id

into LEFT OUTER JOIN ON g.klas1_type_id = a.klas1_type_id

I hope that TODD Query Builer can help? but after installation ToadforSQLServer_Freeware_6.6.0.963.zip

I’ve found that Query Builder Is not enabled (not licensed)

Gow can I try this feature? I have a lot of allstylescripts to convert

Please help!