Generating a Stored Procedure Declarations for NHibernate in PL/SQL

I discussed the execution of Oracle stored procedures via NHibernate in a previous post, and the promise to be continued will now be fulfilled with the generation of the NHibernate mapping file for Stored Procedures.

We need two cursors, one iterating through all procedures, the second one to iterate through the parameter list of a given procedure:

cursor cObjects is
select object_type, object_name
from all_objects
where object_type in ('PROCEDURE')
and [your criteria here]
order by 1, 2;

cursor cColumns(oname in varchar2) is
select p.position, p.argument_name, p.data_type, p.default_length,
p.in_out, p.data_length, p.data_precision, p.data_scale, p.char_length
from all_arguments p
where p.object_name = oname
and [your criteria here]
order by p.position;

We need an XML header, and an sql-query element for each stored procedure:

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

begin
  dbms_output.enable(1000000);
  dbms_output.put_line('<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="protodal" namespace="protodal">');

  for obj in cObjects loop
    dbms_output.put_line('  <sql-query name="' ||
      replace(initcap(obj.object_name), '_', '') || '" callable="true">');

    args := '';

The most “difficult” part of the generator is to translate the PL/SQL data types into C#/.Net data types. This can be achieved in a CASE statement:

    for col in cColumns(obj.object_name) loop
      if (args is null) then
        args := '(:' || col.argument_name;
      else
        args := args || ', :' || col.argument_name;
      end if;

      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('    <query-param name="' || col.argument_name ||
        '" type="' || csdatatype || '" />');
    end loop;

Args contains the list of parameters for each stored procedure, invoking it inside a BEGIN … END; clause:

    if (args is not null) then
      args := args || ')';
    end if;

    dbms_output.put_line('begin ' || obj.object_name || args || '; end;');
    dbms_output.put_line('  </sql-query>');
  end loop;

  dbms_output.put_line('</hibernate-mapping>');
end;

xxx