6 Replies Latest reply on Dec 19, 2012 4:48 PM by 969638

    Is it possible to pass a null Input Oracle parameter to a stored procedure

      I have a stored procedure that take 3 inputs and gives 1 output.I'm using Oracle parameter to add all 3 input parameters as follows :

      OracleParameter inobj = cmd.Parameters.Add("wid", OracleDbType.Int32,50);
      inobj.Direction = ParameterDirection.Input;
      inobj.Value = _employeeID;

      and added the output parameter as follows:

      OracleParameter outobj = _cmd.Parameters.Add("w_first", OracleDbType.Varchar2, 50);
      outobj.Direction = ParameterDirection.Output;

      On the UI end , the user has a choice to provide 1 input or all inputs or any 2 inputs based on his interest. Stored Proc looks as follows:
      SP( inp1 in parameter, inp2 in parameter, inp3 in parameter, output1 out parameter)

      If i just get 1 input or 2 inputs from the user, is it fine to query the data for output using same stored procedure or should i have individual stored procedures for each scenario(combination of different inputs)?

      Will i get any pl/sql error that says invalid number of arguements?
        • 1. Re: Is it possible to pass a null Input Oracle parameter to a stored procedure
          The simplest way to handle this case is if they only give you one parameter, set the value of the other two to null. null is a valid value to pass into a stored procedure, and your procedure code can then decide what to do in that case.

          Much simpler than writing multiple procs. :)

          (Yes, if you just don't set them you'll get the invalid number of arguments error.)
          1 person found this helpful
          • 2. Re: Is it possible to pass a null Input Oracle parameter to a stored procedure
            Thanks for that reply. I'm wondering if optional parameters are something to deal with such scenarios? Also can you provide any reference link in which the stored procedure takes 3 input parameters and the odp.net code passes only 1 input parameter and others as null?
            • 3. Re: Is it possible to pass a null Input Oracle parameter to a stored procedure
              Possibly, I haven't used optional parameters.

              To pass in a null, you just do exactly the same thing as before, only change the value:

              OracleParameter inobj = cmd.Parameters.Add("wid", OracleDbType.Int32,50);
              inobj.Direction = ParameterDirection.Input;
              inobj.Value = null;

              In this case you'd always bind all three parameters, some of them will just have .Value = null. Inside the stored procedure, that parameter will have a null value and you can handle it in there.
              1 person found this helpful
              • 4. Re: Is it possible to pass a null Input Oracle parameter to a stored procedure
                This means if i have 1 input , i need to have a different method to pass 1 parameter value and other input parameters as null and if there are 2 inputs will need different method to handle. So 5 different methods all together?

                What if the stored procedure is as follows ,will its make things easier so that only one method is used for all scenarios?(optional parameters is set default value as null in the stored proc)

                Create or Replace
                Procedure GetFoo
                (cur_z OUT sys_refcursor,
                pub_date IN varchar2,
                fname IN varchar2 default null,
                lname IN varchar2 default null,
                phone IN varchar2 default null
                ---get some data

                *actually i can test this but, all the database servers are down today. So, i need to have idea if this thing works before i confirm with someone. Thanks.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                • 5. Re: Is it possible to pass a null Input Oracle parameter to a stored procedure
                  Maybe I can make it clearer with some C# code. :)
                  void SaveSomethingInOracle(int param1, string param2 = null, decimal? param3 = null)
                  // Set up your oracle connection and query here
                  OracleParameter inobj = _cmd.Parameters.Add("w_id", OracleDbType.Int32,50);
                  inobj.Direction = ParameterDirection.Input;
                  inobj.Value = param1;
                  OracleParameter inobj2 = _cmd.Parameters.Add("name", OracleDbType.Varchar2,50);
                  inobj2.Direction = ParameterDirection.Input;
                  inobj2.Value = param2;
                  OracleParameter inobj3 = _cmd.Parameters.Add("quantity", OracleDbType.Decimal,50);
                  inobj3.Direction = ParameterDirection.Input;
                  if (param3.HasValue)
                  inobj3.Value = param3.Value;
                  inobj3.Value = null;
                  That c# method takes 3 parameters. The first one is required, the other two are optional. If you don't set them, they take the default value null and pass that to the database. If you wanted to set the first and third ones only, you could still pass null as the second one. So you only need this one piece of code to call the stored procedure no matter what parameter combination you're using, and the proc will get values or NULLs appropriately.

                  Hope that clarifies it. You should only need one set of code to do what you want to do. :)

                  Edited by: Tridus on Dec 19, 2012 12:10 PM
                  • 6. Re: Is it possible to pass a null Input Oracle parameter to a stored procedure
                    Perfect, it dint strike me duhh!!..thank you so much.