4 Replies Latest reply on Dec 2, 2015 9:43 PM by akshaypatel2397

    Cannot update a row with a bind variable in UPDATE statement

    akshaypatel2397

      I am using Oracle SQL Developer 4.0.0.13.

       

      Query : Update employes set emptime = systimestamp where emp_id = 123 and emp_device = :abc;

       

      Field Definition : emp_device char(20 byte)

      Value is : 99998000000008880999  (This value is present in the table)

       

      when i run the above query in SQL developer it asks me to give the value for the bind variable, which I paste in the text box and it returns 0 rows updated.

      But when I remove the bind variable in the update query and specify the actual value, it updates the column value.  Below is the query.

      Query:  Update employes set emptime = systimestamp where emp_id = 123 and emp_device = 99998000000008880999 ; ---(works)

       

      Also, When I add some trailing space in the bind variable text box and trim the emp_device column, it updates the column. Below is the query.

      Query : Update employes set emptime = systimestamp where emp_id = 123 and emp_device = trim(:abc);   -- (works --- :abc value is '99998000000008880999   ')

       

      Do not know what is wrong with it. Can some one please take a look and suggest a solution.

       

      Thanks,

      Akshay