Database agnostic. ANSI SQL or ORM?

Question

I work in an project that most of the code is written in PL/SQL. We need to refactor the code to support SQL Server (and if possible in the future to be database agnostic). They decided to transform the PL SQL code to ANSI SQL but for some reason it doesn't sound a good idea to me.

Can you please tell what would be the pros and cons of tranforming to ANSI instead of using an ORM?

Thanks

Answer 1

The problem is that pl/sql is a lot more than just sql statements. It has logic, types, objects, functions, looping and ect....

So while you could change the sql statements to be ansi compatible, there is no such animal for the procedural language.

You would need to rewrite the plsql in some sort of independent middleware language and then you're dependent on that.

Additionally, different databases handle committing changes and locking differently, so when writing code, the differences need to be understood by the developers.

Answer 2

One approach to implement vendor agnostic (dynamic) procedural logic is using a third party product like jOOQ, where you can write something like:

ctx.begin(
    for_(i).in(1, 10).loop(
        insertInto(t).columns(c).values(i)
    )
).execute();

Which would translate to (a selection):

-- Db2
BEGIN
  DECLARE i integer;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END
 
-- Firebird
EXECUTE BLOCK AS
  DECLARE i integer;
BEGIN
  :i = 1;
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (c)
    VALUES (:i);
    :i = (:i + 1);
  END
END
 
-- MariaDB
BEGIN NOT ATOMIC
  FOR i IN 1 .. 10 DO
    INSERT INTO t (c)
    VALUES (i);
  END FOR;
END;
 
-- Oracle
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
  END LOOP;
END;
 
-- PostgreSQL
DO $$
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
  END LOOP;
END;
$$
 
-- SQL Server
BEGIN
  DECLARE @i int;
  BEGIN
    SET @i = 1;
    WHILE @i <= 10 BEGIN
      INSERT INTO t (c)
      VALUES (@i);
      SET @i = (@i + 1);
    END;
  END;
END;

You'll be limited to the least common denominator that is currently being supported by jOOQ, which, as of jOOQ 3.15, removes some features from PL/SQL (such as RECORD types, TABLE types, etc.), but given the requirement of being Oracle/SQL Server agnostic, that may be good enough.

Disclaimer: I work for the vendor behind jOOQ.