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

Having migrated many systems from SQL Server to Oracle, I used to want to cry when I encounter this type of construct.

CREATE PROCEDURE GetLoadsOfData AS
BEGIN
  SELECT t1.name salutation FROM salutation t1; -- Salutations, Mr, Mrs, ...
  SELECT t1.name skill FROM technicalSkill t1;  -- Oracle, C#, XSLT, ...
  SELECT t1.name AS countryName,                -- Countries and currency codes
         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

To state the blindingly obvious, this piece of innocuous looking T-SQL

• returns three result sets from a database ‘procedure’
• each result set has a different definition; different column names, possibly with different data types (int, VARCHAR), sizes VARCHAR(10) vs. VARCHAR(100), and so on
• the result sets cannot be ‘used’ within SQL Server (some other technology is required; SQL Server can create these multi result sets but not use them easily)
• Looking at the T-SQL procedure name, developers have no idea what result-sets are returned by the procedure (sic), nor even how many result-sets there are
• consumers of the result-set must be aware of the result set order; 1NF requires there is no ordering left to right/up or down etc but as implemented there is an introduced ordering of the result-sets
• Oracle can’t do this (historically) – migrating the SQL Server code to Oracle may require a lot of refactoring.

Developers can rarely defend this type of construct, over for example three different methods named GetSalutations, GetCountries, and GetCurrencies (and ask yourself,  isn’t the purpose of each if these methods clear when discretely named like this?), other than reducing the ‘chatter’ in a n-tier application for retrieving lookup data. Put another way, with a single database method, there is a minimal number of database procedures, and some application data layer/middleware can initialise itself with only one invocation, not multiple invocations, loads of interprocess communication, and concerns with lazy or other types of loading and synchronisation of all the API method calls.

Despite the SQL Server DBMS not providing direct functionality to consume procedures that return multiple result-sets, the Microsoft technology stack (and third-party) appear to have no interoperability issues with consuming the result sets.  For example, C# using Dapper (I’ve chosen Dapper as I doubt you can get a more cut-down ORM meaning I can minimally demonstrate consumption of multiple result-sets), as shown below, shows basic ORM without digressing into other and more complex object relational mapping technologies such  Entity Framework, ADO.NET, or flavours of Hibernate.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using Dapper;

namespace ConsoleApplication3787
{
  public class Country 
  {
    public String CountryName { get; set; }
    public String CurrencyCode { get; set; }
    //other properties/omitted
  }

  class Program
  {
    public List<Country> Countries { get; set; }
    public List<String> Salutations { get; set; }
    public List<String> TechnicalSkill { get; set; }
    public void Setup() //setup/initialise application lookup data
    {
      using (IDbConnection db = new SqlConnection(@"Server=MACBOOK003\SQLEXPRESS;Trusted_Connection=true;Initial Catalog=DEV"))
      {
        db.Open();
        var multipleResultSets = db.QueryMultiple(sql: "GetLoadsOfData", commandType: CommandType.StoredProcedure);
            Salutations = multipleResultSets.Read<String>().ToList();
            TechnicalSkill = multipleResultSets.Read<String>().ToList();
            Countries = multipleResultSets.Read<Country>().ToList();
        db.Close();
      }
    }
    static void Main(string[] args)
    {
      var app = new Program();
      app.Setup();
      //other stuff
    }
  }
}

I am migrating the codebase

from:

Microsoft SQL Server 2005 – 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 6.1 (Build 7601: Service Pack 1)

to:

Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production

that were released in 2005 and 2007 respectively. The other technologies are C# and Dapper.

Now, back to the subject of SQL Server to Oracle migration. Given that during the database migration in my current project, there is little wiggle room to justify system architecture changes, or considerable changes to the API (that would involve pervasive middleware changes and knock-on changes to dependent components and unit tests) , the question is now how to return multiple result-sets returned in Oracle.

The answer is to use DBMS_SQL.RETURN_RESULT, that requires an incredible amount of code bloat to coerce Oracle into doing something that SQL Server has been able to do since at least 2005. Unfortunately, DBMS_SQL.RETURN_RESULT is only available from Oracle version 12c, the most recent version of the Oracle database, and not my target version (as at March 2017, the most recent version of Oracle Express Edition is still 11g]).

Oh dear – weeks more of refactoring because the Oracle feature set is behind competitors. Guru Steven Feuerstein makes a comment on this issue too. For me however, just this once, I would have preferred Oracle had not implemented ‘procedures’ that return multiple result-sets. This isn’t because of all the additional Oracle codebase bloat (compared to SQL Server – see Feuerstein’s code examples and my SQL Server example above) that seem to accompany most Oracle retrofitted functionality, but because constructs like GetLoadsOfData, rather than discrete clearly named self describing API methods that return one type of data, just have to be the model for producing maintainable code.

Although I regularly experience the ‘Why does/doesn’t Oracle do this’ déjà vu, especially in database migration projects, should 12c Express Edition become available, I will not voluntarily adopt the model of writing an API that returns some complex structured object even though DBMS_SQL.RETURN_RESULT would be available to me. Database PSM methods that return one thing and as described by the PSM method name are ……. well, do I need to write anything at all here, this blog article should be a lot smaller, as it’s all just about common sense programming isn’t it?

— Published by Mike, 10:23 20 March 2017