How to have a constant field inside a object-CodePudding

to test the code: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=1be9f3d406df287afd874ad5dfc94cfc

I would like to have constant field inside a object. I can do that with a package but I haven't succeeded to do that with an object. Why?

create type a as object(
  a  integer
) --OK

create type  as object(
  b constant  integer :=1
)

ORA-02302: invalid or missing type name

CodePudding user response：

The CREATE TYPE syntax does not allow it:

object_type_def ::=

datatype ::=

Nowhere does it say that you can use the CONSTANT keyword.

As an alternative, you can create an attribute and set it to a value in a user-defined constructor:

CREATE TYPE test IS OBJECT(
  a  NUMBER,
  pi NUMBER,
  
  CONSTRUCTOR FUNCTION test(
    SELF IN OUT NOCOPY test,
    a NUMBER
  ) RETURN SELF AS RESULT
);

with the type body:

CREATE TYPE BODY test IS
  CONSTRUCTOR FUNCTION test(
    SELF IN OUT NOCOPY test,
    a NUMBER
  ) RETURN SELF AS RESULT
  IS
  BEGIN
    SELF.a  := a;
    SELF.pi := 3.14159;
    RETURN;
  END;
END;
/

Then:

SELECT test(1).a, test(1).pi
FROM   DUAL;

Outputs:

TEST(1).A TEST(1).PI

1 3.14159

TEST(1).A	TEST(1).PI
1	3.14159

However, that does not make the value a constant and it can still be set to something else:

DECLARE
  v_obj TEST := TEST(1);
BEGIN
  v_obj.pi := 4; -- For large circles!
  DBMS_OUTPUT.PUT_LINE( v_obj.a || ', ' || v_obj.pi );
END;
/

Outputs:

1, 4

As long as you have a policy of never changing the value then it will be the default from the user-defined constructor; if someone breaks that policy then...

db<>fiddle here