locked
calling Store Procedure RRS feed

  • Question

  • User-541003552 posted

    Hi All,

    Please how can i call a store procedure from an oracle database using the below parameters.

    Any sample code in c# would be much appreciated.

    Thanks

    Argument Name    Type         In/Out     Default?
    ------------------------------ ----------------------- ------ - -------
    CusId         VARCHAR2      IN
    VOUT         REF CURSOR     OUT
    Result                        OUT
    Cus_NO       VARCHAR2(6)    OUT
    SPR             VARCHAR2(7)    OUT

    Friday, August 21, 2015 10:55 AM

All replies

  • User269602965 posted
    /* VB.NET code in ASP.NET application */
    
    ' Get data from stored procedure '
    Try
    	Dim connstr As String = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString
    	Using conn As New OracleConnection(connstr)
    		Using cmd As New OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)
    			cmd.CommandType = CommandType.StoredProcedure
    			cmd.Parameters.Clear()
    			cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input)
    			cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input)
    			cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input)
    			cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output)
    			conn.Open()
    			Using oda As New OracleDataAdapter(cmd)
    				Dim ds As New DataSet()
    				oda.Fill(ds)
    				Me.RadGrid1.MasterTableView.DataSource = ds.Tables(0)
    			End Using
    		End Using
    	End Using
    Catch ex As Exception
    End Try
    
    /* CS.NET code in ASP.NET application */
    
    ' Get data from stored procedure '
    try 
      {
    	string connstr = ConfigurationManager.ConnectionStrings("{OracleConnectionStringNameFromWebConfig}").ConnectionString;
    	using (OracleConnection conn = new OracleConnection(connstr)) 
    	  {
    		using (OracleCommand cmd = new OracleCommand("{SCHEMANAME}.{PACKAGENAME}.rcGET_BIRD_DATA", conn)) 
    		  {
    			cmd.CommandType = CommandType.StoredProcedure;
    			cmd.Parameters.Clear();
    			cmd.Parameters.Add("p_DATE_BAND", OracleDbType.Date, ParameterDirection.Input);
    			cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, ParameterDirection.Input);
    			cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, ParameterDirection.Input);
    			cmd.Parameters.Add("ret_REFCUR", OracleDbType.RefCursor, ParameterDirection.Output);
    			conn.Open();
    			using (OracleDataAdapter oda = new OracleDataAdapter(cmd)) 
    			  {
    				DataSet ds = new DataSet();
    				oda.Fill(ds);
    				this.RadGrid1.MasterTableView.DataSource = ds.Tables[0];
    		  	}
    		  }
    	  }
      } 
    catch (Exception ex) 
    {}
    
    
    /* Oracle PL/SQL code */
    
    CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME}
    AS
    PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                             ,p_AOU_CODE    IN  NUMBER
                             ,p_BIRD_CLASS  IN  VARCHAR2
                             ,ret_REFCUR    OUT SYS_REFCURSOR);
    END PKG_BIRDS;
    /
    
    CREATE OR REPLACE PACKAGE BODY {SCHEMANAME}.{PACKAGENAME}
    AS
    PROCEDURE rcGET_BIRD_DATA(p_DATE_BAND   IN  DATE
                             ,p_AOU_CODE    IN  NUMBER
                             ,p_BIRD_CLASS  IN  VARCHAR2
                             ,ret_REFCUR    OUT SYS_REFCURSOR)
    BEGIN
    	OPEN ret_REFCUR FOR
    		SELECT
    			AOU_CODE,
    			BIRD_CLASS,
    			BIRD_NAME,
    			DATE_BAND,
    			WING_LENGTH,
    			TAIL_LENGTH,
    			EYE_COLOR,
    			PLUMAGE_CODE
    		FROM
    			{SCHEMANAME}BANDED_BIRDS
    		WHERE
    			DATE_BAND  > p_DATE_BAND
    			AND
    			AOU_CODE   = p_AOU_CODE
    			AND
    			BIRD_CLASS = p_BIRD_CLASS
    END;
    
    END {SCHEMANAME}.{PACKAGENAME};
    /
    
    

    Friday, August 21, 2015 5:19 PM
  • User61956409 posted

    Hi KeeEdwins,

    We could call Oracle stored procedures and functions from Microsoft.NET through the Microsoft.NET Oracle provider, the following articles will be helpful.

    Best Regards,

    Fei Han

    Wednesday, August 26, 2015 1:16 AM