6 Replies Latest reply on Nov 22, 2012 11:07 AM by Michel van Zoest

    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!