locked
ORA-01008: not all variables bound RRS feed

  • Question

  • User1117542579 posted

    Two working days have come and gone, and I'm no closer to solving it then when I encountered it. If anyone knows why the variables would not bind, please let me know.

    • If I hard code "Bill Jones" into the query and remark out the parameter declaration, it returns one data row.
    • This only happens with SELECT statements. Code elsewhere in the app uses INSERT, has 11 parameters, and executes without an issue (using command.ExecuteNonQuery(), not adapter.fill()).
    • Oracle.DataAccess, Version=2.112.1.0

    _________________________________________________

    string SQL = string.Empty;

    string user = "Bill Jones";

    SQL = "SELECT USERNAME FROM USERS WHERE USERNAME = :XUSERNAME";


                DataTable DT = new DataTable { };
                OracleCommand iCommand = new OracleCommand();

                iCommand.CommandText = SQL;
                iCommand.Parameters.Add(new OracleParameter("XUSERNAME", user));
                iCommand.BindByName = true;
                iCommand.Connection = new OracleConnection(ConfigurationManager.ConnectionStrings["someConnString"].ConnectionString);
                OracleDataAdapter adapter = new OracleDataAdapter(iCommand.CommandText, iCommand.Connection);                          
                iCommand.Connection.Open();
                adapter.Fill(DT);
                iCommand.Connection.Close();

    _________________________________________________

    Monday, August 8, 2016 5:20 PM

All replies

  • User269602965 posted

    Remove  iCommand.BindByName = true;

    And define the pararmeter datatype and direction.

    And of course, always clear parameters.

    Imports System.Xml.Linq.XElement
    
    Dim DateTimeStamp As Date = DateTime.Now
    Dim TicketSeq As Decimal = 1
    
    Dim connectionString As String = ConfigurationManager.ConnectionStrings("{YourOraConnStringName").ConnectionString
    ' Insert help ticket into database '
    Try
      Dim SQL = <SQL>
                  UPDATE {YourSchemaName}.HELP_TICKET
                  SET TICKET_TIMESTAMP = :TICKET_TIMESTAMP
                  WHERE TICKET_SEQ = :TICKET_SEQ
                </SQL>
      Using conn As New OracleConnection(connectionString)
        Using cmd As New OracleCommand(SQL.Value, conn)
          cmd.Parameters.Clear()
          cmd.Parameters.Add("TICKET_TIMESTAMP", OracleDbType.Date, DateTimeStamp, ParameterDirection.Input)
          cmd.Parameters.Add("TICKET_SEQ", OracleDbType.Decimal, decTicketSeq, ParameterDirection.Input)
          conn.Open()
          cmd.ExecuteNonQuery()
        End Using
      End Using
    Catch ex As Exception
    End Try
    
        Dim SQL2 = _
          <SQL2>
            SELECT WORKORDER_SEQ, UNIT_SEQ, REPAIRS
            FROM TEST.VW_LIST_WORKORDERS_HISTORY WHERE UNIT_SEQ = :p_UNIT_SEQ
          </SQL2>
        Try
          Using conn As New OracleConnection(strConn)
            Using cmd2 As New OracleCommand(SQL2.Value, conn)
              cmd2.Parameters.Clear()
              cmd2.Parameters.Add("p_UNIT_SEQ", OracleDbType.Decimal, decUnitSeq, ParameterDirection.Input)
              Using oda2 As New OracleDataAdapter(cmd2)
                Using ds2 As New DataSet()
                  oda2.Fill(ds2)
                  subrpt.InnerReport.DataSource = ds2
                End Using
              End Using
            End Using
          End Using
        Catch ex As Exception
        End Try
    



    Wednesday, August 10, 2016 3:02 AM
  • User1117542579 posted

    Thanks for your reply and example code. 

    I tried it with and without iCommand.BindByName = true. The results were the same. Since your example works, it will be a safe assumption that it is not required.

    The arguments in the Parameters.Add method for datatype and direction have been added.

    I noticed the argument "decUnitSeq" in your example (SELECT WORKORDER...). Can you expound on that?

    Finally, do you have an example of a query (SELECT...) where the parameter is of type VARCHAR2?

    The code below produces the same error. I tested several of the overloaded methods (.Add) with the same result.

                try
                {
                    string SQL = string.Empty;
                    SQL = "SELECT USERNAME FROM USERS WHERE USERNAME = :XUSERNAME";
                    DataTable DT = new DataTable { };               
                    OracleCommand iCommand = new OracleCommand();               
                    iCommand.CommandText = SQL;


                    iCommand.Parameters.Clear();
                    iCommand.Parameters.Add("XUSERNAME", OracleDbType.Varchar2, ParameterDirection.Input);
                    iCommand.Connection = new OracleConnection(ConfigurationManager.ConnectionStrings["someConn"].ConnectionString);
                    OracleDataAdapter oda = new OracleDataAdapter(iCommand.CommandText, iCommand.Connection);
                    
                    iCommand.Connection.Open();
                    oda.Fill(DT);
                    iCommand.Connection.Close();
                }

                catch (Exception ex)
                {
                    string errorMessage = ex.ToString();            
                }

    Wednesday, August 10, 2016 6:29 PM
  • User269602965 posted

    decUnitSeq is a .NET DECIMAL key value I passed to the procedure containing my sample code.  I did not include the entire proc, just the part relevant to BIND variable and PARAMETERS use.  In this case the data base KEY VALUE of the UNIT in the UNIT WORKORDER in for repair or service.

    On Oracle side NUMBER datatype and DECIMAL datatype on .NET side.

    select birdname from birds where beakcolor = :bindBeakColor

    .....

    cmd.Parameters.Add("bindBeakColor",OracleDBType.Varchar2,strBeakColor,ParameterDirection.input)

    Thursday, August 11, 2016 3:12 AM
  • User1117542579 posted

    I updated the function based on the info you provided and it still returned ORA-01008.

    However, the code below worked, so problem solved. Thanks again for your input.

                    string connectionString = ConfigurationManager.ConnectionStrings["webConfigConnStringName"].ConnectionString;
                    OracleConnection conn = new OracleConnection(connectionString);
                    OracleCommand cmd = new OracleCommand(SQL, conn);
                    cmd.BindByName = true;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add("XUSERNAME", user);
                    OracleDataAdapter adapter = new OracleDataAdapter(cmd);
                    DataSet ds = new DataSet();                
                    adapter.Fill(DT);
                    conn.Close();

    Thursday, August 11, 2016 7:16 PM