title: Setting default value in Oracle Object Type Constructor Function
body: <p>I want to set default values in Oracle object type, but it requires all attributes needs to be passed in constructor function for that.  </p>
  
  <p>Is there anyway, so that I can pass only required attributes in constructor function for which default value is required.  </p>
  
  <p>Please see the following details</p>
  
  <pre>
  
      SQL> CREATE TYPE TYPE_SUB AS OBJECT(
        2  COL1 NUMBER,
        3  COL2 VARCHAR2(100)
        4  )
        5  NOT FINAL
        6  /
  
      Type created.
  
      SQL> CREATE OR REPLACE TYPE TYPE_MAIN
        2  UNDER TYPE_SUB
        3  (
        4  COL3 varchar2(10),
        5  COL4 VARCHAR2(10),
        6  CONSTRUCTOR FUNCTION TYPE_MAIN(COL1 NUMBER, COL2 VARCHAR2, COL3 varchar2, COL4 VARCHAR2) RETURN SELF AS RESULT)
        7  NOT FINAL
        8  /
  
      Type created.
  
      SQL> CREATE OR REPLACE TYPE BODY TYPE_MAIN  IS
        2  CONSTRUCTOR FUNCTION TYPE_MAIN (COL1 NUMBER, COL2 VARCHAR2, COL3 varchar2, COL4 VARCHAR2) RETURN SELF AS RESULT IS
        3  BEGIN
        4   SELF.COL1 := nvl(COL1,123);
        5   SELF.COL2 := nvl(COL2,'NA');
        6   SELF.COL3 := nvl(COL3,'NA');
        7   SELF.COL4 := nvl(COL4,NULL);
        8   RETURN;
        9  end;
       10  END;
       11  /
  
      Type body created.
  
      SQL> CREATE TABLE TAB_MAIN  (
        2  PKEY NUMBER,
        3  COLTEST VARCHAR2(100),
        4  COLNEW TYPE_MAIN)
        5  /
  
      Table created.
  
      SQL> INSERT INTO TAB_MAIN(PKEY) VALUES(1)
        2  /
  
      1 row created.
  
      SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW) VALUES(1,'TESTCOL',TYPE_MAIN('1','2',NULL,NULL))
        2  /
  
      1 row created.
  
      SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW) VALUES(1,'TESTCOL2',TYPE_MAIN('1',NULL,NULL,NULL))
        2  /
  
      1 row created.
  
      SQL> SELECT * FROM TAB_MAIN
        2  /
  
            PKEY COLTEST    COLNEW(COL1, COL2, COL3, COL4)
      ---------- ---------- ----------------------------------------
               1
               1 TESTCOL    TYPE_MAIN(1, '2', 'NA', NULL)
               1 TESTCOL2   TYPE_MAIN(1, 'NA', 'NA', NULL)
  
  </pre>
  
  <p>Now, in above example if I will pass only Col3 and Col4 attribute in constructor function for default value, then it didn't work.  Please see the following example.</p>
  
  <pre>
  
      SQL> CREATE TYPE TYPE_SUB AS OBJECT(
        2  COL1 NUMBER,
        3  COL2 VARCHAR2(100)
        4  )
        5  NOT FINAL
        6  /
  
      Type created.
  
      SQL> CREATE OR REPLACE TYPE TYPE_MAIN
        2  UNDER TYPE_SUB
        3  (
        4  COL3 varchar2(10),
        5  COL4 VARCHAR2(10),
        6  CONSTRUCTOR FUNCTION TYPE_MAIN(COL3 varchar2, COL4 VARCHAR2) RETURN SELF AS RESULT)
        7  NOT FINAL
        8  /
  
      Type created.
  
      SQL> CREATE OR REPLACE TYPE BODY TYPE_MAIN  IS
        2  CONSTRUCTOR FUNCTION TYPE_MAIN (COL3 varchar2, COL4 VARCHAR2) RETURN SELF AS RESULT IS
        3  BEGIN
        4   SELF.COL3 := nvl(COL3,'NA');
        5   SELF.COL4 := nvl(COL4,NULL);
        6   RETURN;
        7  end;
        8  END;
        9  /
  
      Type body created.
  
      SQL> CREATE TABLE TAB_MAIN  (
        2  PKEY NUMBER,
        3  COLTEST VARCHAR2(100),
        4  COLNEW TYPE_MAIN)
        5  /
  
      Table created.
  
      SQL> INSERT INTO TAB_MAIN(PKEY) VALUES(1)
        2  /
  
      1 row created.
  
      SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW) VALUES(1,'TESTCOL',TYPE_MAIN('1','2',NULL,NULL))
        2  /
  
      1 row created.
  
      SQL> INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW) VALUES(1,'TESTCOL2',TYPE_MAIN('1',NULL,NULL,NULL))
        2  /
  
      1 row created.
  
      SQL> SELECT * FROM TAB_MAIN
        2  /
  
            PKEY COLTEST    COLNEW(COL1, COL2, COL3, COL4)
      ---------- ---------- ----------------------------------------
               1
               1 TESTCOL    TYPE_MAIN(1, '2', NULL, NULL)
               1 TESTCOL2   TYPE_MAIN(1, NULL, NULL, NULL)
  
  </pre>