6 Replies Latest reply: Nov 22, 2012 5:07 AM by Michel van Zoest RSS

    OSB to Object Types: difference between NULL value and not available

    Michel van Zoest
      Hello all,

      I have a question about Object Types. More specifically, how to differentiate between an empty value (null) and a string not available.

      This is my case:
      I have created an object type with three parameters.
      CREATE OR REPLACE TYPE OSB_EMP_OBJ_TYPE AS OBJECT
       (EMP_ID NUMBER
       ,DEPT_ID NUMBER
       ,ORDER_ID NUMBER
       ,CONSTRUCTOR FUNCTION OSB_EMP_OBJ_TYPE
       RETURN self as result
       );
      /
      I would like to see what happens when I put an empty string into emp_id, NULL into DEPT_ID and nothing into ORDER_ID.
      To do so I have this test script:
      declare
        p_emp OSB_EMP_OBJ_TYPE := OSB_EMP_OBJ_TYPE();
      begin
        p_emp.EMP_ID := '';
        p_emp.DEPT_ID := null;
      --  p_emp.ORDER_ID := null;
        --
        if p_emp.EMP_ID is null
        then
          dbms_output.put_line('Empty');
        end if;
        --
        if p_emp.DEPT_ID is null
        then
          dbms_output.put_line('NULL');
        end if;
        --
        if p_emp.ORDER_ID is null
        then
          dbms_output.put_line('Not available');
        end if;
      end;
      The result of this is:
      Empty
      NULL
      Not available
      It seems that Oracle treats all three situations alike. What I would like to achieve is a way to see that p_emp.ORDER_ID was not initialized.

      To elaborate a bit: in our production system this procedure would be called from OSB and the object type would by the input for that procedure. Our database version is 10.2.0.5.0.
      Our procedures look something like this:
      procedure p_procedure ( p_emp in osb_emp_obj_type )
      is
      begin
        do_something;
      end;
      Can anyone tell me if there is a way to achieve this, so I can see whether or not a value in the object type was filled?
      Thanks in advance!
        • 1. Re: OSB to Object Types: difference between NULL value and not available
          jeneesh
          Because '',NULL and nothing is same for ORACLE
          • 2. Re: OSB to Object Types: difference between NULL value and not available
            Michel van Zoest
            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>
            • 3. Re: OSB to Object Types: difference between NULL value and not available
              padders
              But 'not available' should be different in this situation
              You 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.
              p_emp osb_emp_obj_type := osb_emp_obj_type ();
              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.

              But then again you might consider these use cases to be significantly different as to warrant subtypes.
              • 4. Re: OSB to Object Types: difference between NULL value and not available
                Michel van Zoest
                Darn...

                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?
                • 5. Re: OSB to Object Types: difference between NULL value and not available
                  John Spencer
                  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.

                  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.

                  John
                  • 6. Re: OSB to Object Types: difference between NULL value and not available
                    Michel van Zoest
                    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!