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;

Answer 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