Conversion of SQL scripts to ANSI Format - Toad for Oracle Forum - Toad for Oracle - Toad World

Conversion of SQL scripts to ANSI Format

Follow / 15 Jul 2010 at 11:59pm

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

Replies

  • Follow / 16 Jul 2010 at 12:17am

    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.

  • Follow / 16 Jul 2010 at 12:17am

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

  • Follow / 16 Jul 2010 at 4:23pm

    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

  • Follow / 16 Jul 2010 at 4:32pm
    image001.jpg

    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

  • Follow / 16 Jul 2010 at 4:48pm

    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

  • Follow / 16 Jul 2010 at 5:04pm

    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?

  • Follow / 16 Jul 2010 at 5:11pm

    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

  • Follow / 17 Jul 2010 at 5:17pm

    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.

  • Follow / 17 Jul 2010 at 5:26pm

    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

  • Follow / 7 Oct 2016 at 4:16pm

    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!