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 · Accepted Answer · 2012-03-29 13:34:10Z

1

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

answered Mar 29, 2012 at 13:34

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marin Bonacci Over a year ago

ORA-06553: PLS-307 is result of duplicate constructor definition, Oracle is somewhat funny in that; if you replace "in_val" parameter name with val (as is the name of the attribute), it will correctly override default constructor.

Marin Bonacci Over a year ago

I'm aware of trigger workaround, but 5-10 lines of code for something that should be possible in a much more readable and manageable fashion. Initially I presumed it is not possible because SQL context does not see inside PL/SQL context, but that's clearly not the case since then none of the OO stuff would work in pure SQL. So this problem is either an oversight on Oracle's part, or there is some syntax I'm unaware of...

Peter Lang Over a year ago

I absolutely agree, I would also hate to have the trigger workaround in my code. Btw, when overriding the constructor using val, then creating of the table with default value already fails with ORA-00904: "KLASSX"."TEST_T"."TEST_T": invalid identifier.

Marin Bonacci Over a year ago

Yes, it just points out that it is not possible to use ANY non-default constructor in default clause, even if it is an overridden default constructor. If nobody comes up with anything in a couple of days, I'll accept your answer and contact Oracle (as much good that will do).

Collectives™ on Stack Overflow

Oracle: specifying default value for object type column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related