Oracle Live SQL - Script: PL/SQL Object Types: putting the structure into the table

Statement 1

create table force_powers ( 
  id integer generated by default as identity not null primary key, 
  structure varchar2(50) not null, 
  name varchar2(100) not null, 
  constraint force_powers_uq_struct unique ( structure ) 
)

Table created.

Statement 2

insert into force_powers (structure, name) values ( '1', 'Universal' )

1 row(s) inserted.

Statement 3

insert into force_powers (structure, name) values ( '1.1', 'Telekinesis' )

1 row(s) inserted.

Statement 4

insert into force_powers (structure, name) values ( '1.1.1', 'Force Push' )

1 row(s) inserted.

Statement 5

insert into force_powers (structure, name) values ( '1.1.2', 'Force Pull' )

1 row(s) inserted.

Statement 6

insert into force_powers (structure, name) values ( '1.1.3', 'Force Jump' )

1 row(s) inserted.

Statement 7

insert into force_powers (structure, name) values ( '2', 'Light' )

1 row(s) inserted.

Statement 8

insert into force_powers (structure, name) values ( '2.1', 'Mind Trick' )

1 row(s) inserted.

Statement 9

insert into force_powers (structure, name) values ( '2.1.1', 'Force Persuation' )

1 row(s) inserted.

Statement 10

insert into force_powers (structure, name) values ( '3', 'Dark' )

1 row(s) inserted.

Statement 11

insert into force_powers (structure, name) values ( '3.1', 'Force wound' )

1 row(s) inserted.

Statement 12

insert into force_powers (structure, name) values ( '3.1.1', 'Force grip' )

1 row(s) inserted.

Statement 13

insert into force_powers (structure, name) values ( '3.1.2', 'Force choke' )

1 row(s) inserted.

Statement 14

create or replace type t_numeric_structure force as object 
( 
  c_level1 number(3,0), 
  c_level2 number(3,0), 
  c_level3 number(3,0), 
 
  constructor function t_numeric_structure( 
    i_struct varchar2 ) 
    return self as result, 
  member function p$_position_for_level( 
    i_string varchar2, 
    i_level positiven ) return pls_integer, 
  member function level1 return pls_integer, 
  member function level2 return pls_integer, 
  member function level3 return pls_integer, 
  member function structure return varchar2, 
  member function sort return varchar2, 
  member function depth return pls_integer, 
  member function parent return t_numeric_structure 
);

Type created.

Statement 15

create or replace type body t_numeric_structure as 
 
  constructor function t_numeric_structure( 
    i_struct varchar2 ) 
    return self as result 
  as 
    begin 
      self.c_level1 := p$_position_for_level(i_struct, 1); 
      self.c_level2 := p$_position_for_level(i_struct, 2); 
      self.c_level3 := p$_position_for_level(i_struct, 3); 
      return; 
    end; 
 
  member function p$_position_for_level( 
    i_string in varchar2, 
    i_level in positiven ) return pls_integer 
  as 
    begin 
      return 
        nvl(to_number( 
          regexp_substr(i_string, '[0-9]+', 1, i_level) 
        ),0); 
    end; 
 
  member function level1 return pls_integer 
  as 
    begin 
      return c_level1; 
    end; 
 
  member function level2 return pls_integer 
  as 
    begin 
      return c_level2; 
    end; 
 
  member function level3 return pls_integer 
  as 
    begin 
      return c_level3; 
    end; 
 
  member function structure return varchar2 
  as 
    l_result varchar2(50); 
    begin 
      if ( c_level1 > 0 ) then 
        l_result := to_char(c_level1); 
      end if; 
      if ( c_level2 > 0 ) then 
        l_result := l_result || '.' || to_char(c_level2); 
      end if; 
      if ( c_level3 > 0 ) then 
        l_result := l_result || '.' || to_char(c_level3); 
      end if; 
      return l_result; 
    end; 
 
  member function sort return varchar2 
  as 
    begin 
      return lpad(c_level1, 3, '0') 
        || lpad(c_level2, 3, '0') 
        || lpad(c_level3, 3, '0'); 
    end; 
 
  member function depth return pls_integer 
  as 
    begin 
      if c_level3 > 0 then 
        return 3; 
      elsif c_level2 > 0 then 
        return 2; 
      elsif c_level1 > 0 then 
        return 1; 
      else 
        return 0; 
      end if; 
    end; 
 
  member function parent return t_numeric_structure 
  as 
    begin 
      if ( c_level3 > 0 ) then 
        return new t_numeric_structure( 
          to_char(c_level1)||'.'||to_char(c_level2)); 
      elsif ( c_level2 > 0 ) then 
        return new t_numeric_structure( 
          to_char(c_level1)); 
      else 
        return null; 
      end if; 
    end; 
