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

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;

Answer 1

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

Answer 2

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

Answer 3

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.