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
Pingback: Generating a Stored Procedure Wrapper for NHibernate in PL/SQL « devioblog