locked
Query works in Oracle developer but doesn't work in Visual Studio RRS feed

  • Question

  • User-924093023 posted

    I have a query in VS that works fine and returns records when I query in Oracle developer, but returns an empty dataset in VS.

    Is something wrong in the query.

    TIA

    Tuesday, October 13, 2020 7:51 PM

All replies

  • User-189459990 posted

    I created a project and tested "with...as...select statement" with my own database. And everything works fine.

    So, how did you return the DataSet? Via OracleDataAdapter or System.Data.OracleClient?<o:p></o:p>

    To handle this issue, it would be better if you can provide the related code about how you execute the query and return DataSet.<o:p></o:p>

    Wednesday, October 14, 2020 6:13 AM
  • User269602965 posted

    Since the System.Oracle.DataClient has been deprecated, it is better to use Oracle data adapter to populate a dataset.

    THis example returns a REF CURSOR via Oracle data adapter to populate a .NET dataset.

    /* VB.NET code in ASP.NET application */
    
    Sub PopulateTable (dateBand as Date, decAOUCode as Decimal, strBirdClass as String) 
    	' 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, dateBand, ParameterDirection.Input)
    				cmd.Parameters.Add("p_AOU_CODE", OracleDbType.Decimal, decAOUCode, ParameterDirection.Input)
    				cmd.Parameters.Add("p_BIRD_CLASS", OracleDbType.Varchar2, strBirdClass, 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
    End Sub
    
    /* 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};
    /
    
    

    Thursday, October 22, 2020 12:03 AM