How to execute SP with type of Table..

V

venkat.oar

Guru's,

I have oracle sp with the below types defined, and my sp uses these
types for outparameter, i am using DAAB.

Need to know how to execute this kind of SP.

highly appreciate if you can get me solution for this..
Thanks in advance..
-------
TYPE SECTTABTYPE IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
TYPE MCHTABTYPE IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER;
TYPE NUMTABTYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
------------
PROCEDURE GET_CAP( P_SECTIONS IN VARCHAR2,
P_NUM_DAYS IN NUMBER,
P_PROC_ALL_MCHS IN VARCHAR2,
P_SECTS OUT SECTTABTYPE,
P_MCHS OUT MCHTABTYPE,
P_RQDS OUT NUMTABTYPE,
P_PLANCAPS OUT NUMTABTYPE) IS
--some code

********
// .Net code for executing SP using DAAB..
DBCommandWrapper dbCommandWrapper =
db.GetStoredProcCommandWrapper("dcp_PKG.get_cap");
dbCommandWrapper.AddInParameter("P_SECTIONS", DbType.String, section);
dbCommandWrapper.AddInParameter("P_NUM_DAYS", DbType.Int32,NumDays);
dbCommandWrapper.AddInParameter("P_PROC_ALL_MCHS", DbType.String, Mac);
dbCommandWrapper.AddOutParameter("P_SECTS", DbType.String,20);
dbCommandWrapper.AddOutParameter("P_MCHS", DbType.String,24);
dbCommandWrapper.AddOutParameter("P_RQDS", DbType.Int32,4);
dbCommandWrapper.AddOutParameter("P_PLANCAPS", DbType.Int32,4 );
//Execute the stored procedure
db.ExecuteDataSet(dbCommandWrapper);
 
F

Frans Bouma [C# MVP]

Guru's,

I have oracle sp with the below types defined, and my sp uses these
types for outparameter, i am using DAAB.

Need to know how to execute this kind of SP.

highly appreciate if you can get me solution for this..
Thanks in advance..
-------
TYPE SECTTABTYPE IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER;
TYPE MCHTABTYPE IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER;
TYPE NUMTABTYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
------------
PROCEDURE GET_CAP( P_SECTIONS IN VARCHAR2,
P_NUM_DAYS IN NUMBER,
P_PROC_ALL_MCHS IN VARCHAR2,
P_SECTS OUT SECTTABTYPE,
P_MCHS OUT MCHTABTYPE,
P_RQDS OUT NUMTABTYPE,
P_PLANCAPS OUT NUMTABTYPE) IS
--some code

********
// .Net code for executing SP using DAAB..
DBCommandWrapper dbCommandWrapper =
db.GetStoredProcCommandWrapper("dcp_PKG.get_cap");
dbCommandWrapper.AddInParameter("P_SECTIONS", DbType.String, section);
dbCommandWrapper.AddInParameter("P_NUM_DAYS", DbType.Int32,NumDays);
dbCommandWrapper.AddInParameter("P_PROC_ALL_MCHS", DbType.String,
Mac); dbCommandWrapper.AddOutParameter("P_SECTS", DbType.String,20);
dbCommandWrapper.AddOutParameter("P_MCHS", DbType.String,24);
dbCommandWrapper.AddOutParameter("P_RQDS", DbType.Int32,4);
dbCommandWrapper.AddOutParameter("P_PLANCAPS", DbType.Int32,4 );
//Execute the stored procedure
db.ExecuteDataSet(dbCommandWrapper);
Click to expand...

It's my understanding you can't use procs with UDT's and TABLE typed
parameters in .NET, but it's been a while since I looked at that
particular part of the docs of Oracle's providers.

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Top