Re: tool: convert classic oracle sql to ansi?

From: Michael D O'Shea <michael.oshea_at_tessella.com>
Date: Fri, 19 Dec 2008 02:55:36 -0800 (PST)
Message-ID: <e5a9bb31-9a2b-4ead-b5c2-b990e4af2dc2@f24g2000vbf.googlegroups.com>

On Dec 16, 5:18 pm, steph <stepha..._at_yahoo.de> wrote:
> Hi Group,
> Does anybody know of a way, tool or whatever to convert classic oracle
> sql-statements to ansi-sql and vice versa?
> Thanks,
> Stephan

Hi Stephan, I do not know of a tool to perform this task but expect it would be neither difficult nor time consuming to write one.

To convert between the varying syntax of Oracle and ANSI SQL, your approach should be cautious of just replacing (+) with "INNER JOIN" etc. AFAIK the ANSI join syntax offers more rich functionality than the familiar Oracle specific syntax. For example (below), this compact ANSI full outer join cannot be represented so neatly and clearly using the Oracle specific syntax.

Regards
Mike

TESSELLA Michael.OS..._at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429

SQL>
SQL>
SQL>
SQL> SELECT t1.a, t2.a

  2 FROM tblTest1 t1 FULL OUTER JOIN tblTest2 t2   3 ON t1.a = t2.a;

         A A
---------- ----------

         6          6
         7          7
         8          8
         9          9
        10         10
         5
         3
         1
         2
         4
                   13
                   15
                   12
                   11
                   14

15 rows selected.
SQL>
SQL> SELECT *
  2 FROM tbltest1;

         A

---

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> SELECT *
  2 FROM tblTest2;

         A

---

         6
         7
         8
         9
        10
        11
        12
        13
        14
        15

10 rows selected.

SQL>
SQL>
SQL> SELECT *

  2 FROM V$VERSION; BANNER

---

Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod PL/SQL Release 10.1.0.4.0 - Production
CORE 10.1.0.4.0 Production
TNS for 32-bit Windows: Version 10.1.0.4.0 - Production NLSRTL Version 10.1.0.4.0 - Production

SQL> Received on Fri Dec 19 2008 - 04:55:36 CST