SGyPhAXu

1create table organization (
2    id number(10) not null,
3    parent_id number(10),
4    title varchar2(255 char),
5    hierarchy_code varchar2(255 char)
6)
7alter table organization
8  add constraint fk_parent_id foreign key (parent_id)
9  references organization (id) 
10     
11department A
12  |
13  |__ office 1
14  |   |
15  |   |__ room A
16  |   |   |__ room A1
17  |   |   |__ room A2
18  |   |
19  |   |__ room B 
20     
21________________________________________________
22| id | parent_id | title        | hierarchy_code |
23|-------------------------------------------------
24| 1  | null      | department A | 1              |
25|-------------------------------------------------
26| 2  | 1         | office 1     | 1001           |
27|-------------------------------------------------
28| 3  | 2         | room A       | 1001001        |
29|-------------------------------------------------
30| 3  | 3         | room A1      | 1001001001     |
31|-------------------------------------------------
32| 4  | 3         | room A2      | 1001001002     |
33|-------------------------------------------------
34| 5  | 2         | room B       | 1001002        |
35 ------------------------------------------------ 
36     
37create or replace procedure after_save_organization(id_var       number, old_parentid number, new_parentid number) is
38  old_hierarchy_code        varchar2(255 char);
39  new_hierarchy_code        varchar2(255 char);
40  parent_new_hierarchy_code varchar2(255 char);
41  sub_str1                  varchar2(255 char);
42  str1                      varchar2(255 char);
43  number_                   number(10);
44begin
45
46  select p.hierarchy_code
47    into old_hierarchy_code
48    from organization p
49   where p.id = id_var;
50
51  select hierarchy_code
52    into parent_new_hierarchy_code
53    from organization
54   where id = new_parentid;
55
56  select max(p.hierarchy_code)
57    into str1
58    from organization p
59   where p.parentid = new_parentid
60     and p.hierarchy_code != old_hierarchy_code;
61
62  if str1 is not null then
63    sub_str1 := substr(str1, 0, length(str1) - 3);
64
65    number_           := to_number(substr(str1,
66                                          length(str1) - 2,
67                                          length(str1)));
68    number_           := number_ + 1;
69    new_hierarchy_code := sub_str1 || case
70                           when length(number_) = 1 then
71                            '00'
72                           when length(number_) = 2 then
73                            '0'
74                         end || number_;
75  else
76    new_hierarchy_code := parent_new_hierarchy_code || '001';
77  end if;
78
79  update organization po
80     set po.hierarchy_code = new_hierarchy_code ||
81                            substr(po.hierarchy_code,
82                                   length(old_hierarchy_code) + 1,
83                                   length(po.hierarchy_code))
84   where exists (select pp.id
85            from organization pp
86           where po.id = pp.id
87          connect by prior pp.id = pp.parentid
88           start with pp.id = id_var);
89end; 
90     
91ORA-00604: error occurred at recursive SQL level 1
92ORA-20000: Oracle Text error:
93DRG-50610: internal error: drexdsync
94DRG-50857: oracle error in drekrtd (reselect rowid row locator)
95ORA-00942: table or view does not exist
96
97ORA-06512: at "CTXSYS.SYNCRN", line 1
98ORA-06512: at line 1