Please help in ORACLE SP and .NET Code to call the SP

Question

User-327401599 posted

Hi Friends,

Please help me. This is my stored procedure

create or replace
PROCEDURE GET_SELECT1
(
p_recordset1 OUT Types.cursor_type) AS

BEGIN
OPEN p_recordset1 FOR
SELECT * FROM TBL_SAMPLE WHERE Value= '72' and ceil(rownum/10)=1 ;

END;

How do i call this Stored procedure in C# code

WIth out any oracle parameter i'm calling this SP. is this correct?

dt = Orahelp.ExecuteNonQuery_SP("GET_SELECT1");
return dt;

Please do help me in fixing this issue.... 

Answer 1

User1508394307 posted

What is orahelp? 

Usually you can use the Microsoft.NET Oracle provider and its object model residing in the namespace System.Data.OracleClient. See here complete example on how to call sp using OracleClient.

Answer 2

User551462331 posted

Orahelp.ExecuteNonQuery_SP

this is clearly a custom function that you have created... so, it is difficut to say how u r actually calling the oracle procedure...

ideally, you should use OracleCommand to pass the procedure name and one parameter... like this

DataSet ds = new DataSet();

OracleCommand command = New OracleCommand(con);
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "PackageName.GET_SELECT1";
command.Parameters.Add("p_recordset1", OracleType.Cursor)
command.Direction = ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(command);
da.Fill(ds);

hope this helps...

Answer 3

User-327401599 posted

Thanks a lot for your quick response. 

It worked...

Answer 4

User-327401599 posted

Thanks a lot.... :-)