1 Reply Latest reply: Jun 11, 2008 1:39 PM by 502182 RSS

    Problem with OracleParameter that has default value

    644336
      I created Oracle procedure, that has parameters with default value (v_min_salary and v_max_salary)

      create or replace procedure JOB_EDIT (
      v_job_id in jobs.job_id%type,
      v_job_title in jobs.job_title%type,
      v_min_salary in jobs.min_salary%type:=null,
      v_max_salary in jobs.max_salary%type:=null
      )
      is
      begin
      ...

      And when I run it from C# code I receive OracleException: "ORA-01008: not all variables bound" :

      OracleConnection conn = null;
      OracleTransaction tran=null;
      try {
      conn = new OracleConnection( ...here my connection string ... );
      string strCmd = "begin job_edit(:v_job_id, :v_job_title, :v_min_salary, :v_max_salary); end;";
      OracleCommand cmd = new OracleCommand(strCmd, conn);

      cmd.Parameters.Add(new OracleParameter(":v_job_id", OracleType.VarChar, 10)).Value = jobRow.JOB_ID;
      cmd.Parameters.Add(new OracleParameter(":v_job_title", OracleType.VarChar, 35)).Value = jobRow.JOB_TITLE;

      // When I worked with Sql Server I did not add parameters with deafult value
      // What I must to do with parameters v_min_salary and v_max_salary ?

      conn.Open();
      cmd.Transaction = tran = conn.BeginTransaction();
      cmd.ExecuteNonQuery();
      tran.Commit();
      }
      catch (OracleException ex) {
      // here OracleException: "ORA-01008: not all variables bound"
      tran.Rollback();
      }
      catch (Exception ex) {
      tran.Rollback();
      }
      finally {
      conn.Close();
      }

      What I must to do with parameters v_min_salary and v_max_salary ?