Home » SQL & PL/SQL » SQL & PL/SQL » null and empty string not being the same in object? (Oracle, 9i, windows)
null and empty string not being the same in object? [message #421456] Tue, 08 September 2009 06:51 Go to next message
Messages: 1
Registered: September 2009
Junior Member

I know that null and empty string are interpreted the same in oracle.
However I discovered the strange behaviour concerning user defined objects:
create or replace
  value VARCHAR2(2000)

  xml xmltype;
  obj object;
  obj := object('abcd');
  xml := xmltype(obj);
  obj.value := '';
  xml := xmltype(obj);
  obj.value := null;
  xml := xmltype(obj);

When creating xml from object, all not-null fields are transformed into xml tag.
I supposed that obj.value being either '' or null will lead to the same result.
However this is output from Oracle 9i:


Oracle 10g behaves as expected:

However Oracle 9i behaviour leads me to the conclusion that oracle
must somehow distinguish between empty string and null in user defined objects...

Can someone clarify this behaviour?
Thus is it possible to test if object's field is empty or null?
Re: null and empty string not being the same in object? [message #421469 is a reply to message #421456] Tue, 08 September 2009 08:05 Go to previous message
Michel Cadot
Messages: 65088
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
XML is handled differently and 10g fix this behaviour accordingly to the standard which states that empty (or null) must not be included in xml string.
It is not possible for you to see a difference.

By the way, naming "OBJECT" a type is not really wise.

Previous Topic: How to check procedure is running more than stipulate time frame
Next Topic: Filter Data for uniqueness
Goto Forum:

Current Time: Tue Jul 25 22:06:25 CDT 2017

Total time taken to generate the page: 0.06257 seconds