tobspr tobspr - 4 months ago 7
SQL Question

Default values for Objects in OracleDB

Assuming I have an Object and Table declared like this:

CREATE TYPE MyType AS OBJECT (
some_property NUMBER(20)
) NOT FINAL;

CREATE TABLE MyTable OF MyType;


How can I specify default values? For normal tables, I would use something like
some_property NUMBER(20) DEFAULT 123
. Is it possible to use something similar for objects?

I know its possible to overload the constructor, but I'd like to be able to
use the table the same way as without objects (That is, being able to specify only a small amount of columns when inserting data, and default the others).

Answer

So instead of comment I add full answer due to misunderstanding

DDL

CREATE TYPE MyType AS OBJECT (
   some_property NUMBER(20),
  CONSTRUCTOR FUNCTION MyType(some_property number default 123) RETURN SELF AS RESULT
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY MyType AS
  CONSTRUCTOR FUNCTION MyType(some_property number default 123)
    RETURN SELF AS RESULT
  AS
  BEGIN
    SELF.some_property := some_property;
    RETURN;
  END;
END;
/

CREATE TABLE MyTable OF MyType;

DML works like this

-- Insert row with 123
INSERT INTO MyTable(some_property)
VALUES(MyType().some_property);
/

-- Insert row with 456
INSERT INTO MyTable(some_property)
VALUES(MyType(456).some_property);
/

In my opinion constructor is more flexible when create table with column object

create table testt (
      v mytype);
/

insert into testt values(MyType());
/

insert into testt values(MyType(456));
/

Update

I think you cann't write smt like this insert into <table> values() with object types using constructors. Also look at workaround with trigger Oracle: specifying default value for object type column. But it isn't reliable.

One more note

Recent I know one more thing that you can add default value to nested table column as it was ordinary table. I thought that it is forbidden.

Look at this

CREATE TYPE MyType AS OBJECT (
   some_property NUMBER(20),
   test_property NUMBER(20)
);

CREATE TABLE MyTable OF MyType
 ( some_property default '123' ); -- this simply transforms into 'alter table MYTABLE modify some_property default '123';'

 insert into MyTable(test_property) values(1)
Comments