In Oracle pl/sql update statement, when the condition columns are null or a space, the update doesn't always work. For example,
set serveroutput on verify off
vp1 varchar2(10) := '&1';
vp2 varchar2(20) := '&2';
vp3 varchar2(30) := '&3';
sql := 'update t1 set c1=10 where p1 = :1 and p2 = :2 and p3 = :3';
execute immediate sql using vp1, vp2, vp3;
When some of vp1,2,3 are null or just one space (' '), the statement does not always work as expected. It just squeezes the space out and leave it as null.
How to fix it? Can any gurus shed some light on it?
Maybe separate the issue. step 1: consider a space ( there is no null for any vp1,2,3).