I want to create a type with an attribute which should be expect three possible values. Something like following statement:

CREATE TYPE my_type AS OBJECT(
    attrib1 VARCHAR2(30),
    attrib2 VARCHAR2(30),
    attrib3 VARCHAR2(30) -- this attribute should accept three possible values: val1, val2, or val3
);

Is there any way to do this, just like in the case of tables?

Thank you in advance.

CodePudding user response：

This is what you'd be happy with, but - unfortunately - it won't work:

SQL> create or replace type my_type as object
  2  (attrib1 varchar2(30),
  3   attrib2 varchar2(30),
  4   attrib3 varchar2(30) check(attrib3 in ('A', 'B'))
  5  );
  6  /
create or replace type my_type as object
*
ERROR at line 1:
ORA-06545: PL/SQL: compilation error - compilation aborted
ORA-06550: line 4, column 23:
PLS-00103: Encountered the symbol "CHECK" when expecting one of the following:
:= ) , not null default external character
The symbol ":= was inserted before "CHECK" to continue.
ORA-06550: line 0, column 0:
PLS-00565: MY_TYPE must be completed as a potential REF target (object type)

Therefore, create the type as is ...

SQL> create or replace type my_type as object
  2  (attrib1 varchar2(30),
  3   attrib2 varchar2(30),
  4   attrib3 varchar2(30)
  5  );
  6  /

Type created.

... and then apply check constraint on attrib3 while using that type in create table statement, e.g.

SQL> create table test of my_type
  2  (attrib1 primary key,
  3   attrib2 not null,
  4   attrib3 default 'A' check (attrib3 in ('A', 'B'))
  5  );

Table created.

SQL>

CodePudding user response：

Is there any way to do this, just like in the case of tables?

No, you cannot apply a CHECK constraint to an OBJECT data-type.

From the CONSTRAINT documentation:

Constraint clauses can appear in the following statements:

• CREATE TABLE (see CREATE TABLE)
• ALTER TABLE (see ALTER TABLE)
• CREATE VIEW (see CREATE VIEW)
• ALTER VIEW (see ALTER VIEW)

So you cannot create a constraint in a CREATE TYPE or an ALTER TYPE statement.

CodePudding user response：

You may use custom type constructor and perform this check inside PL/SQL code.

create type my_type as object(
  attrib1 varchar2(30),
  attrib2 varchar2(30),
  attrib3 varchar2(30),
  
  constructor function my_type (
    attrib1 in varchar2,
    attrib2 in varchar2,
    attrib3 in varchar2
  ) return self as result
);
/

create type body my_type as
  constructor function my_type (
    attrib1 in varchar2,
    attrib2 in varchar2,
    attrib3 in varchar2
  ) return self as result
  as
    invalid_attr_value exception;
    pragma exception_init(invalid_attr_value, -20001);
  begin
    if attrib3 not in ('val1', 'val2', 'val3') then
      raise_application_error(-20001, 'Invalid attrib3 value supplied');
    end if;
    self.attrib1 := attrib1;
    self.attrib2 := attrib2;
    self.attrib3 := attrib3;
    return;
  end;
end;/

with a(col) as (
  select my_type('a', 'b', 'val1')
  from dual
)
select
  a.col.attrib1,
  a.col.attrib2,
  a.col.attrib3
from a a

with a(col) as (
  select my_type('a', 'b', 'val10')
  from dual
)
select
  a.col.attrib1,
  a.col.attrib2,
  a.col.attrib3
from a a

ORA-20001: Invalid attrib3 value supplied
ORA-06512: at "FIDDLE_INSFSVTZEJFLABZQAKDJ.MY_TYPE", line 12

fiddle