eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: SYS.PRVTPARENTCHILD

Source


1 package body PrvtParentChild
2 IS
3 
4 Function getParentIDFromModelID
5        ( modelID IN varchar2 )
6        RETURN PARENTIDARRAY
7     IS
8        parentElementID PARENTIDARRAY := PARENTIDARRAY();
9        cur0 refCursor;
10        pID RAW(200);
11        sqlStmtBase varchar2(2000);
12        sqlStmt varchar2(2000);
13        counter number(38);
14        complexID varchar2(200);
15        BEGIN
16     sqlStmtBase := 'from xdb.xdb$complex_type ct where ( sys_op_r2o(ct.xmldata.all_kid) =';
17     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
18     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.choice_kid) = ';
19     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
20     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.sequence_kid) = ';
21     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
22     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.group_kid) = ';
23     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
24     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.complexcontent.extension.sequence_kid) = ';
25     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
26     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.complexcontent.extension.choice_kid) = ';
27     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
28     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.complexcontent.extension.all_kid) = ';
29     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
30     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.complexcontent.extension.group_kid) = ';
31     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
32     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.complexcontent.restriction.sequence_kid) = ';
33     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
34     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.complexcontent.restriction.choice_kid) = ';
35     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
36     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.complexcontent.restriction.all_kid) = ';
37     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
38     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.complexcontent.restriction.group_kid) = ';
39     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
40     sqlStmtBase := sqlStmtBase || ' )';
41 
42     sqlStmt := ' select count(*) ' || sqlStmtBase;
43     EXECUTE IMMEDIATE sqlStmt INTO counter ;
44     IF (counter <> 0) THEN
45         sqlStmt := 'select ct.sys_nc_oid$ ' || sqlStmtBase;
46         EXECUTE IMMEDIATE sqlStmt INTO complexID ;
47 
48         sqlStmt := ' select hextoraw(e.xmldata.property.prop_number) from xdb.xdb$element e where sys_op_r2o(e.xmldata.property.type_ref) = ';
49         sqlStmt := sqlStmt || Dbms_Assert.Enquote_Literal(complexID);
50         sqlStmt := sqlStmt ;
51         counter := 1;
52         OPEN cur0 FOR sqlStmt;
53         LOOP
54              FETCH cur0 INTO pID;
55              EXIT WHEN cur0%NOTFOUND;
56              parentElementID.extend;
57              parentElementID(counter) := pID;
58              counter := counter + 1;
59         END LOOP;
60         CLOSE cur0;
61         RETURN parentElementID;
62     ELSE
63        RETURN NULL;
64     END IF;
65 
66 
67     RETURN parentElementID;
68     EXCEPTION
69     WHEN OTHERS THEN
70       RETURN NULL;
71     END;
72 
73 Function getParentIDFromGroupID
74        ( groupID IN varchar2 )
75        RETURN PARENTIDARRAY
76     IS
77        modelID varchar2(200);
78        complexID varchar2(200);
79        counter number(38);
80        sqlStmtBase varchar2(2000);
81        sqlStmt varchar2(2000);
82        seqKid boolean := FALSE;
83        choiceKid boolean := FALSE;
84        allKid boolean := FALSE;
85        kidClause varchar2(100);
86        elementID varchar2(200);
87        parentElementID PARENTIDARRAY := PARENTIDARRAY();
88        cur0 refCursor;
89        pID RAW(200);
90     BEGIN
91     counter := 0;
92        -- Get the reference ID from the def ID
93        select count(*) INTO counter from xdb.xdb$group_ref rg, xdb.xdb$group_def dg where ref(dg) = rg.xmldata.groupref_ref and dg.sys_nc_oid$= groupID;
94        IF (counter <> 0) THEN
95           select rg.sys_nc_oid$ INTO elementID from xdb.xdb$group_ref rg, xdb.xdb$group_def dg where ref(dg) = rg.xmldata.groupref_ref and dg.sys_nc_oid$= groupID;
96        ELSE
97           RETURN NULL;
98        END IF;
99        -- choice
100       sqlStmtBase := 'from xdb.xdb$choice_model sm, table(sm.xmldata.groups) t where sys_op_r2o(t.column_value) = ';
101       sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(elementID);
102       sqlStmt := ' select count(*) ' || sqlStmtBase;
103        EXECUTE IMMEDIATE sqlStmt INTO counter ;
104        IF ( counter <> 0 ) THEN
105            sqlStmt := ' select sm.sys_nc_oid$ ' || sqlStmtBase;
106            EXECUTE IMMEDIATE sqlStmt INTO modelID ;
107            choiceKid := TRUE;
108        ELSE
109       sqlStmtBase := 'from xdb.xdb$sequence_model sm, table(sm.xmldata.groups) t where sys_op_r2o(t.column_value) = ';
110       sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(elementID);
111       sqlStmt := ' select count(*) ' || sqlStmtBase;
112        EXECUTE IMMEDIATE sqlStmt INTO counter ;
113           IF ( counter <> 0 ) THEN
114 
115            sqlStmt := ' select sm.sys_nc_oid$ ' || sqlStmtBase;
116            EXECUTE IMMEDIATE sqlStmt INTO modelID ;
117             seqKid := TRUE;
118           ELSE
119       sqlStmtBase := 'from xdb.xdb$all_model sm, table(sm.xmldata.groups) t where sys_op_r2o(t.column_value) = ';
120       sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(elementID);
121       sqlStmt := ' select count(*) ' || sqlStmtBase;
122             IF ( counter <> 0 ) THEN
123 
124            sqlStmt := ' select sm.sys_nc_oid$ ' || sqlStmtBase;
125            EXECUTE IMMEDIATE sqlStmt INTO modelID ;
126                 allKid := TRUE;
127             ELSE
128                -- could be a direct child
129     sqlStmtBase := 'from xdb.xdb$complex_type ct where ( ';
130     sqlStmtBase := sqlStmtBase || '  sys_op_r2o(ct.xmldata.group_kid) = ';
131     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(elementID);
132     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.complexcontent.extension.group_kid) = ';
133     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(elementID);
134     sqlStmtBase := sqlStmtBase || ' OR sys_op_r2o(ct.xmldata.complexcontent.restriction.group_kid) = ';
135     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(elementID);
136     sqlStmtBase := sqlStmtBase || ' )';
137 
138     sqlStmt := ' select count(*) ' || sqlStmtBase;
139     DBMS_OUTPUT.PUT_LINE ( sqlStmt);
140     EXECUTE IMMEDIATE sqlStmt INTO counter ;
141     IF (counter <> 0) THEN
142         sqlStmt := 'select ct.sys_nc_oid$ ' || sqlStmtBase;
143         EXECUTE IMMEDIATE sqlStmt INTO complexID ;
144 
145         sqlStmt := ' select hextoraw(e.xmldata.property.prop_number) from xdb.xdb$element e where sys_op_r2o(e.xmldata.property.type_ref) = ';
146         sqlStmt := sqlStmt || Dbms_Assert.Enquote_Literal(complexID);
147         sqlStmt := sqlStmt ;
148 
149         counter := 1;
150         OPEN cur0 FOR sqlStmt;
151         LOOP
152              FETCH cur0 INTO pID;
153              EXIT WHEN cur0%NOTFOUND;
154              parentElementID.extend;
155              parentElementID(counter) := pID;
156              counter := counter + 1;
157         END LOOP;
158         CLOSE cur0;
159         RETURN parentElementID;
160     ELSE
161        RETURN NULL;
162     END IF;
163             END IF;
164         END IF;
165        END IF;
166 
167        sqlStmtBase := '';
168        WHILE TRUE
169        LOOP
170 
171             IF (seqKid = TRUE) THEN kidClause := 'sequence_kids';
172             ELSIF (choiceKid = TRUE) THEN kidClause := 'choice_kids';
173             ELSE RETURN getParentIDFromModelID(modelID);
174             END IF;
175 
176             counter := 0;
177             sqlStmtBase := 'table(sm.xmldata.' || kidClause || ')t where sys_op_r2o(t.column_value) = ';
178             sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
179             sqlStmt := 'select count(*) from xdb.xdb$choice_model sm, ' || sqlStmtBase;
180             EXECUTE IMMEDIATE sqlStmt INTO counter  ;
181             IF (counter <> 0) THEN
182               sqlStmt := 'select sm.sys_nc_oid$ from xdb.xdb$choice_model sm, ' || sqlStmtBase;
183               EXECUTE IMMEDIATE sqlStmt INTO modelID ;
184               choicekid := TRUE; seqKid := FALSE; allKid := FALSE;
185             ELSE
186               sqlStmt := 'select count(*)   from xdb.xdb$sequence_model sm, ' || sqlStmtBase;
187               EXECUTE IMMEDIATE sqlStmt INTO counter;
188               IF (counter <> 0) THEN
189                  sqlStmt := 'select sm.sys_nc_oid$  from xdb.xdb$sequence_model sm, ' || sqlStmtBase;
190                  EXECUTE IMMEDIATE sqlStmt INTO modelID ;
191                  choicekid := FALSE; seqKid := TRUE; allKid := FALSE;
192               ELSE
193                  sqlStmt := 'select count(*)  from xdb.xdb$all_model sm, ' || sqlStmtBase;
194                  EXECUTE IMMEDIATE sqlStmt INTO counter ;
195                  IF (counter <> 0) THEN
196                     sqlStmt := 'select sm.sys_nc_oid$   from xdb.xdb$all_model sm, ' || sqlStmtBase;
197                     EXECUTE IMMEDIATE sqlStmt INTO modelID;
198                     choicekid := FALSE; seqKid := FALSE; allKid := TRUE;
199                  ELSE -- group
200                        RETURN getParentIDFromModelID(modelID);
201                  END IF;
202               END IF;
203            END IF;
204        END LOOP;
205 
206     RETURN NULL;
207     EXCEPTION
208     WHEN OTHERS THEN
209       RETURN NULL;
210 END;
211 
212 Function getParentID
213        ( elementID IN varchar2 )
214        RETURN PARENTIDARRAY
215     IS
216        modelID varchar2(200);
217        complexID varchar2(200);
218        counter number(38);
219        sqlStmtBase varchar2(2000);
220        sqlStmt varchar2(2000);
221 
222        seqKid boolean := FALSE;
223        choiceKid boolean := FALSE;
224        allKid boolean := FALSE;
225        kidClause varchar2(100);
226     BEGIN
227     counter := 0;
228        -- choice
229        select count(*) INTO counter from xdb.xdb$element e, xdb.xdb$choice_model sm,
230        table(sm.xmldata.elements)t where ref(e) = t.column_value and e.xmldata.property.prop_number = elementID ;
231        IF ( counter <> 0 ) THEN
232            select sm.sys_nc_oid$ INTO modelID from xdb.xdb$element e, xdb.xdb$choice_model sm, table(sm.xmldata.elements)t where
233            ref(e) = t.column_value and e.xmldata.property.prop_number =  elementID ;
234            choiceKid := TRUE;
235        ELSE
236           -- sequence
237           select count(*) INTO counter from xdb.xdb$element e, xdb.xdb$sequence_model sm,
238           table(sm.xmldata.elements)t where ref(e) = t.column_value and e.xmldata.property.prop_number = elementID ;
239           IF ( counter <> 0 ) THEN
240             select sm.sys_nc_oid$ INTO modelID from xdb.xdb$element e, xdb.xdb$sequence_model sm, table(sm.xmldata.elements)t where
241             ref(e) = t.column_value and e.xmldata.property.prop_number =  elementID ;
242             seqKid := TRUE;
243           ELSE
244             -- all
245             select count(*) INTO counter from xdb.xdb$element e, xdb.xdb$all_model sm,
246             table(sm.xmldata.elements)t where ref(e) = t.column_value and e.xmldata.property.prop_number =  elementID ;
247             IF ( counter <> 0 ) THEN
248                 select sm.sys_nc_oid$ INTO modelID from xdb.xdb$element e, xdb.xdb$all_model sm, table(sm.xmldata.elements)t where
249                 ref(e) = t.column_value and e.xmldata.property.prop_number =  elementID ;
250                 allKid := TRUE;
251             ELSE
252                RETURN NULL;
253             END IF;
254         END IF;
255        END IF;
256 
257        WHILE TRUE
258        LOOP
259             IF (seqKid = TRUE) THEN kidClause := 'sequence_kids';
260             ELSIF (choiceKid = TRUE) THEN kidClause := 'choice_kids';
261             ELSE RETURN getParentIDFromModelID(modelID);
262             END IF;
263 
264             counter := 0;
265             sqlStmtBase := 'table(sm.xmldata.' || kidClause || ')t where sys_op_r2o(t.column_value) = ';
266             sqlStmtBase := sqlStmtBase ||  Dbms_Assert.Enquote_Literal(modelID);
267             sqlStmt := 'select count(*) from xdb.xdb$choice_model sm, ' || sqlStmtBase;
268             EXECUTE IMMEDIATE sqlStmt INTO counter  ;
269             IF (counter <> 0) THEN
270               sqlStmt := 'select sm.sys_nc_oid$ from xdb.xdb$choice_model sm, ' || sqlStmtBase;
271               EXECUTE IMMEDIATE sqlStmt INTO modelID ;
272               choicekid := TRUE; seqKid := FALSE; allKid := FALSE;
273             ELSE
274               sqlStmt := 'select count(*)   from xdb.xdb$sequence_model sm, ' || sqlStmtBase;
275               EXECUTE IMMEDIATE sqlStmt INTO counter;
276               IF (counter <> 0) THEN
277                  sqlStmt := 'select sm.sys_nc_oid$  from xdb.xdb$sequence_model sm, ' || sqlStmtBase;
278                  EXECUTE IMMEDIATE sqlStmt INTO modelID ;
279                  choicekid := FALSE; seqKid := TRUE; allKid := FALSE;
280               ELSE
281                  sqlStmt := 'select count(*)  from xdb.xdb$all_model sm, ' || sqlStmtBase;
282                  EXECUTE IMMEDIATE sqlStmt INTO counter ;
283                  IF (counter <> 0) THEN
284                     sqlStmt := 'select sm.sys_nc_oid$   from xdb.xdb$all_model sm, ' || sqlStmtBase;
285                     EXECUTE IMMEDIATE sqlStmt INTO modelID;
286                     choicekid := FALSE; seqKid := FALSE; allKid := TRUE;
287                  ELSE -- group
288                     IF (seqKid = TRUE) THEN kidClause := 'sequence_kid';
289                     ELSIF (choiceKid = TRUE) THEN kidClause := 'choice_kid';
290                     ELSE kidClause := 'all_kid';
291                     END IF;
292                     sqlStmtBase := '  from xdb.xdb$group_def sm where sys_op_r2o(sm.xmldata. ' || kidClause || ') =';
293                     sqlStmtBase := sqlStmtBase || Dbms_Assert.Enquote_Literal(modelID);
294                     sqlStmt := 'select count(*) ' || sqlStmtBase;
295                     EXECUTE IMMEDIATE sqlStmt INTO counter ;
296                     IF (counter <> 0) THEN
297                        sqlStmt := 'select sm.sys_nc_oid$ ' || sqlStmtBase;
298                        EXECUTE IMMEDIATE sqlStmt INTO modelID;
299                        RETURN getParentIDFromGroupID(modelID);
300                     ELSE
301                        RETURN getParentIDFromModelID(modelID);
302                     END IF;
303                  END IF;
304               END IF;
305            END IF;
306        END LOOP;
307 
308     RETURN NULL;
309     EXCEPTION
310     WHEN OTHERS THEN
311        RETURN NULL;
312 END;
313 
314   Function sizeArray ( elementID IN varchar2 )
315        RETURN NUMBER
316   IS
317    p parentidarray;
318   BEGIN
319     p := PrvtParentChild.getparentID(elementID);
320     IF (p IS NULL) THEN
321        RETURN 0;
322     ELSE
323        RETURN p.count;
324     END IF;
325   END;
326 END;