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

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;