Generating a Stored Procedure Wrapper for NHibernate in PL/SQL

I discussed the execution of Oracle stored procedures via NHibernate in a previous post, and also the generation of the NHibernate .hbm.xml mapping files for Stored Procedures.

We use the same cursor declaration as in my post on generating the .hbm.xml file, and also the same parameter type conversion logic:

  csdatatype varchar2(50);
  args varchar2(500);

begin
  dbms_output.enable(1000000);
  dbms_output.put_line('using System.Collections;');
  dbms_output.put_line('using NHibernate;');
  dbms_output.put_line('');
  dbms_output.put_line('namespace MyDAL');
  dbms_output.put_line('{');
  dbms_output.put_line('    public static class Procedures');
  dbms_output.put_line('    {');

  for obj in cObjects loop
    args := '';

    for col in cColumns(obj.object_name) loop
      csdatatype := col.data_type;

      case col.data_type
        when 'NVARCHAR2' then
          csdatatype := 'string';
        when 'VARCHAR2' then
          csdatatype := 'string';
        when 'CHAR' then
          if col.char_length=1 then
            csdatatype := 'bool';
          else
            csdatatype := 'string';
          end if;
        when 'NUMBER' then
          if col.data_precision is null then
            csdatatype := 'long';
          elsif col.data_precision < 10 then
            csdatatype := 'int';
          else
            csdatatype := 'long';
          end if;
        else
          csdatatype := col.data_type;
      end case;

      args := args || ', ' || csdatatype || ' ' || col.argument_name;
    end loop;

After concatenating the parameter list for the C# method, we create an IQuery object, add parameters using the SetParameter<>() method, and return the result as an IList:

    dbms_output.put_line('        public static IList ' ||
      replace(initcap(obj.object_name), '_', '')
        || '(ISession session' || args || ')');
    dbms_output.put_line('        {');
    dbms_output.put_line('            IQuery sp = session.GetNamedQuery("'
        || replace(initcap(obj.object_name), '_', '') || '");');

    for col in cColumns(obj.object_name) loop
      csdatatype := col.data_type;

      case col.data_type
        when 'NVARCHAR2' then
          csdatatype := 'string';
        when 'VARCHAR2' then
          csdatatype := 'string';
        when 'CHAR' then
          if col.char_length=1 then
            csdatatype := 'bool';
          else
            csdatatype := 'string';
          end if;
        when 'NUMBER' then
          if col.data_precision is null then
            csdatatype := 'long';
          elsif col.data_precision < 10 then
            csdatatype := 'int';
          else
            csdatatype := 'long';
          end if;
        else
          csdatatype := col.data_type;
      end case;

      dbms_output.put_line('            sp.SetParameter<'
          || csdatatype || '>("' ||
        replace(initcap(col.argument_name), '_', '') || '", ' ||
        replace(initcap(col.argument_name), '_', '') || ');');
    end loop;

    dbms_output.put_line('            return sp.List();');
    dbms_output.put_line('        }');
  end loop;

  dbms_output.put_line('    }');
  dbms_output.put_line('}');

The IQuery object references the generated HBM declaration, and executes the PL/SQL code behind the sql-query statement

BEGIN MyStoredProc(params); END;