SQL Server to Oracle migration - déjà vu 001

Every time I undertake a project to migrate a system from SQL Server to Oracle, I am reminded how deficient and cumbersome it is to work with Oracle. Put another way, as a very skilled Oracle developer, each time I am forced to use an alternative DBMS, I get déjà vu and find myself asking myself yet again whether I have backed the wrong horse.

The code below for example is a simple piece of T-SQL for a function that returns a result set containing a country name and currency code. It is so simple to understand; not self documenting code, but close.

CREATE FUNCTION getCountryData()
    RETURNS @x TABLE (countryName VARCHAR(100), currencyCode VARCHAR(3)) AS
     BEGIN
        INSERT @x (countryName, currencyCode)
           SELECT t1.name AS countryName,
                   t2.code AS currencyCode
             FROM countryCurrency t3
               INNER JOIN country t1 
                 ON t1.id = t3.countryID
                   INNER JOIN currency t2
                      ON t2.id = t3.currencyID;
       RETURN
END

Furthermore this function may be queried as though it were a normal database table. For example

SELECT t.countryName, t.currencyCode
  FROM getCountryData() t;

Although not best practice, you could even trim down the code above further to

CREATE FUNCTION getCountryData()
    RETURNS TABLE AS
     RETURN (
              SELECT t1.name AS countryName,
                      t2.code AS currencyCode
                FROM countryCurrency t3
                  INNER JOIN country t1 
                    ON t1.id = t3.countryID
                      INNER JOIN currency t2
                         ON t2.id = t3.currencyID;
             )
END

At the moment I am working on a project migrating a system from SQL Server to Oracle. How do you implement this stock and clear SQL Server functionality shown above in Oracle? The answer is you cannot, as the functionality doesn’t come out of the box implemented in a similar manner. What you have to do in Oracle is write a lot more code. Although tempting to use Oracle REF CURSOR’s to return the result set (I have whinged and blogged on this previously), you cannot query REF CURSORS using a piece of SQL, selecting from named columns etc. The only approaches you have in Oracle, even the recent versions, is to introduce some Oracle bloat. The options include:

1. Perhaps an additional (temporary) table, that you populate with one procedure, and query elsewhere
2. Considerable refactoring of the port of the SQL Server code to Oracle to accommodate for how things are done in Oracle (perhaps the design change is warranted, but perhaps not).
3. A new database object type, a new table type of the object, a pipelined table function (or alternatively returning an associative array if the result set is sufficiently small) returning the table of object type. Cross your fingers that the new UDT can be used everywhere in Oracle, and that if there is necessity to return a row of type in a result set to some invoking process, that it is adequately supported in your database driver too. As mentioned above, the function could return an Oracle REF CURSOR removing necessity for the additional object/object table type, but a REF CURSOR is something you can create, not create and use (see my initial blog article, and the follow-up article).

I don’t want to make unnecessary throw-away comments on my blog so I demonstrate point 3 above, with the new object type, table of type, and so on below.

CREATE TYPE t_country_row AS OBJECT
  (
     countryName VARCHAR2(100),
     currencyCode VARCHAR2(3)
  );
/

CREATE TYPE t_country_tab IS TABLE OF t_country_row;
/

CREATE OR REPLACE FUNCTION getCountryData 
    RETURN t_country_tab PIPELINED AS
 BEGIN
   FOR r IN ( 
              SELECT t1.name AS countryName,
                      t2.code AS currencyCode
                FROM countryCurrency t3
                  INNER JOIN country t1
                    ON t1.id = t3.countryID
                      INNER JOIN currency t2
                        ON t2.id = t3.currencyID
            ) LOOP
                 PIPE ROW(t_country_row(r.countryName, r.currencyCode));
              END LOOP;
END;
/

And then, as before, you can query from the function as though it were a table.

SELECT t.countryName, t.currencyCode
   FROM getCountryData() t;

In summary, even in this most trivial of examples, Oracle requires me to create a couple of additional database types,  code that obfuscates the logic, risk associated with database drivers not handing UDT’s correctly (should you choose to return the UDT in a result set), perhaps concerns with performance, and more lines of code and PSM dependencies to debug and maintain. Note too that these are the hurdles for a single simple database function – should you find yourself migrating just shy of  300 functions (a guesstimate for the number of functions I will need to migrate in my current project), with vastly more complexity than that shown above, the code size I have to maintain and debug quickly balloons in Oracle whereas the SQL Server codebase still presents itself with modest clarity.

— Published by Mike, 09:47 17 March 2017