Generating NHibernate Class in PL/SQL

Data access classes for use in NHibernate take the form

public class MyFoo
{
  public virtual int MyBar { get; set; }
  ...
}

i.e. they are public classes and their member variables are declared public virtual with get and set accessors.

Due to this regular pattern and the fact that tables and columns can easily be retrieved in Oracle using the DBA_, ALL_, or USER_ views, we can thus write a stored procedure to generate the data access classes. The stored procedure can then be invoked during the build process to reflect changes in the data model

First, define cursors for tables and views and their columns:

create or replace
procedure dev_generate_dal_classes
is
  csdatatype varchar2(50);
  csname varchar2(50);
  csaccessor varchar2(50);

  cursor cObjects is
  select object_type, object_name
  from all_objects
  where owner = 'MYUSER'
  and object_type in ('TABLE', 'VIEW')
  order by 1, 2;

  cursor cColumns(oname in varchar2) is
  select tc.column_name, tc.data_type, tc.char_length, tc.data_length,
    tc.data_precision, tc.data_scale, tc.nullable,
    p.table_name
  from all_tab_columns tc
  left outer join all_cons_columns cc
    inner join all_constraints c on c.owner = cc.owner 
      and c.constraint_name = cc.constraint_name 
      and c.constraint_type = 'R'
    inner join all_constraints p on c.r_owner = p.owner 
      and c.r_constraint_name = p.constraint_name
  on tc.owner = cc.owner and tc.table_name = cc.table_name 
    and tc.column_name = cc.column_name
  where tc.owner = 'MYUSER'
  and tc.table_name = oname
  order by tc.column_id;

Next, we loop through all tables and views (outer cursor):

begin
  dbms_output.enable(1000000);

  for obj in cObjects loop
    dbms_output.put_line('public partial class ' || 
      replace(initcap(obj.object_name), '_', ''));
    dbms_output.put_line('{');

    for col in cColumns(obj.object_name) loop

      csname := replace(initcap(col.column_name), '_', '');
      csdatatype := col.data_type;
      csaccessor := '{ get; set; }';

For the inner loop we use a parameterized cursor. Each column needs to have its data type mapped onto a C# data type:

      case col.data_type
        when 'NVARCHAR2' then
          csdatatype := 'string';
        when 'VARCHAR2' then
          csdatatype := 'string';
        when 'NUMBER' then
          if col.column_name = 'OID' then
            csdatatype := 'long';
            csaccessor := '{ get; private set; }';
          elsif col.column_name like '%\_OID' escape '\' 
              and col.table_name is not null then
            csdatatype := replace(initcap(col.table_name), '_', '');
            csname := replace(initcap(replace(col.column_name, '_OID', '')), '_', '');
          elsif 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('    public virtual ' || 
        csdatatype || ' ' || csname || ' ' || csaccessor);

    end loop;

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

end;

Restrictions:

• The example only shows string and int data type conversion.
• Primary key columns are always named OID, and foreign key columns end in _OID.
• Table and column names are generated calling INITCAP() and then removing underscores.