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

  • 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.... 

    Monday, September 9, 2013 12:34 PM

Answers

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 9, 2013 1:05 PM
  • 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...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 9, 2013 1:11 PM

All replies

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 9, 2013 1:05 PM
  • 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...

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 9, 2013 1:11 PM
  • User-327401599 posted

    Thanks a lot for your quick response. 

    It worked...

    Monday, September 9, 2013 1:45 PM
  • User-327401599 posted

    Thanks a lot.... :-)

    Monday, September 9, 2013 1:45 PM