end;

Type created.

Statement 16

select * from force_powers


ID STRUCTURE NAME
1 1 Universal
2 1.1 Telekinesis
3 1.1.1 Force Push
4 1.1.2 Force Pull
5 1.1.3 Force Jump
6 2 Light
7 2.1 Mind Trick
8 2.1.1 Force Persuation
9 3 Dark
10 3.1 Force wound
11 3.1.1 Force grip
12 3.1.2 Force choke

12 rows selected.

ID	STRUCTURE	NAME
1	1	Universal
2	1.1	Telekinesis
3	1.1.1	Force Push
4	1.1.2	Force Pull
5	1.1.3	Force Jump
6	2	Light
7	2.1	Mind Trick
8	2.1.1	Force Persuation
9	3	Dark
10	3.1	Force wound
11	3.1.1	Force grip
12	3.1.2	Force choke

Statement 17

alter table force_powers 
  add struct t_numeric_structure

Table altered.

Statement 18

update force_powers 
  set struct = t_numeric_structure(structure)

12 row(s) updated.

Statement 19

select 
  p.id, 
  p.name, 
  p.struct.structure() structure 
  from force_powers p


ID NAME STRUCTURE
1 Universal 1
2 Telekinesis 1.1
3 Force Push 1.1.1
4 Force Pull 1.1.2
5 Force Jump 1.1.3
6 Light 2
7 Mind Trick 2.1
8 Force Persuation 2.1.1
9 Dark 3
10 Force wound 3.1
11 Force grip 3.1.1
12 Force choke 3.1.2

12 rows selected.

ID	NAME	STRUCTURE
1	Universal	1
2	Telekinesis	1.1
3	Force Push	1.1.1
4	Force Pull	1.1.2
5	Force Jump	1.1.3
6	Light	2
7	Mind Trick	2.1
8	Force Persuation	2.1.1
9	Dark	3
10	Force wound	3.1
11	Force grip	3.1.1
12	Force choke	3.1.2

Statement 20

select 
  p.id, 
  p.name, 
  struct.structure() structure 
  from force_powers p

ORA-00904: "STRUCT"."STRUCTURE": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 21

alter table force_powers 
  add constraint force_powers_uq_struct_new unique ( struct )

ORA-02329: column of datatype ADT cannot be unique or a primary key

More Details: https://docs.oracle.com/error-help/db/ora-02329

Statement 22

alter table force_powers 
  add constraint force_powers_uq_struct_new unique ( struct.structure() )

ORA-00904: : invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 23

create unique index idx_force_powers_struct on 
  force_powers ( struct.structure() )

ORA-30553: The function is not deterministic

More Details: https://docs.oracle.com/error-help/db/ora-30553

Statement 24

alter table force_powers 
  add constraint force_powers_uq_struct_new unique 
    ( struct.c_level1, struct.c_level2, struct.c_level3 )

Table altered.

Statement 25

alter table force_powers 
  drop column structure

Table altered.

Statement 26

insert into force_powers ( name, struct ) 
select 'some name', t_numeric_structure('4.0.0') 
from dual connect by level <= 10

ORA-00001: unique constraint (SQL_WSWPRMGTKENTACBWLNVOQJJCE.FORCE_POWERS_UQ_STRUCT_NEW) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-00001

Statement 27

update force_powers p 
  set struct = t_numeric_structure('4.0.0') 
  where p.struct.structure() like '3%'

ORA-00001: unique constraint (SQL_WSWPRMGTKENTACBWLNVOQJJCE.FORCE_POWERS_UQ_STRUCT_NEW) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-00001

Statement 28

insert into force_powers ( name, struct ) 
  values ( 'Force healing', t_numeric_structure('2.2'))

1 row(s) inserted.

Statement 29

update force_powers p 
  set struct = t_numeric_structure('3.1.3') 
  where p.struct.structure() = '3.1.2'

1 row(s) updated.

Statement 30

select 
  p.id, 
  p.struct.structure() structure, 
  p.name 
  from force_powers p


