Cannot Execute Oracle Package.Function from C#

Question

I am trying to use Oracle.DataAccess to connect to the Oracle database from Visual Studio 2012 (c#) and run FUNCTION which is part of a PACKAGE. below is the SQL:

FUNCTION TEST (test_name in varchar2,
   test_year in number,
   test_outmessage out varchar2)
RETURN NUMBER IS
   test_exists     VARCHAR2(1) := 'N';
BEGIN
   BEGIN
      SELECT 'Y' INTO test_exists
        FROM Test_Table
       WHERE table_name = test_name
         AND table_year = test_year;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         test_exists := 'N';
      WHEN OTHERS THEN
         test_outmessage := 'error'
         DBMS_OUTPUT.put_line (test_outmessage);
         RETURN -1;
   END;

   IF test_exists = 'Y' THEN
      RETURN 1;
   ELSE
      RETURN 0;
   END IF;

END TEST;

I have written c# code to execute this function from my app:

 using (OracleConnection connection = new OracleConnection("Data Source=Database; User ID=user; Password=pass"))
{
  OracleCommand command = connection.CreateCommand();
  command.CommandType = System.Data.CommandType.StoredProcedure;
  command.CommandText = "USER.PACKAGE.TEST";


   OracleParameter test_name = new OracleParameter();
   test_name.ParameterName = "test_name";
   test_name.OracleDbType = OracleDbType.NVarchar2;
   test_name.Direction = System.Data.ParameterDirection.Input;
   test_name.Value = "myname";
   command.Parameters.Add(test_name);

   OracleParameter test_year = new OracleParameter();
   test_year.ParameterName = "test_year";
   test_year.OracleDbType = OracleDbType.Decimal;
   test_year.Direction = System.Data.ParameterDirection.Input;
   test_year.Value = 2004;
   command.Parameters.Add(test_year);

   OracleParameter test_outmessage = new OracleParameter();
   test_outmessage.ParameterName = "test_outmessage ";
   test_outmessage.OracleDbType = OracleDbType.NVarchar2;
   test_outmessage.Direction = System.Data.ParameterDirection.Output;
   command.Parameters.Add(test_outmessage);

   OracleParameter result = new OracleParameter();
   result.ParameterName = "ReturnValue";
   result.OracleDbType = OracleDbType.Decimal;
   result.Direction = System.Data.ParameterDirection.ReturnValue;
   command.Parameters.Add(result);
   try
   {
         connection.Open();

         command.ExecuteNonQuery();
         string r = result.Value.ToString();
         string r1 = test_outmessage.Value.ToString();
   }
   catch (Exception)
   {

   }
   finally
   {
          connection.Close();

    }
 }

There is no exception when I run this code, however, the return value (Both r and r1) is null. As in the function, I should expect -1, 1 or 0 for string r I guess.

Kin · Accepted Answer · 2013-01-15 23:42:15Z

1

Found the answer. Oracle cares about the sequence in which the parameter is added, Return Value should be the first parameters added to the OracleCommand.

answered Jan 15, 2013 at 23:42

Kin

711 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cannot Execute Oracle Package.Function from C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related