8 Replies Latest reply: Sep 10, 2010 1:44 PM by jareeq RSS

    Execute oracle stored procedure from C# always returns null

    691768
      Hi,
      I'm trying to execute a stored procedure on oracle 9i. I'm using .Net OracleClient provider.
      Apparently, I can execute the stored procedure, but it always returns null as a result (actually all the sp's I have there returns null)! I can execute any text statement against the database successfully, and also I can execute the stored procedure using Toad.
      This is not the first time for me to call an oracle stored procedure, but this really is giving me a hard time! Can anyone help please?

      Below are the SP, and the code used from .Net to call it, if that can help.

      Oracle SP:

      CREATE OR REPLACE PROCEDURE APIECARE.CHECK_EXISTENCE(l_number IN NUMBER) AS
      v_status VARCHAR2(5) := NULL;
      BEGIN
      BEGIN
      SELECT CHECK_NO_EXISTENCE(to_char(l_number))
      INTO v_status
      FROM DUAL;
      EXCEPTION WHEN OTHERS THEN
      v_status := NULL;
      END;
      DBMS_OUTPUT.PUT_LINE(v_status);
      END CHECK_CONTRNO_EXISTENCE;
      /


      C# Code:
      string connStr = "Data Source=datasource;Persist Security Info=True;User ID=user;Password=pass;Unicode=True";
      OracleConnection conn = new OracleConnection(connStr);

      OracleParameter param1 = new OracleParameter();
      param1.ParameterName = "v_status";
      param1.OracleType = OracleType.VarChar;
      param1.Size = 5;
      param1.Direction = ParameterDirection.Input;

      OracleParameter param2 = new OracleParameter();
      param2.ParameterName = "l_number";
      param2.OracleType = OracleType.Number;
      param2.Direction = ParameterDirection.Input;
      param2.Value = 006550249;

      OracleParameter[] oraParams = new OracleParameter[] { param1, param2 };
      OracleCommand cmd = new OracleCommand("CHECK_EXISTENCE", conn);
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddRange(oraParams);
      conn.Open();
      object result = cmd.ExecuteScalar();
      conn.Close();
        • 1. Re: Execute oracle stored procedure from C# always returns null
          gdarling - oracle
          Hi,

          Does that actually execute? You're passing two parameters to a procedure that only takews 1 and get no error?
          Your stored procedure doesnt return anything and has no output parameters, what are you expecting to be returned exactly?
          If you're trying to access V_STATUS you'll need to declare that as either an output parameter of the procedure, or return value of the function, and also access it via accessing Param.Value, not as the result of ExecuteScalar.
          See if this helps.

          Cheers,
          Greg
          /*
          create or replace function myfunc(myinvar in varchar2, myoutvar out varchar2) return varchar2
          is
          retval varchar2(50);
          begin
           myoutvar := myinvar; 
           retval := 'the return value';
           return retval;
          end;
          /
          */
          
          using System;
          using System.Data;
          using Oracle.DataAccess.Client;
          
          public class odpfuncparams
          {
                public static void Main()
               {
                OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=orcl");
                con.Open();
                OracleCommand cmd = new OracleCommand("myfunc", con);
                cmd.CommandType = CommandType.StoredProcedure;
          
                
                OracleParameter retval = new OracleParameter("retval",OracleDbType.Varchar2,50);
                retval.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(retval);
          
                OracleParameter inval = new OracleParameter("inval",OracleDbType.Varchar2);
                inval.Direction = ParameterDirection.Input;  
                inval.Value="hello world";
                cmd.Parameters.Add(inval);
                
                OracleParameter outval = new OracleParameter("outval",OracleDbType.Varchar2,50);
                outval.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(outval);
          
                cmd.ExecuteNonQuery();
                Console.WriteLine("return value is {0}, out value is {1}",retval.Value,outval.Value);
                con.Close();
               }
          }
          • 2. Re: Execute oracle stored procedure from C# always returns null
            691768
            Thanks Greg for the reply.
            I don't have experience in Oracle. But I have been given this SP to invoke! Also when calling using the code, no error is returned, it just null.
            CHECK_NO_EXISTENCE(to_char(l_number)) is a function that returns a varchar value, and when executing the SP using say..Toad, I get a result, actually this is what confused me!
            I want it to still be a stored procedure and not a function, but I’ll try what you’ve suggested.

            Thanks,
            Basil
            • 3. Re: Execute oracle stored procedure from C#
              691865
              OracleCommand mycom = new OracleCommand();
              mycom.CommandText = "procedure_name";
              mycom.Connection = con;
              mycom.CommandType = CommandType.StoredProcedure;

              mycom.Parameters.Add("i_pin_number", OracleType.VarChar, 2000).Value = value1;
              mycom.Parameters["i_pin_number"].Direction = ParameterDirection.Input;

              mycom.Parameters.Add("proc_value", OracleType.VarChar, 2000).Value = value2;
              mycom.Parameters["proc_value"].Direction = ParameterDirection.Input;

              mycom.Parameters.Add("proc_value", OracleType.VarChar, 2000).Value = value3;
              mycom.Parameters["proc_value"].Direction = ParameterDirection.Input;

              mycom.Parameters.Add("proc_value", OracleType.VarChar, 2000).Value = value4;
              mycom.Parameters["proc_value"].Direction = ParameterDirection.Input;

              mycom.Parameters.Add("proc_value", OracleType.VarChar, 2000).Value = value5;
              mycom.Parameters["proc_value"].Direction = ParameterDirection.Input;

              mycom.Parameters.Add("outputprocvalue", OracleType.VarChar, 2000).Direction = ParameterDirection.Output;

              mycom.ExecuteNonQuery();
              output = mycom.Parameters["o_result"].Value.ToString();
              return output;
              • 4. Re: Execute oracle stored procedure from C# always returns null
                691768
                I've just figured out the problem. To get the result value of an oracle stored procedure, it first has to be declared as an OUT parameter in the stored procedure signature, and to declare an output parameter for the oracle command in C#. I didn’t know that since I was coming from an MS SQL background!
                So modifying the SP to this will solve the problem.

                CREATE OR REPLACE PROCEDURE APIECARE.CHECK_EXISTENCE(l_number IN NUMBER, l_status OUT VARCHAR2) AS
                v_status VARCHAR2(5) := NULL;
                BEGIN
                BEGIN
                SELECT CHECK_NO_EXISTENCE(to_char(l_number))
                INTO v_status
                FROM DUAL;
                EXCEPTION WHEN OTHERS THEN
                v_status := NULL;
                END;
                L_status := v_status
                DBMS_OUTPUT.PUT_LINE(v_status);
                END CHECK_CONTRNO_EXISTENCE;


                Thank you
                Basil
                • 5. how to Execute oracle function from C#
                  691865
                  How Can i call the following oracle function and get the return type?????? plz help me :( :(

                  thnx in advance


                  CREATE OR REPLACE FUNCTION STIDB.return_req(
                  i_number IN VARCHAR,
                  i_id IN VARCHAR,
                  i_ce IN VARCHAR
                  )
                  RETURN XMLTYPE
                  AS
                  xml XMLTYPE;
                  BEGIN
                  xml :=
                  XMLTYPE
                  ('<?xml version=''1.0'' encoding=''utf-8'' ?> <DATA><REQUEST ID="101"> <NAME>abc</NAME> <REQUESTDATE>02/02/2009</REQUESTDATE><REQUESTTYPE>Enquiry</REQUESTTYPE> </REQUEST></DATA>'
                  );
                  RETURN xml;
                  END;
                  /
                  • 6. Re: how to Execute oracle function from C#
                    gdarling - oracle
                    Just add a parameter with direction=ReturnValue of XMLTYPE.

                    XML TYPE INPUT & OUTPUT PARAMS FOR A FUNCTION WITH EXECUTENONQUERY()

                    hope it helps,
                    Greg
                    • 7. Re: Execute oracle stored procedure from C# always returns null
                      706296
                      Hi gdarling

                      I saw ur reply for executing a orcale stroed procedure in C#. however in your reply, you gave C# code and on that above you gave oracle function instead of a procedure. is that C# code suites for both procedure and function.

                      i implemented your code to execute my procedure. i got an exception stating "procedure name cannot be identified"

                      here is my procedure details

                      i am using a fully qualified name of my procedure as "*sapncdb.encrypt_field"*

                      and my above said procedure takes 1 parameter of type IN and OUT.

                      i hope in my procedure case your code wont suites. becz u r taking two different parameters for IN and OUT.

                      please provide me C# code to execute the said stored procedure which was created with a IN OUT parameter.

                      plz mail me your response to my id rajkumar.bathula@gmail.com_

                      very very thanks in advance.

                      Raj
                      • 8. Re: Execute oracle stored procedure from C# always returns null
                        jareeq
                        @gdarling thanks !

                        There are to many examples and after studding bunch of them thanks to you now I know that parameters order DO MATTER.

                        Thanks Again