This content has been marked as final. Show 6 replies
Because '',NULL and nothing is same for ORACLE
That's a little short around the corner (to engrish a Dutch saying).
nothing and NULL are the same in PL/SQL, agreed. But 'not available' should be different in this situation.
The XML equivalent would look something like this:
<OSB_EMP_OBJ_TYPE> <EMP_ID/> <DEPT_ID/> </OSB_EMP_OBJ_TYPE>
But 'not available' should be different in this situationYou may want it to be different but it isn't - because PL/SQL already initialised all the attributes of osb_emp_obj_type when you called the default constructor.
If you wanted to handle this differently you consider overriding and/or overloading the constructor to cater for situations such as where no order number was supplied.
p_emp osb_emp_obj_type := osb_emp_obj_type ();
But then again you might consider these use cases to be significantly different as to warrant subtypes.
Eventually we want to use this for an update procedure. The client gets all current data from the database by calling a webservice that (using OSB) selects the data from our database. What we want to achieve is that the client can update that data, by returning only the changed fields to an update procedure.
We then handle an empty tag to update the field to null, and we ignore missing tags.
OSB itself can handle this, but PL/SQL can't.
I'm now thinking of adding an indicator to each optional field (clear_field_yn). OSB can still check for empty tags or missing tags. When a tag is empty, it's corresponding indicator will be set to 'Y'. If the tag is missing, then it will be set to 'N'.
Is that a solution to consider, or is there a much simpler approach possible?
I think your only option would be to use an indicator of some sort, whether that is an additional field in the object or a spacial value of some kind would be an implementation decision.1 person found this helpful
I do a fair bit of work with the HL7 messaging interface, which is a text based fielded structure. Not all fields in a message are required, but the absence of data does not neccessarily mean that the data does not exist, just that it is not important to this particular message, or not available tot he application sending the message. The standard in HL7 is to use a pair of double quotes ("") to indicate that the value should be set null.
We have decided to go with the Indicator approach.
In the object type we'll add an indicator to every (optional) field. OSB will fill it with a TRUE value, whenever the corresponding tag is found in the message.
In PL/SQL we'll update the field with it's new value (NULL or otherwise) when the indicator is TRUE and ignore the field when it's indicator is FALSE or empty.
That should work :-)
Thanks everybody, for thinking with me!