update statement with special condition
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
declare
sql varchar(300);
vp1 varchar2(10) := '&1';
vp2 varchar2(20) := '&2';
vp3 varchar2(30) := '&3';
begin
sql := 'update t1 set c1=10 where p1 = :1 and p2 = :2 and p3 = :3';
execute immediate sql using vp1, vp2, vp3;
commit;
end;
/
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).
set serveroutput on verify off
declare
sql varchar(300);
vp1 varchar2(10) := '&1';
vp2 varchar2(20) := '&2';
vp3 varchar2(30) := '&3';
begin
sql := 'update t1 set c1=10 where p1 = :1 and p2 = :2 and p3 = :3';
execute immediate sql using vp1, vp2, vp3;
commit;
end;
/
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).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you post the snippet of the shell script that calls this? I believe that your issue is more with the shell script than the SQL.
It would also be fairly simple to write the SQL statement in the shell script and pass that into SQL*Plus as well.
Without sample code, it is difficult to see where the problem is.
It would also be fairly simple to write the SQL statement in the shell script and pass that into SQL*Plus as well.
Without sample code, it is difficult to see where the problem is.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the info.
The situation is like that
Assume that &1=' ' (one space), in Linux (red head v6), just when passing the space into update statement, the space is squeezed out to become no space, that is null, so
where (p1 = '&1' or '&1' is null) ...
becomes
where (p1 = null or null is null).
There is an error. If you use
execute immediate ...
using ...
There is no error, but within table t1, p1 is one space,... you can see the update will do nothing because nothing is not matched in the where clause.
I tried the following
if ( pv1 = ' ') then
update ... where p1 = ' ' ...
elsif ( pv1 is null ) then
update ... where p1 is null ...
else
update ... where p1 = pv1 ...
end if;
...
It always works indeed, but if having many columns to be matched, the code is awkward.
The shell script provides the value for pl/sql, but you can assume
vp1 := ''; --no space
vp2 := ' '; --one space
vp3 := 'test'; -- a value
Is there a better way to deal with the update statement to surely preserve the one space condition?
In windows 7 environment, there is no issue, but on Linux the normal update does not seem right.
The situation is like that
Assume that &1=' ' (one space), in Linux (red head v6), just when passing the space into update statement, the space is squeezed out to become no space, that is null, so
where (p1 = '&1' or '&1' is null) ...
becomes
where (p1 = null or null is null).
There is an error. If you use
execute immediate ...
using ...
There is no error, but within table t1, p1 is one space,... you can see the update will do nothing because nothing is not matched in the where clause.
I tried the following
if ( pv1 = ' ') then
update ... where p1 = ' ' ...
elsif ( pv1 is null ) then
update ... where p1 is null ...
else
update ... where p1 = pv1 ...
end if;
...
It always works indeed, but if having many columns to be matched, the code is awkward.
The shell script provides the value for pl/sql, but you can assume
vp1 := ''; --no space
vp2 := ' '; --one space
vp3 := 'test'; -- a value
Is there a better way to deal with the update statement to surely preserve the one space condition?
In windows 7 environment, there is no issue, but on Linux the normal update does not seem right.
>>if ( pv1 = ' ') then update ... where p1 = ' ' ...
That doesn't make any sense. If pv1 contains a space then the where clause you have originally will work. Oracle doesn't "squeeze" out spaces unless you tell it to.
My guess is the shell script isn't passing the "space" to the Oracle script. That is probably why johnsone asked you to post a script example where it happens. Then we can test on our systems and provide a solution.
>>becomes where (p1 = null or null is null).
Yes, I know. I thought that is what you wanted.
Since I guess it isn't, can you post a simple test case with the table, sample data to be passed into the script and expected results?
That doesn't make any sense. If pv1 contains a space then the where clause you have originally will work. Oracle doesn't "squeeze" out spaces unless you tell it to.
My guess is the shell script isn't passing the "space" to the Oracle script. That is probably why johnsone asked you to post a script example where it happens. Then we can test on our systems and provide a solution.
>>becomes where (p1 = null or null is null).
Yes, I know. I thought that is what you wanted.
Since I guess it isn't, can you post a simple test case with the table, sample data to be passed into the script and expected results?
As I said, it seems to me that the shell script is the source of the issue. You haven't posted that yet. Keeping posting the SQL statement isn't going to help. I'm sure it has to do with how you are passing the parameter into SQL*Plus, but you keep refusing to show us how you are doing that.
ASKER
Can't give the test case on rhat Linux right now. Thanks for the comments.
>>Can't give the test case on rhat Linux right now
We will wait until you can.
If we cannot see what is happening and be able to reproduce it, I don't see how we can help.
We will wait until you can.
If we cannot see what is happening and be able to reproduce it, I don't see how we can help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
nvl(:1,'') is meaningless. '' would give you a null in return, so if you are sending in null, why convert it to null. Then you are doing an equality comparison with null, which will always be false.
Based on the information so far, it seems pretty clear that the issue is with a parameter containing one or more spaces is not getting passed properly into the SQL statement. No amount of NVL is going to fix that.
Based on the information so far, it seems pretty clear that the issue is with a parameter containing one or more spaces is not getting passed properly into the SQL statement. No amount of NVL is going to fix that.
ASKER
Thanks for everyone's info. Finally it works by
execute immediate 'update ...' and passed in the array from linux as input to the update statement.
execute immediate 'update ...' and passed in the array from linux as input to the update statement.
I still don't see why the execute immediate is necessary.
A complete test case should allow us to provide working code that may likely be better than what you have.
Can you drive a screw with a table knife? Sure, but a screwdriver is a better choice...
I think the code you have is a table knife. Sure it might work but it is the wrong approach.
A complete test case should allow us to provide working code that may likely be better than what you have.
Can you drive a screw with a table knife? Sure, but a screwdriver is a better choice...
I think the code you have is a table knife. Sure it might work but it is the wrong approach.
I have to agree. Based the little information we were given, it still seems to me the problem is with the shell script, but since you won't post that part of it, we cannot give you an optimal solution.
ASKER
The small snippet is within a shell script. Each time different values are given as inputs into the pl/sql piece. If the inputs occur to be just one space, it often causes a problem that the space is squeezed into null. That directly causes the update failure. Since the real situation is for multiple columns. It is hard to use "if-else". This is why I need gurus' opinion.