CODEWITHSUNDEEP
oracle-databasenhibernatestored-proceduresparameterssql-insert
vladiastudillo
vladiastudillo

Reputation: 407

NHibernate 3.3: <sql-insert> executing an Oracle 11g stored procedure

I'm mapping an Oracle 11g stored procedure as command in a NHibernate's .hbm.xml mapping file, like this:

  <class name="Person" table="PERSONS">
    <id name="Id" column="COD_PERSON" />
    <property name="Name" column="NAME" />

    <property name="AuditField1" column="AUDITFIELD1" />
    <property name="AuditField2" column="AUDITFIELD2" />
    <property name="AuditField3" column="AUDITFIELD3" />

    <sql-insert>exec PKG_PERSONS.insert_sp ?,?</sql-insert>
  </class>

This is the stored procedure:

create or replace package body PKG_PERSONS is
  procedure insert_sp(pcod_person  persons.cod_person%type, 
                      pname        persons.name%type) is
  begin
    insert into persons(cod_person, name) values(pcod_person, pname);
  end;

From this mapping, I'm expecting 'Id' and 'Name' properties would be send as parameters, but for sure, this isn't happening; I'm getting this error from Oracle: ORA-01036: illegal variable name/number. NHibernate shows some logs in Console window and it seems like NH tries to map all properties, included AuditFields, to invoke the stored procedure.

Maybe is this the source's error?

Is this the expected behaviour?

Is it possible to specific what properties send as parameter with NH3/Oracle?

Thanks in advance.

Upvotes: 1

Views: 1218

Answers (1)

vladiastudillo
vladiastudillo

Reputation: 407

Confirmed, NH tries to send as parameter every entity's properties mapped to <sql-insert>/<sql-update> stored procedures, but you could make exceptions with insert/update=false.

Another issue was the right sintaxis to execute Oracle11g's stored procedure, this is the mapping that works for me with NH3.3 and Oracle.DataAccess 4.112.2.0:

  <class name="Person" table="PERSONS">
    <id name="Id" column="COD_PERSON" />
    <property name="Name" column="NAME" />

    <property name="AuditField1" column="AUDITFIELD1" insert="false" update="false" />
    <property name="AuditField2" column="AUDITFIELD2" insert="false" update="false" />
    <property name="AuditField3" column="AUDITFIELD3" insert="false" update="false" />

    <sql-insert check="none">begin PKG_PERSONS.insert_sp(:p0,:p1); end;</sql-insert>
  </class>

I had to redefine the parameter's order, because Id property is send at last position:

create or replace package body PKG_PERSONS is
  procedure insert_sp(pname        persons.name%type,
                      pcod_person  persons.cod_person%type
                      ) is
  begin
    insert into persons(cod_person, name) values(pcod_person, pname);
  end;

Maybe this will help somebody else, Thanks.

Upvotes: 1

Related Questions