SQL-To-Oracle Translator

In the Summer of 1998, the Ingenium team was faced with a dilemma: although their customer base demanded both SQL Server and Oracle DBMS support, the team had focused several years developing over a 130,000 lines of commercial code targeted specifically for SQL Server, the simpler of the two platforms. Near the end of the development cycle, the team needed to decide how they were going to effectively deliver and maintain the product on a second platform. All of the traditional solutions (adding conditional code for every SQL statement, creating a separate code base, etc.) required significant developer training, increased the amount of code to maintain, and effectively doubled the testing effort.

Although there is substantial structural and notational differences between the SQL Syntax used by SQL Server and Oracle, both platforms were almost functionally equivalent. . . the problem was really a matter of language translation.

Solution

Armed with a whitepaper that one of the developers wrote to explain the differences in notation, I began the process of writing a real-time translator that would take almost any valid SQL Server statement and translate it into the equivalent Oracle notation.

After little more than a week’s effort, we had a single function that we were able to insert into our Data Access layer (fortunately, we had already abstracted data access into a single object) that would translate SQL statements at runtime.

The impact of this solution was profound. Not only did it minimize developer education (developers could continue to program with their preferred language), but dramatically reduced the risk of database-specific bugs. Every new feature developed by either the core developer or consultants was automatically available to both database platforms.

At the time, this solution had the biggest financial return of any of my contributions as Lead Architect on the Ingenium team.

Sample

SQL Server Syntax		Oracle Syntax
SELECT Emp_LName AS ‘Last Name’, Emp_FName AS ‘First Name’, Skl_Name AS ‘Skill Name’ FROM (Emp INNER JOIN EmpSkl ON Emp_PK = EmpSkl_EmpFK) LEFT JOIN Skl ON EmpSkl_SklFK = SklPK WHERE Emp_PK = 100;	=	SELECT Emp_LName AS “Last Name”, Emp_FName AS “First Name”, Skl_Name AS “Skill Name” FROM Public.Emp, Public.EmpSkl, Public.Skl WHERE ((Emp_PK = EmpSkl_EmpFK) AND EmpSkl_SklFK = SklPK(+)) AND Emp_PK = 100;

Employer: Meliora Systems
Roles: Lead Software Architect, Software Engineer
Style: Commercially-available, Database-driven
Focus Area: Language Processing
Technology: Visual Basic 6.0, SQL Server 6.5, SQL Server 7.0, Oracle 7.x