Calling oracle function RSS

• • Reply
  

  rajkm42

  Member

  6 Points

  84 Posts

  Calling oracle function

  Apr 30, 2012 02:27 PM|LINK

  Hi

  I have a Oracle function like this.

  FUNCTION getengrep(     end_date DATE   ) RETURN SYS_REFCURSOR;

  this is in a package called "get_engRep_Pkg"

  I want to call this function and loop through all rows in the resultset.

  My code as below but giving error..."wrong number or types of arguments in call to 'GETENGREP'

  Code:

  using(OracleConnection conn = new OracleConnection("Data Source=ds; User ID=uid; Password=pwd"))
  {
      cmd = new OracleCommand();
      cmd.Connection = conn;
      cmd.CommandText = "test.get_engrep_pkg.getengrep";
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Add("cur_res", OracleType.Cursor).Direction = ParameterDirection.Output;
      cmd.Parameters.Add("end_date", OracleType.DateTime).Direction = ParameterDirection.Input;
      cmd.Parameters["end_date"].Value = "01-Mar-12";
      conn.Open();
      OracleDataReader rdr = cmd.ExecuteReader();
      Show(rdr);
  }
  
  }

  
  

  Could you suggest where I am doing wrong.

  thanks

  • Edited by XIII on Apr 30, 2012 02:52 PM
  • Moved by XIII on Apr 30, 2012 02:52 PM
• • Reply
  

  markfitzme

  Star

  14433 Points

  2227 Posts

  Re: Calling oracle function

  Apr 30, 2012 02:33 PM|LINK

  I'm not very fluent in Oracle, but the function takes a Date and the parameter sets it for a DateTime. Many modern RDBMSes such as Oracle and SQL Server have both a DateTime, and then seperate Date and Time datatypes. If you pass a DateTime to a function expecting a Date, your are passing incorrect information as the function is only expecting a date part, and not a more complex datetime.

  Don't forget to mark useful responses as Answer if they helped you towards a solution.
• • Reply
  

  rajkm42

  Member

  6 Points

  84 Posts

  Re: Calling oracle function

  Apr 30, 2012 02:48 PM|LINK

  Hi

  Thanks for the reply. I tried with '01-Apr-12 00:00:00' but the same error.

  If I run query below on oracle sql developer, it executes well.

  select test.get_engrep_pkg.getengrep('01-JAN-12') from dual;

  But I can't execute the above statements directly as it returns ref_cursor

  Any suggestions?

   

• • Reply
  

  Lannie

  Contributor

  3738 Points

  728 Posts

  Re: Calling oracle function

  May 01, 2012 01:19 AM|LINK

  cmd.Parameters["end_date"].Value = "01-Mar-12";

  Before you call Oracle

  Dimension a datetime variable and convert your string 01-Mar-2012 to a datetime

  then pass the variable into Oracle.

• • Reply
  

  Prashant Kum...

  Star

  12344 Points

  1992 Posts

  Re: Calling oracle function

  May 01, 2012 04:58 AM|LINK

  Change cmd.Parameters.Add("cur_res", OracleType.Cursor).Direction = ParameterDirection.Output;

  to this

  cmd.Parameters.Add("cur_res", OracleType.Cursor).Direction = ParameterDirection.ReturnValue;

  
  

  Prashant
  • Marked as answer by Ming Xu - MSFT on May 08, 2012 12:22 PM