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
  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,
  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):


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

    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';
            csdatatype := 'long';
          end if;

          csdatatype := col.data_type;

      end case;

      dbms_output.put_line('    public virtual ' || 
        csdatatype || ' ' || csname || ' ' || csaccessor);

    end loop;

  end loop;



  • 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.

4 thoughts on “Generating NHibernate Class in PL/SQL

  1. Pingback: Generating NHibernate ClassMap in PL/SQL « devioblog

  2. Pingback: informatica question? « El Notas – Tu Portal Web!

  3. Pingback: Generating NHibernate Classes for MSSQL in TSQL « 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.