none
Problem with OracleParameter that has default value RRS feed

  • Question

  • 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 ?

    Tuesday, June 10, 2008 12:18 PM

Answers

  • Parameters without setting the value is not work correctly.

    I have found the decision.
    I have refused the way of creation of a command accepted in Oracle.
    And before names of parameters the sign ":" is not necessary.
    Here my changes:
     
    OracleConnection conn = null;
    OracleTransaction tran=null;
    try {
       conn = new OracleConnection( ...here my connection string ... );
       OracleCommand cmd = new OracleCommand("job_edit", conn);

       cmd.CommandType = CommandType.StoredProcedure;


       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;

     

       // Parameters with values by default now can be not added


       conn.Open();
       cmd.Transaction = tran = conn.BeginTransaction();
       cmd.ExecuteNonQuery();
       tran.Commit();

     

    All works normally
    Thursday, June 19, 2008 2:24 AM

All replies

  • Would you please try to bind the two parameters without setting the value. The default value is null.

     

    Wednesday, June 11, 2008 4:48 AM
  • Did my reply answer your question or anything else you want to know?

    Monday, June 16, 2008 12:15 PM
  • Parameters without setting the value is not work correctly.

    I have found the decision.
    I have refused the way of creation of a command accepted in Oracle.
    And before names of parameters the sign ":" is not necessary.
    Here my changes:
     
    OracleConnection conn = null;
    OracleTransaction tran=null;
    try {
       conn = new OracleConnection( ...here my connection string ... );
       OracleCommand cmd = new OracleCommand("job_edit", conn);

       cmd.CommandType = CommandType.StoredProcedure;


       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;

     

       // Parameters with values by default now can be not added


       conn.Open();
       cmd.Transaction = tran = conn.BeginTransaction();
       cmd.ExecuteNonQuery();
       tran.Commit();

     

    All works normally
    Thursday, June 19, 2008 2:24 AM