I always thought that null is not equal to '' in Oracle Database world till today I am trying to use them in the procedure, One of my procedure will accept few in parameters(varchar2) since I am going to call an outside xml based webservice so I will need to manager the situation when the parameter is null or '': I will pass the value when it's not null AND it's not '' so I write it like this:
where in_bu is not null and in_bu!='' then..
everything works fine if I pass some thing till I pass '', I thought since I have in_bu!='' it will go to other branch but I was very surprised to see that it is not: When I pass '', it always go to a wrong process and it always treat it as NULL.
I write some simple test code and did some research and find out that in PL/SQL, if the variable is varchar type, then '' is same as NULL; when the variable is char type, '' is different with NULL.
I know NULL is very 'special' in Oracle term but still thought I understood the whole concept but obviously not really :-)
