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.
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 ); /
The result of this is:
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;
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.
Empty NULL Not available
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?
procedure p_procedure ( p_emp in osb_emp_obj_type ) is begin do_something; end;
<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 ();