How to set a constant value when creating a type?-CodePudding

I'm trying to set "Datetime" from super-type "OperationTY" and "Op_type" from sub-type "CallTY" as constant values (CURRENT_TIMESTAMP) and 'Call'. I don't really know how to do it, I'm new in Oracle, so I decided to use the constructor and set default values:

CREATE TYPE OperationTY AS OBJECT (
 Datetime TIMESTAMP, 
 Customer REF Customerty,
 Content BLOB, 
 CONSTRUCTOR FUNCTION Operationty (
  Datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  Customer REF Customerty DEFAULT NULL,
  Content BLOB DEFAULT NULL)
  RETURN SELF AS RESULT
 ) 
NOT FINAL;

CREATE TYPE BODY OperationTY AS
CONSTRUCTOR FUNCTION OperationTY (
 Datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 Customer REF CustomerTY DEFAULT NULL,
 Content BLOB DEFAULT NULL)
RETURN SELF AS RESULT IS
BEGIN
 SELF.Datetime := Datetime;
 SELF.Customer := Customer;
 SELF.Content := Content;
 RETURN;
 END;
END;

in the same way i created the subtype

CREATE TYPE CallTY UNDER OperatinTY(
 Duration NUMBER,
 Location VARCHAR2(20),
 Receiver VARCHAR2(20),
 Op_type VARCHAR2(4),
 CONSTRUCTOR FUNCTION Callty (
  Duration NUMBER DEFAULT NULL,
  Location VARCHAR2 DEFAULT NULL,
  Receiver VARCHAR2 DEFAULT NULL,
  Op_type VARCHAR2 DEFAULT 'Call')
  RETURN SELF AS RESULT)

CREATE TYPE BODY CallTY AS
CONSTRUCTOR FUNCTION CallTY (
 Duration NUMBER DEFAULT NULL,
 Location VARCHAR2 DEFAULT NULL,
 Receiver VARCHAR2 DEFAULT NULL,
 Op_type VARCHAR2 DEFAULT 'Call')
 RETURN SELF AS RESULT IS
 BEGIN
  SELF.Duration := Duration;
  SELF.Location := Location;
  SELF.Receiver := Receiver;
  SELF.Op_type := Op_type;
  RETURN;
 END;
END;
 
CREATE TABLE Operation of Operationty;

So when i try to make an INSERT :

Insert into Operation values (CallTY('', (Select REF(c) from customer c Where c.name = 'John'), NULL, 1, 'London', '1234567890', '')

then instead of querying the default value (CURRENT_TIMESTAMP) and 'Call', i get a null value.

What am I doing wrong?

CodePudding user response：

Specify all the constructor arguments in the sub-type:

CREATE TYPE CallTY UNDER OperationTY(
  Duration NUMBER,
  Location VARCHAR2(20),
  Receiver VARCHAR2(20),
  Op_type VARCHAR2(4),

  CONSTRUCTOR FUNCTION Callty (
    Datetime TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
    Customer REF CustomerTY DEFAULT NULL,
    Content  BLOB           DEFAULT NULL,
    Duration NUMBER         DEFAULT NULL,
    Location VARCHAR2       DEFAULT NULL,
    Receiver VARCHAR2       DEFAULT NULL,
    Op_type  VARCHAR2       DEFAULT 'Call'
  )
  RETURN SELF AS RESULT
);

CREATE TYPE BODY CallTY AS
  CONSTRUCTOR FUNCTION CallTY (
    Datetime TIMESTAMP      DEFAULT CURRENT_TIMESTAMP,
    Customer REF CustomerTY DEFAULT NULL,
    Content  BLOB           DEFAULT NULL,
    Duration NUMBER         DEFAULT NULL,
    Location VARCHAR2       DEFAULT NULL,
    Receiver VARCHAR2       DEFAULT NULL,
    Op_type  VARCHAR2       DEFAULT 'Call'
  )
  RETURN SELF AS RESULT
  IS
  BEGIN
    SELF.Datetime := Datetime;
    SELF.Customer := Customer;
    SELF.Content  := Content;
    SELF.Duration := Duration;
    SELF.Location := Location;
    SELF.Receiver := Receiver;
    SELF.Op_type  := Op_type;
    RETURN;
  END;
END;
/

Then you can either use named arguments and skip the ones you want to use the DEFAULT for:

Insert into Operation values (
  CallTY(
    Customer => (Select REF(c) from customer c Where c.name = 'John'),
    Content  => NULL,
    Duration => 1,
    Location => 'London',
    Receiver => '1234567890', 
    op_type  => NULL
  )
);

Or specify all the arguments:

Insert into Operation values (
  CallTY(
    CURRENT_TIMESTAMP,
    (Select REF(c) from customer c Where c.name = 'John'),
    NULL,
    1,
    'London',
    '1234567890', 
    NULL
  )
);

What am I doing wrong?

You passed an empty string literal '' (which, in Oracle, is identical to a NULL value) and that overrides any DEFAULT. So the INSERT put in a NULL value into the field because you told it to do that instead of having no value when it would use the DEFAULT.

db<>fiddle here