Oracle object types: using constructors

2

As PL/SQL developer you probably have worked with object types before and maybe you also know that you can add methods (i.e. functions and procedure which operate on the object type) to your type, but did not use them.
So why should you use them or not?

First of all, it is not mandatory to use methods when working with object types in the Oracle database, but it can be handy. Especially constructors can make working with object types in PL/SQL code even easier. A constructor is a special kind of method and is a function which returns a new instance of the object and sets up the initial values of the object type. When defining an object type (CREATE OR REPLACE TYPE {object type name} AS OBJECT), a default constructor is always implicitly defined. You use that constructor to create an new instance of the object type by providing all values. But what if you do not want to provide all values or even no values at all?

In that case you need to create a constructor explictly.
In the next example we create a new object type with a constructor with no arguments:

CREATE OR REPLACE TYPE hrm_employee_obj AS OBJECT (
   EMPLOYEE_ID        NUMBER(6)
 , FIRST_NAME         VARCHAR2(20)
 , LAST_NAME          VARCHAR2(25)
 , EMAIL              VARCHAR2(25)
 , PHONE_NUMBER       VARCHAR2(20)
 , HIRE_DATE          DATE
 , JOB_ID             VARCHAR2(10)
 , SALARY             NUMBER(8,2)
 , COMMISSION_PCT     NUMBER(2,2)
 , MANAGER_ID         NUMBER(6)
 , DEPARTMENT_ID      NUMBER(4)

, CONSTRUCTOR FUNCTION hrm_employee_obj
 RETURN SELF AS RESULT
 );

CREATE OR REPLACE TYPE BODY hrm_employee_obj IS
 CONSTRUCTOR FUNCTION hrm_employee_obj
   RETURN SELF AS RESULT
 IS
 BEGIN
   RETURN;
 END;
END;

With this object type, you can use the following constructor in your PL/SQL code:

DECLARE
 l_emp hrm_employee_obj;

 BEGIN
 l_emp := hrm_employee_obj();
 ...
 END;

What this code does it that is creates a new instance of the hrm_employee_obj object type with all attributes having NULL values.
After this initialization, you can provide values for the attributes, e.g.

...
 l_emp.name := 'GRISHAM';
 ...

You can define multiple constructors and also overload them is necessary. Every constructor has the same structure:

CONSTRUCTOR FUNCTION {object type name} {(argument(s))}
RETURN SELF AS RESULT
IS
BEGIN
...
RETURN;
END;

Note that that the RETURN statement is always present and is not followed by any expression.

As being said, with a constructor with no arguments, all attributes are initialized to null. But this only applies to scalar data types. When you use other object or collection types for your attributes, you must initialize them with in the type body. E.g.:

CREATE OR REPLACE TYPE hrm_department_obj AS OBJECT (
   DEPARTMENT_ID   NUMBER(4)
 , DEPARTMENT_NAME VARCHAR2(30)
 , MANAGER_ID      NUMBER(6)
 , LOCATION_ID     NUMBER(4)
 , CONSTRUCTOR FUNCTION hrm_department_obj
   RETURN SELF AS RESULT
);
/

CREATE OR REPLACE TYPE BODY hrm_department_obj
IS
 CONSTRUCTOR FUNCTION hrm_department_obj
   RETURN SELF AS RESULT
 IS
 BEGIN
   RETURN;
 END;
END;
/

CREATE OR REPLACE TYPE hrm_employee_obj AS OBJECT (
   EMPLOYEE_ID        NUMBER(6)
 , FIRST_NAME         VARCHAR2(20)
 , LAST_NAME          VARCHAR2(25)
 , EMAIL              VARCHAR2(25)
 , PHONE_NUMBER       VARCHAR2(20)
 , HIRE_DATE          DATE
 , JOB_ID             VARCHAR2(10)
 , SALARY             NUMBER(8,2)
 , COMMISSION_PCT     NUMBER(2,2)
 , MANAGER_ID         NUMBER(6)
 , DEPARTMENT         HRM_DEPARTMENT_OBJ
 , CONSTRUCTOR FUNCTION hrm_employee_obj
   RETURN SELF AS RESULT
);
/
CREATE OR REPLACE TYPE BODY hrm_employee_obj
IS
 CONSTRUCTOR FUNCTION hrm_employee_obj
   RETURN SELF AS RESULT
 IS
 BEGIN
   department := hrm_department_obj();
   RETURN;
 END;
END;

In the examples, I use always the constructor with no arguments, because this form of a constructor provide you with a means to initialize object variables quickly, especially when you have a lot of attributes, you otherwise need to provide. And maybe you need default values to be applied on your attributes. Now you only need to implement this once (within the type body). Without the constructor you need more code to implement the same functionality.

Share.

About Author

Senior Oracle Developer for AMIS Services (Designer, Forms, PL/SQL, APEX, Oracle Service Bus)

2 Comments

  1. This is true, but there is a difference when you convert it to XML using select sys_xmlgen(hrm_employee_obj(),XMLFormat(‘EMP’)).GetStringVal() from dual;

    With:
    CREATE OR REPLACE TYPE BODY hrm_employee_obj
    IS
    CONSTRUCTOR FUNCTION hrm_employee_obj
    RETURN SELF AS RESULT
    IS
    BEGIN
    RETURN;
    END;
    END;

    The query results in:

    <?xml version=”1.0″?> <EMP/>

    With:
    CREATE OR REPLACE TYPE BODY hrm_employee_obj
    IS
    CONSTRUCTOR FUNCTION hrm_employee_obj
    RETURN SELF AS RESULT
    IS
    BEGIN
    department := hrm_department_obj();
    RETURN;
    END;
    END;

    The query results in:

    <?xml version=”1.0″?> <EMP> <DEPARTMENT/> </EMP>

  2. Anton Scheffer on

    Nice post, but even an object type attribute doesn’t have to be initialized:

    SQL>set serveroutput on
    SQL>create or replace type obj1 as object
      2  ( name varchar2(100) );
      3  /
    Type created.
    SQL>create or replace type obj2 as object
      2  ( name varchar2(100)
      3  , o1 obj1
      4  , constructor function obj2 return self as result 
      5  );
      6  /
    Type created.
    SQL>create or replace type body obj2
      2  as
      3    constructor function obj2 return self as result
      4    is
      5    begin
      6      return;
      7    end;
      8  end; 
      9  /
    Type body created.
    SQL>declare
      2   o2 obj2;
      3   o3 obj2;
      4  begin
      5    o2 := obj2();
      6    o2.name := ‘Hello';
      7    o2.o1 := obj1( ‘World’ );
      8    dbms_output.put_line( o2.name || ‘ ‘ || o2.o1.name );
      9    o3 := obj2( ‘Hallo’, obj1( ‘Wereld’ ) );
     10    dbms_output.put_line( o3.name || ‘ ‘ || o3.o1.name );
     11  end;
     12  /
    Hello World
    Hallo Wereld
    PL/SQL procedure successfully completed.
    SQL>