ID STRUCTURE NAME
1 1 Universal
2 1.1 Telekinesis
3 1.1.1 Force Push
4 1.1.2 Force Pull
5 1.1.3 Force Jump
6 2 Light
7 2.1 Mind Trick
8 2.1.1 Force Persuation
9 3 Dark
10 3.1 Force wound
11 3.1.1 Force grip
12 3.1.3 Force choke
23 2.2 Force healing

13 rows selected.

ID	STRUCTURE	NAME
1	1	Universal
2	1.1	Telekinesis
3	1.1.1	Force Push
4	1.1.2	Force Pull
5	1.1.3	Force Jump
6	2	Light
7	2.1	Mind Trick
8	2.1.1	Force Persuation
9	3	Dark
10	3.1	Force wound
11	3.1.1	Force grip
12	3.1.3	Force choke
23	2.2	Force healing

Statement 31

select 
  base.id, 
  base.struct.structure() structure, 
  base.name, 
  base.struct.level1() level1, 
  base.struct.level2() level3, 
  base.struct.level3() level3, 
  base.struct.depth() depth, 
  base.struct.sort() sort, 
  (select id 
     from force_powers parent 
     where parent.struct.structure() = 
           base.struct.parent().structure() 
  ) parent_id 
  from 
    force_powers base


ID STRUCTURE NAME LEVEL1 LEVEL3 LEVEL3 DEPTH SORT PARENT_ID
1 1 Universal 1 0 0 1 001000000  - 
2 1.1 Telekinesis 1 1 0 2 001001000 1
3 1.1.1 Force Push 1 1 1 3 001001001 2
4 1.1.2 Force Pull 1 1 2 3 001001002 2
5 1.1.3 Force Jump 1 1 3 3 001001003 2
6 2 Light 2 0 0 1 002000000  - 
7 2.1 Mind Trick 2 1 0 2 002001000 6
8 2.1.1 Force Persuation 2 1 1 3 002001001 7
9 3 Dark 3 0 0 1 003000000  - 
10 3.1 Force wound 3 1 0 2 003001000 9
11 3.1.1 Force grip 3 1 1 3 003001001 10
12 3.1.3 Force choke 3 1 3 3 003001003 10
23 2.2 Force healing 2 2 0 2 002002000 6

13 rows selected.

ID	STRUCTURE	NAME	LEVEL1	LEVEL3	LEVEL3	DEPTH	SORT	PARENT_ID
1	1	Universal	1	0	0	1	001000000	-
2	1.1	Telekinesis	1	1	0	2	001001000	1
3	1.1.1	Force Push	1	1	1	3	001001001	2
4	1.1.2	Force Pull	1	1	2	3	001001002	2
5	1.1.3	Force Jump	1	1	3	3	001001003	2
6	2	Light	2	0	0	1	002000000	-
7	2.1	Mind Trick	2	1	0	2	002001000	6
8	2.1.1	Force Persuation	2	1	1	3	002001001	7
9	3	Dark	3	0	0	1	003000000	-
10	3.1	Force wound	3	1	0	2	003001000	9
11	3.1.1	Force grip	3	1	1	3	003001001	10
12	3.1.3	Force choke	3	1	3	3	003001003	10
23	2.2	Force healing	2	2	0	2	002002000	6

Statement 32

create unique index idx_force_powers_uq_struct on 
  force_powers ( struct.structure() )

ORA-30553: The function is not deterministic

More Details: https://docs.oracle.com/error-help/db/ora-30553

Statement 33

create or replace type t_numeric_structure force as object 
( 
  c_level1 number(3,0), 
  c_level2 number(3,0), 
  c_level3 number(3,0), 
 
  constructor function t_numeric_structure( 
    i_struct varchar2 ) 
    return self as result, 
  member function p$_position_for_level( 
    i_string varchar2, 
    i_level positiven ) return pls_integer, 
  member function level1 return pls_integer, 
  member function level2 return pls_integer, 
  member function level3 return pls_integer, 
  member function structure return varchar2 
    deterministic, 
  member function sort return varchar2, 
  member function depth return pls_integer, 
  member function parent return t_numeric_structure2 
);

ORA-22866: cannot replace a type with table dependents

More Details: https://docs.oracle.com/error-help/db/ora-22866

Statement 34

alter table force_powers 
  add strucutre_backup varchar2(12)

Table altered.

Statement 35

update force_powers p 
  set strucutre_backup = p.struct.structure()

