locked
what is the C# code to read TYPES.cursortypes in procedure RRS feed

  • Question

  • User1494431305 posted

    I have a stored procedure like this

            PROCEDURE PRC_ABCD_GETALL (resultset_out OUT TYPES.cursorType)

    AS BEGIN OPEN resultset_out FOR SELECT * FROM ABCD; END PRC_ABCD_GETALL;

    And my C# code is like this

    using (OracleConnection conn = new OracleConnection(cnn))
    {
    	conn.Open();
    	OracleCommand cmd = new OracleCommand();
    	cmd.Connection = conn;
    	cmd.CommandText = "PRC_ABCD_GETALL";
    	cmd.CommandType = CommandType.StoredProcedure;
    	cmd.Parameters.Add("resultset_out", OracleDbType.Cursor, ParameterDirection.Output);
    	OracleDataReader rdr = cmd.ExecuteReader();
    	while (rdr.Read())
    	{
    		result.Add(Construct(rdr));
    	}
    }

    but I'm getting error 'OracleDbType' does not contain a definition for 'Cursor' in OracleType.Cursor. Any solution for this??</div>

    Tuesday, January 2, 2018 8:38 AM

All replies

  • User269602965 posted

    Oracle .NET documentation encourages use of REFCURSOR data type for the OUT.

    Example:

    /* 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};
    /
    
    /* 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) 
    {}
    
    

    Wednesday, January 3, 2018 2:49 AM