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
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 PLSQLAssocia开发者_如何学CtiveArray
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();
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;
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
精彩评论