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?
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.
Wouldn't INSERT INTO employee (emp_id, emp_name) VALUES (pLocation. EmployeeId, plocation. EmployeeName) work as well?
– a_horse_with_no_name May 3 at 10:52 @ahorse: the original stored procedure used a table type in which you could pass several records at once. – Quassnoi May 3 at 10:56 @Quassnoi:I have updated my question with Call from NAs this given code worked fine with MS SQL Server2008. Can you check will it work with Oracle.
– anupam May 3 at 11:07 @anupam: sorry, I'm not familiar with N– Quassnoi May 3 at 11:09 @Quassnoi:Then I feel ,we should not mark the above answer as correct . As this only answers it partially.As per the question NIt's bit misleading for the users. Is it possible for you to suggest some way to make it work with ADO.
Net – anupam May 3 at 11:17.
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. 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. 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. The constructor parameter specifies the type of the base array type (i.e. If you passing an array of strings, pass OracleDbType.
There probably is a way to deduce the DB type from the value type, but I'm not sure yet how to do that.
I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.
Related Questions
Execute sybase stored procedure as linked server procedure sql server 2008?
Efficiently filter data in a table-valued parameter in a stored procedure?
How to call a stored procedure from another stored procedure in oracle?
NHibernate to access Oracle stored procedure REFCURSOR and output parameter?
Create table that invokes a stored procedure in SQL Server 2008?
Unable to migrate stored procedure from SQL Server 2008 to SQL Azure?