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
  sql varchar(300);
  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).
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
= null is not a valid comparison, the correct syntax would be is null.  If any of the parameters being passed was an empty string or a null, then no records would be found with the syntax posted.  = null will always be false.

I'm assuming that this is more complicated than what was posted.  If this is the total process, then PL/SQL is not required and you could simply do:

update t1 set c1=10 where p1 = '&1' and p2 = '&2' and p3 = '&3';

There should be no issues with any of the variables being a space.  As these are being passed in as parameters from somewhere else, are you sure they are being properly quoted so that they actually make it into the script as a space?

Can you post an example of how you are running this script?
jl66ConsultantAuthor Commented:
Thanks for the info. I tried to give a bit background.
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.
johnsoneSenior Oracle DBACommented:
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.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
I agree that most of the SQL you posted is unnecessary.  Just go with a single update statement and be done with it.

>> If the inputs occur to be just one space

I'm not sure what you mean.  Do you mean they only provide two command line parameters and the script assigns the value that was meant for VP3 to VP2?

If you mean that VP2 gets assigned a null value and VP3 is assigned the correct value, then take what johnsone and make it handle nulls:
update t1 set c1=10 where (p1 = '&1' or '&1' is null)  and (p2 = '&2' or '&2' is null) and (p3 = '&3' or '&3' is null);

If you mean something else, as requested, please provide more information.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jl66ConsultantAuthor Commented:
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) ...
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 ...
     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.
slightwv (䄆 Netminder) Commented:
>>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?
johnsoneSenior Oracle DBACommented:
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.
jl66ConsultantAuthor Commented:
Can't give the test case on rhat Linux right now. Thanks for the comments.
slightwv (䄆 Netminder) Commented:
>>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.
HuaMin ChenProblem resolverCommented:
Do like this

set serveroutput on verify off
  sql varchar(300);
  vp1 varchar2(10) := '&1';
  vp2 varchar2(20) := '&2';
  vp3 varchar2(30) := '&3';

   sql := 'update t1 set c1=10 where p1 = nvl(:1,'''') and p2 = nvl(:2,'''') and p3 = nvl(:3,'''')';
    execute immediate sql using vp1, vp2, vp3;

Open in new window

johnsoneSenior Oracle DBACommented:
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.
jl66ConsultantAuthor Commented:
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.
slightwv (䄆 Netminder) Commented:
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.
johnsoneSenior Oracle DBACommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.