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
MikeDundee
Messages: 1
Registered: September 2009
Junior Member
Hello,

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
TYPE object AS OBJECT (
  value VARCHAR2(2000)
);

declare
  xml xmltype;
  obj object;
begin
  obj := object('abcd');
  xml := xmltype(obj);
  dbms_output.put_line(xml.getStringVal());
  
  obj.value := '';
  xml := xmltype(obj);
  dbms_output.put_line(xml.getStringVal());
  
  obj.value := null;
  xml := xmltype(obj);
  dbms_output.put_line(xml.getStringVal());
end;


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:

<OBJECT_ID><VALUE>abcd</VALUE></OBJECT_ID>
<OBJECT_ID><VALUE></VALUE></OBJECT_ID>
<OBJECT_ID/>

Oracle 10g behaves as expected:
<OBJECT><VALUE>abcd</VALUE></OBJECT>
<OBJECT/>
<OBJECT/>

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: 64111
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.

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


Current Time: Mon Dec 05 21:18:29 CST 2016

Total time taken to generate the page: 0.06218 seconds