Update column to blank (' ')
itupjaluMay 30 2011 — edited May 31 2011I have an old database design in Oracle 10g, which for reasons I hardly remember, have columns defined as CHAR and NOT NULL. Example:
PHOMOB NOT NULL CHAR(17)
To erase a value via SQL*Plus I update to blank with
...
set phomob = ' '
...
Now I try to do the same with JDBC with an empty string. Pseudocode:
String phomob = "";
prepareStatement(" ....... set phomob = ? .......");
pstmt.setString(1,phomob);
But I then run into ORA 1407 cannot update PHOMOB to NULL. I have googled the problem and found tons of information about why this happens, but that doesn't help me solve my problem.
I am not able to change the table definition, and I am not allowed to put in a faked value since this data is used elseware. I have tried to modify the update to:
set phomob = rpad(?,17)
without any luck. The only solution I see right now is to make a stored procedure that blanks this field, but since I want the users to be able to update their information (including eraseing fields) and handle that in one operation, I want to solve this problem.