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);

  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;
        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';
            csdatatype := 'string';
          end if;
        when 'NUMBER' then
          if col.data_precision is null then
            csdatatype := 'long';
          elsif col.data_precision < 10 then
            csdatatype := 'int';
            csdatatype := 'long';
          end if;
          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;



1 thought on “Generating a Stored Procedure Declarations for NHibernate in PL/SQL

  1. Pingback: Generating a Stored Procedure Wrapper for NHibernate in PL/SQL « devioblog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.