13 row(s) updated.

Statement 36

alter table force_powers 
  drop column struct

Table altered.

Statement 37

create or replace type t_numeric_structure force as object 
( 
  c_level1 number(3,0), 
  c_level2 number(3,0), 
  c_level3 number(3,0), 
 
  constructor function t_numeric_structure( 
    i_struct varchar2 ) 
    return self as result, 
  member function p$_position_for_level( 
    i_string varchar2, 
    i_level positiven ) return pls_integer, 
  member function level1 return pls_integer, 
  member function level2 return pls_integer, 
  member function level3 return pls_integer, 
  member function structure return varchar2 
    deterministic, 
  member function sort return varchar2, 
  member function depth return pls_integer, 
  member function parent return t_numeric_structure 
);

Type created.

Statement 38

create or replace type body t_numeric_structure as 
 
  constructor function t_numeric_structure( 
    i_struct varchar2 ) 
    return self as result 
  as 
    begin 
      self.c_level1 := p$_position_for_level(i_struct, 1); 
      self.c_level2 := p$_position_for_level(i_struct, 2); 
      self.c_level3 := p$_position_for_level(i_struct, 3); 
      return; 
    end; 
 
  member function p$_position_for_level( 
    i_string in varchar2, 
    i_level in positiven ) return pls_integer 
  as 
    begin 
      return 
        nvl(to_number( 
          regexp_substr(i_string, '[0-9]+', 1, i_level) 
        ),0); 
    end; 
 
  member function level1 return pls_integer 
  as 
    begin 
      return c_level1; 
    end; 
 
  member function level2 return pls_integer 
  as 
    begin 
      return c_level2; 
    end; 
 
  member function level3 return pls_integer 
  as 
    begin 
      return c_level3; 
    end; 
 
  member function structure return varchar2 
    deterministic 
  as 
    l_result varchar2(50); 
    begin 
      if ( c_level1 > 0 ) then 
        l_result := to_char(c_level1); 
      end if; 
      if ( c_level2 > 0 ) then 
        l_result := l_result || '.' || to_char(c_level2); 
      end if; 
      if ( c_level3 > 0 ) then 
        l_result := l_result || '.' || to_char(c_level3); 
      end if; 
      return l_result; 
    end; 
 
  member function sort return varchar2 
  as 
    begin 
      return lpad(c_level1, 3, '0') 
        || lpad(c_level2, 3, '0') 
        || lpad(c_level3, 3, '0'); 
    end; 
 
  member function depth return pls_integer 
  as 
    begin 
      if c_level3 > 0 then 
        return 3; 
      elsif c_level2 > 0 then 
        return 2; 
      elsif c_level1 > 0 then 
        return 1; 
      else 
        return 0; 
      end if; 
    end; 
 
  member function parent return t_numeric_structure 
  as 
    begin 
      if ( c_level3 > 0 ) then 
        return new t_numeric_structure( 
          to_char(c_level1)||'.'||to_char(c_level2)); 
      elsif ( c_level2 > 0 ) then 
        return new t_numeric_structure( 
          to_char(c_level1)); 
      else 
        return null; 
      end if; 
    end; 
end;

Type created.

Statement 39

alter table force_powers 
  add struct t_numeric_structure

Table altered.

Statement 40

update force_powers p 
  set struct = t_numeric_structure(strucutre_backup)

13 row(s) updated.

Statement 41

create unique index idx_force_powers_uq_struct on 
  force_powers ( substr(struct.structure(),1,12) )

Index created.

Statement 42

insert into force_powers ( name, struct ) 
select 'some name', t_numeric_structure('4.0.0') 
from dual connect by level <= 10

ORA-00001: unique constraint (SQL_WSWPRMGTKENTACBWLNVOQJJCE.IDX_FORCE_POWERS_UQ_STRUCT) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-00001

Statement 43

update force_powers p 
  set struct = t_numeric_structure('4.0.0') 
  where p.struct.structure() like '3%'

ORA-00001: unique constraint (SQL_WSWPRMGTKENTACBWLNVOQJJCE.IDX_FORCE_POWERS_UQ_STRUCT) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-00001

Statement 44

select 
  p.id, 
  p.name, 
  p.struct.structure() structure 
  from force_powers p 
  where p.struct.structure() = '1.1.1'


ID NAME STRUCTURE
3 Force Push 1.1.1

PL/SQL Object Types: putting the structure into the table

Additional Information