Does NHibernate Supports Passing Collections to Oracle Procedures?

[Juan Del Arco]

Hi everyone,

I need to know if NHibernate supports passing a collection (List<string> or string[]) to a stored procedure, or if there are any alternative ways to do it.

Thank you

Here is what I have been trying:

Oracle Package

CREATE OR REPLACE PACKAGE THE_PACKAGE AS

    TYPE STRING_ARRAY IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

    PROCEDURE PROCESS_SELECTED_RECORDS(p_IDS_ARRAY  IN STRING_ARRAY);

END THE_PACKAGE;

CREATE OR REPLACE PACKAGE BODY THE_PACKAGE AS

    PROCEDURE PROCESS_SELECTED_RECORDS(p_IDS_ARRAY IN STRING_ARRAY) IS

    BEGIN

            FOR i IN 1 .. p_IDS_ARRAY.COUNT LOOP

                -- DO THINGS

            END LOOP;

    END;

END THE_PACKAGE;

XML NHibernate Mapping

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">

  <sql-query name="THE_PACKAGE.PROCESS_SELECTED_RECORDS">

    CALL THE_PACKAGE.PROCESS_SELECTED_RECORDS(:p_IDS_ARRAY)

  </sql-query>

</hibernate-mapping>

C# Procedure call

var query = session.GetNamedQuery("THE_PACKAGE.PROCESS_SELECTED_RECORDS");

query.SetParameter("p_IDS_ARRAY ", selectedIds);

query.ExecuteUpdate();

[Gökhan Abatay]

Hi I have the an issue with dml batching then I wrote an implementation that supports batching with hql, linq and native sql I think you could use the same implementation.

var batch = ISession.CreateQueryBatchDml();

foreach (var item in new string[] { "1311", "1328" })
{
    var query = ISession.CreateSQLQuery($"UPDATE DBO.STMT_ACCOUNT_STAT set FIRST_DELAY_DATE = :FirstDelayDate where ACCOUNT_NO = :AccountNo");
    var date = item == "1311" ? System.DateTime.Now : System.DateTime.Now.AddDays(1);
    query.SetParameter("FirstDelayDate", date);
    query.SetParameter("AccountNo", item);
    batch.Add(query);
}
batch.Execute();

this sends FirstDelayDate and AccountNo parameters to query as array, if batching is supported by your database provider.

https://github.com/nhibernate/nhibernate-core/issues/2217#issuecomment-533374519

I hope it helps

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nhusers+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/0e20518d-acd4-4e26-80dd-6a65612b075b%40googlegroups.com.

--

Gökhan Abatay

P: 05542728777

P: 05313734616
Senior Software Architect