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
– Justin Cave, Commented Jun 15, 2015 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
– Justin Cave, Commented Jun 15, 2015 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
– myrmix, Commented Jun 15, 2015 at 22:55
How do you know that the value of the bind variable is the same for both statements? — Justin Cave
– Justin Cave, Commented Jun 15, 2015 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
– myrmix, Commented Jun 15, 2015 at 23:04

MT0 · Accepted Answer · 2015-06-15 23:11:01Z

1

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

edited Jun 15, 2015 at 23:11

answered Jun 15, 2015 at 23:05

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

myrmix Over a year ago

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.

MT0 Over a year ago

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).

myrmix Over a year ago

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.

MT0 Over a year ago

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.

Collectives™ on Stack Overflow

Oracle - Cannot update table record using bind variable

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related