Oracle: specifying default value for object type column

Question

I have an object type with no-args constructor, but when I specify it as default value for a column of that type, I get ORA-00904: invalid identifier error.

Example:

CREATE OR REPLACE TYPE test_t AS OBJECT
(
  val      NUMBER(10),
  CONSTRUCTOR FUNCTION test_t return self as result
)

CREATE OR REPLACE TYPE BODY test_t AS 
  CONSTRUCTOR FUNCTION test_t RETURN SELF AS RESULT IS
  BEGIN
    val := 1;
    RETURN;
  END;
END;

CREATE TABLE test_table (
    test_attr test_t DEFAULT new test_t()
)

Error: ORA-00904: "INKA"."TEST_T"."TEST_T": invalid identifier

If I replace DEFAULT with e.g. test_t(1), it works, but that sort of breaks the OO encapsulation paradigm, I want all fields of same type to have same default "default values" (hope you know what I mean :-)

Am I missing something here, or is this normal and it is not possible to use non-default constructors like this?

Peter Lang Peter Lang · Accepted Answer · 2012-03-29T13:34:10

Looks like this is not possible.

One workaround would be to use a trigger:

CREATE OR REPLACE TRIGGER test_trigger
  BEFORE INSERT OR UPDATE
ON test_table
  FOR EACH ROW
WHEN ( new.test_attr IS NULL )
BEGIN
  :new.test_attr := NEW test_t();
END test_trigger;
/

It does not completely ignore non-default constructors by the way, overriding the default constructor

CONSTRUCTOR FUNCTION test_t(in_val NUMBER)
RETURN SELF AS RESULT

leads to an exception when trying to define the table with DEFAULT NEW test_t(1):

ORA-06553: PLS-307: too many declarations of 'TEST_T' match this call

Oracle: specifying default value for object type column

1 Answers