locked
Wrong number of arguments error while calling Oracle stored procedure from ASP.NET RRS feed

  • Question

  • User-163012697 posted

    Hi,

     I use to get the data in Oracle Sql Developer , but when I am executing the procedure from ASP.NET application it throws error as Wrong Number of arguments.

    ASP.NET C#:

    public DataTable Execute2DataTableoraclesp()
        {
            DataTable dt = new DataTable();
            OracleConnection conn = null;
            try
            {
    
                conn = new OracleConnection(oradb);
    
                    conn.Open();
               
                OracleCommand cmd = new OracleCommand();
                OracleDataAdapter da = new OracleDataAdapter("sp_procedure", conn);
                cmd.Connection = conn;
                cmd.CommandText = argQuery;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("p_recordset", OracleType.NVarChar,2000).Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();
    
                da.Fill(dt);
                return dt;
            }
    
            catch (Exception ex)
            {
                return dt;
    
            }
            finally
            {
                conn.Close();
            }

    Procedure In Oracle:

    create or replace PROCEDURE sp_procedure(p_recordset OUT SYS_REFCURSOR)
    AS 
    BEGIN 
      OPEN p_recordset FOR
        select count(*) as totalcalls,to_date(max(datetime1)) as STARTDATE, to_date(min(DATETIME1)) as  CURRENTDATE  from tablename;
    END sp_procedure;

    Saturday, January 30, 2016 12:33 PM

All replies

  • User269602965 posted

    This code works for me.

    Clear your parameters

    Drop the Command Text

    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}.rcSelectCountContracts", conn)
              cmd.CommandType = CommandType.StoredProcedure
              cmd.Parameters.Clear()
              cmd.Parameters.Add("CountContracts", 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
    
    Oracle PL/SQL code
        
    CREATE OR REPLACE PACKAGE {SCHEMANAME}.{PACKAGENAME} AS TYPE refCursor IS REF CURSOR;
      PROCEDURE rcSelectCountContracts(CountContracts OUT refCursor);
    END {PACKAGENAME};
    /
    
    CREATE OR REPLACE PACKAGE BODY {PACKAGENAME} AS
    
    PROCEDURE rcSelectCountContracts(CountContracts OUT refCursor)
    IS
    BEGIN
      OPEN CountContracts FOR
        SELECT PRODUCT_CLASS, AMOUNT_ANNUAL_CONTRACT, COUNT, TOTAL_VALUE FROM {SCHEMANAME}.VW_COUNT_CONTRACTS;
    END;
    
    END {PACKAGENAME};
    /

    Sunday, January 31, 2016 7:10 PM
  • User269602965 posted

    ANother example: mix IN and OUT parms

    But same concept CLEAR parameters

    Enclose in USING statements

    Get rid of the COMMAND TEXT line

    And you are returning a REF CURSOR, then do in the Parameter OUTPUT directive.

    ' 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};
    /
    
    

    Sunday, January 31, 2016 7:17 PM
  • User269602965 posted

    And forgot to mention another tip.

    Oracle is picky about the ORDER of parameters.  The order of the parameters in .NET side have to match the order of parameters on the PL/SQL side.

    Sunday, January 31, 2016 7:18 PM