Setting default value in Oracle Object Type Constructor Function

Question

I want to set default values in Oracle object type, but it requires all attributes needs to be passed in constructor function for that.

Is there anyway, so that I can pass only required attributes in constructor function for which default value is required.

Please see the following details


    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)

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.


    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)

Alex Poole Alex Poole · Accepted Answer · 2013-07-30T07:46:22

In your second code block, you aren't calling the constructor that only has two arguments. Passing a null argument doesn't mean you use the other constructor, you're calling the (default) constructor with four arguments, two of which just happen to be intentionally null. You're effectively doing:

TYPE_MAIN(col1 => '1', col2 => NULL, col3 => NULL, col4 => NULL)

not

TYPE_MAIN(col3 => '1', col4 => NULL)

So this works:

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW) VALUES(1,'TESTCOL2',TYPE_MAIN('3','4'))
/

...

      PKEY COLTEST         COLNEW                                           
---------- --------------- --------------------------------------------------
         1                                                                    
         1 TESTCOL         TYPE_MAIN(1,'2',NULL,NULL)           
         1 TESTCOL2        TYPE_MAIN(1,NULL,NULL,NULL)          
         1 TESTCOL2        TYPE_MAIN(NULL,NULL,'3','4')

To get the defaults to work as I think you intend in your first code block, set them in the constructor parameters, not in the body of the constructor:

CREATE OR REPLACE TYPE TYPE_MAIN
UNDER TYPE_SUB
(
COL3 varchar2(10),
COL4 VARCHAR2(10),
CONSTRUCTOR FUNCTION TYPE_MAIN(COL1 number default 123,
  COL2 VARCHAR2 default 'NA',
  COL3 varchar2 default 'NA',
  COL4 VARCHAR2 default null) RETURN SELF AS RESULT)
NOT FINAL
/

CREATE OR REPLACE TYPE BODY TYPE_MAIN  IS
CONSTRUCTOR FUNCTION TYPE_MAIN (COL1 number default 123,
  COL2 VARCHAR2 default 'NA',
  COL3 varchar2 default 'NA',
  COL4 VARCHAR2 default null) RETURN SELF AS RESULT IS
BEGIN
 SELF.COL1 := COL1;
 SELF.COL2 := COL2;
 SELF.COL3 := COL3;
 SELF.COL4 := COL4;
RETURN;
end;
END;
/

Then when only passing one of the arguments (or any time you aren't passing them all, or the first few that you want to override in order starting from col1) you need to name it, rather than passing null for the others, as that would only override the defaults.

INSERT INTO TAB_MAIN(PKEY) VALUES(1)
/

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)
VALUES(1,'TESTCOL',TYPE_MAIN(1,'2'))
/

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)
VALUES(1,'TESTCOL2',TYPE_MAIN(1))
/

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)
VALUES(1,'TESTCOL3',TYPE_MAIN(col3 => '3'))
/

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)
VALUES(1,'TESTCOL4',TYPE_MAIN(col4 => '4'))
/

      PKEY COLTEST         COLNEW                                           
---------- --------------- --------------------------------------------------
         1                                                                    
         1 TESTCOL         TYPE_MAIN(1,'2','NA',NULL)           
         1 TESTCOL2        TYPE_MAIN(1,'NA','NA',NULL)          
         1 TESTCOL3        TYPE_MAIN(123,'NA','3',NULL)         
         1 TESTCOL4        TYPE_MAIN(123,'NA','NA','4')

To pass both col3 and col4 you'll still need to name the arguments, otherwise it will assume you're starting from col1:

INSERT INTO TAB_MAIN(PKEY,COLTEST,COLNEW)
VALUES(1,'TESTCOL5',TYPE_MAIN(col3 => '3', col4 => '4'))
/

      PKEY COLTEST         COLNEW                                           
---------- --------------- --------------------------------------------------
         1                                                                    
         1 TESTCOL         TYPE_MAIN(1,'2','NA',NULL)           
         1 TESTCOL2        TYPE_MAIN(1,'NA','NA',NULL)          
         1 TESTCOL3        TYPE_MAIN(123,'NA','3',NULL)         
         1 TESTCOL4        TYPE_MAIN(123,'NA','NA','4')         
         1 TESTCOL5        TYPE_MAIN(123,'NA','3','4')

Setting default value in Oracle Object Type Constructor Function

1 Answers