In SQL Server 2008 I am able to pass table-valued parameter to my stored procedure from NHibernate.How to achieve the same in Oracle

Question

I have created a table as a type in SQL Server 2008.

As SQL Server 2008 supports passing table value parameter as IN parameter to stored procedure. It is working fine.

Now I have to perform the same approach in Oracle.

I did it through PLSQLAssociativeArray but the limitaion of Associative array is they are homogeneous (every element must be of the same type).

Where as in case of table-valued parameter of SQL Server 2008, it is possible.

How to achieve the same in Oracle.?

Following are my type and stored procedure in SQL Server 2008:

CREATE TYPE [dbo].[EmployeeType] AS TABLE(  
    [EmployeeID] [int] NULL,  
    [EmployeeName] [nvarchar](50) NULL  
)  
GO


CREATE PROCEDURE [dbo].[TestCustom] @location EmployeeType READONLY  
AS  
insert into Employee (EMP_ID,EMP_NAME)   
SELECT EmployeeID,EmployeeName  
FROM @location;

GO

Call from NHibernate

   var dt = new DataTable();  
   dt.Columns.Add("EmployeeID", typeof(int));  
   dt.Columns.Add("EmployeeName", typeof(string));  
   dt.Rows.Add(new object[] { 255066, "Nachi11" });  
   dt.Rows.Add(new object[] { 255067, "Nachi12" });                 
   ISQLQuery final = eventhistorysession.CreateSQLQuery("Call TestCustom @pLocation = :id");  
   IQuery result = final.SetStructured("id", dt);  
   IList finalResult = result.List();

Answer 1

As I understand, it is not possible to use Oracle object table parameters (see @Quassnoi's answer for an example) using either nHibernate or ODP.NET. The only collection type supported by ODP.NET is PLSQLAssociativeArray.

However, one could easily achieve the same result as with SQL Server TVPs using associative arrays. The trick is to define an array for each parameter instead of a single one for the whole table.

I'm posting a complete proof-of-concept solution as I haven't been able to find one.

Oracle Schema

The schema includes a table and a packaged insert procedure. It treats each parameter as a column and assumes each array is at least as long as the first one.

create table test_table
(
    foo number(9),
    bar nvarchar2(64)
);
/

create or replace package test_package as
    type number_array is table of number(9) index by pls_integer;
    type nvarchar2_array is table of nvarchar2(64) index by pls_integer;

    procedure test_proc(p_foo number_array, p_bar nvarchar2_array);
end test_package;
/ 

create or replace package body test_package as

    procedure test_proc(p_foo number_array, p_bar nvarchar2_array) as
    begin
        forall i in p_foo.first .. p_foo.last
            insert into test_table values (p_foo(i), p_bar(i));
    end;
end test_package; 
/

nHibernate Mapping

<sql-query name="test_proc">
  begin test_package.test_proc(:foo, :bar); end;
</sql-query>

nHibernate Custom IType

I've borrowed the concept from a great SQL Server related answer and modified the class slightly to work with ODP.NET. As IType is huge, I only show the implemented methods; the rest throws NotImplementedException.

If anyone wants to use this in production code, please be aware that I've not tested this class extensively even if it does what I immediately need.

public class OracleArrayType<T> : IType
{
    private readonly OracleDbType _dbType;

    public OracleArrayType(OracleDbType dbType)
    {
        _dbType = dbType;
    }

    public SqlType[] SqlTypes(IMapping mapping)
    {
        return new []{ new SqlType(DbType.Object) };
    }

    public bool IsCollectionType
    {
        get { return true; }
    }

    public int GetColumnSpan(IMapping mapping)
    {
        return 1;
    }

    public void NullSafeSet(IDbCommand st, object value, int index, ISessionImplementor session)
    {
        var s = st as OracleCommand;
        var v = value as T[];
        if (s != null && v != null)
        {
            s.Parameters[index].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
            s.Parameters[index].OracleDbType = _dbType;
            s.Parameters[index].Value = value;
            s.Parameters[index].Size = v.Length;
        }
        else
        {
            throw new NotImplementedException();
        }
    }

    // IType boiler-plate implementation follows.

The constructor parameter specifies the type of the base array type (i.e. if you passing an array of strings, pass OracleDbType.NVarchar2. There probably is a way to deduce the DB type from the value type, but I'm not sure yet how to do that.

Extension Method for IQuery

This wraps the type creation:

public static class OracleExtensions
{
    public static IQuery SetArray<T>(this IQuery query, string name, OracleDbType dbType, T[] value)
    {
        return query.SetParameter(name, value, new OracleArrayType<T>(dbType));
    }
}

Usage

To tie all this together, this is how the class is used:

using (var sessionFactory = new Configuration().Configure().BuildSessionFactory())
using (var session = sessionFactory.OpenSession())
{
    session
        .GetNamedQuery("test_proc")
        .SetArray("foo", OracleDbType.Int32, new[] { 11, 21 })
        .SetArray("bar", OracleDbType.NVarchar2, new [] { "bar0", "bar1" })
        .ExecuteUpdate();
}

The result of select * from test_table after running the code:

FOO   BAR
----------------
11    bar0
21    bar1

Answer 2

CREATE OR REPLACE TYPE employeeType AS OBJECT (employeeId INT, employeeName VARCHAR2(50));

CREATE TYPE ttEmployeeType AS TABLE OF employeeType;

CREATE PROCEDURE testCustom (pLocation ttEmployeeType)
AS
BEGIN
        INSERT
        INTO    employee (emp_id, emp_name)
        SELECT  *
        FROM    TABLE(pLocation);
END;