Oracle - Cannot update table record using bind variable

Question

This query returns 1 row:

SELECT col1, col2 FROM table1 WHERE col1 = :column1;

But this updates 0 rows:

UPDATE table1 SET col2 = :column2  WHERE col1 = :column1;
COMMIT;

I added this constraint to set col1 as primary key, but it didn't fix it.

ALTER TABLE table1 ADD CONSTRAINT col1_pk PRIMARY KEY (col1);

I am trying this from SQL Developer, any idea why it does not update the row?

EDIT:

col1 is VARCHAR2(32 BYTE) NOT NULL

col2 is CLOB NOT NULL

EDIT 2: Test Case, set :var1 to 0011223344556677 in the select and update sentences.

CREATE TABLE MY_TABLE 
   (    COL1 VARCHAR2(32 BYTE) NOT NULL ENABLE, 
    COL2 CLOB, 
     CONSTRAINT "MY_TABLE_PK" PRIMARY KEY ("COL1")
   )

INSERT INTO MY_TABLE (COL1, COL2) VALUES ('0011223344556677', '1434407992143440799214344079921434407992');

SELECT * FROM MY_TABLE WHERE COL1 = :var1;

UPDATE MY_TABLE SET COL2 = 'test' WHERE COL1 = :var1;
   COMMIT;

Something doesn't track. If the `select` returns a single row, the `update` will modify exactly 1 row assuming they are run in the same context. If your `select` is run in a session where the row it is selecting is uncommitted and the `update` is run in a different session, then the `update` won't see the row in question. If some other session deletes the row before the `update` happens and commits the change, then the `update` will update 0 rows. Sort of that, though, it would be helpful to provide a test case that shows that the row is actually not being updated. — Justin Cave, Jun 15 '15 at 22:07
How do you determine that the `update` modifies 0 rows? If all this is being done in a single session and the `select` returns a row, either the bind variable value is actually different for the two statements or you're not actually executing the `update` or the `update` is modifying a row and you are missing it. — Justin Cave, Jun 15 '15 at 22:50
Thanks @JustinCave, check the test case in **Edit 2**. SQL Developer shows the message: "0 rows updated. Commit complete." in the Script Output. I'm doing the select and update in the same session, with the same bind variable value. — myrmix, Jun 15 '15 at 22:55
How do you know that the value of the bind variable is the same for both statements? — Justin Cave, Jun 15 '15 at 22:59
When I execute the statements in SQL Developer, it prompts a dialog to enter the binds, there I enter 0011223344556677 for :var1 (actually, sql developer stores this value, so I only had to enter it once). Did you try the test case? Does it update the row in your environment? — myrmix, Jun 15 '15 at 23:04

MT0 · Answer 1 · 2015-06-15T23:11:01.120

TL;DR - Make sure the value being stored in the bind variable is parsed as a character string not a number.

I've run this in SQL Developer (Version 4.0.3.16):

CREATE TABLE MY_TABLE 
   (    COL1 VARCHAR2(32 BYTE) NOT NULL ENABLE, 
    COL2 CLOB, 
     CONSTRAINT "MY_TABLE_PK" PRIMARY KEY ("COL1")
   );
/
INSERT INTO MY_TABLE (COL1, COL2) VALUES ('0011223344556677', '1434407992143440799214344079921434407992');
/
VARIABLE var1 VARCHAR2(32);
/
BEGIN
  :var1 := '0011223344556677';
END;
/
SELECT * FROM MY_TABLE WHERE COL1 = :var1;
/
UPDATE MY_TABLE SET COL2 = 'test' WHERE COL1 = :var1;
/
COMMIT;
/
SELECT * FROM MY_TABLE;
/

And it runs fine:

table MY_TABLE created.
1 rows inserted.
anonymous block completed
COL1                             COL2                                                                           
-------------------------------- --------------------------------------------------------------------------------
0011223344556677                 1434407992143440799214344079921434407992                                         

1 rows updated.
committed.
COL1                             COL2                                                                           
-------------------------------- --------------------------------------------------------------------------------
0011223344556677                 test

If you change the variable assignment to (remove quotes):

BEGIN
  :var1 := 0011223344556677;
END;

Then the value is parsed as a number and the leading zeros are ignored and the output is:

table MY_TABLE created.
1 rows inserted.
anonymous block completed
no rows selected


0 rows updated.
committed.
COL1                             COL2                                                                           
-------------------------------- --------------------------------------------------------------------------------
0011223344556677                 1434407992143440799214344079921434407992

Is there a way to do it without declaring VARIABLE var1? There has to be a way, since I'm able to do it for select and insert statements. — myrmix, Jun 15 '15 at 23:19
No - if you run the query with `CTRL-ENTER` then SQL Developer will implicitly define the variable (possibly not with the correct data type) but if you run it as a script with `F5` then will tell you the bind variable is not defined and you will need to define it manually (as I did). — MT0, Jun 15 '15 at 23:38
When I run your script in SQL Developer, although you defined var1 and set a value, it prompts a dialog to enter the var1 bind, so I enter 0011223344556677 (without quotes) and it updates the row correctly, but it does not work for me because I should not define the variable manually since I have to do this with OCILIB in C. — myrmix, Jun 16 '15 at 00:02
You could just try it in OCILIB using prepared statements and see if it works (which it should) and if it doesn't then you can post the OCILIB source in a different question and ask about that. I don't have a definitive answer about why SQL developer doesn't do what you want when you enter the values manually but it is probably using implicit type conversions somewhere hidden from the user that is affecting the equality comparison - doing it programattically you can define the types and do not get the issue. — MT0, Jun 16 '15 at 08:22

Oracle - Cannot update table record using bind variable

1 Answers1