1 function logstdby$utabf (viewusage in varchar2)
2 return logstdby$urecs pipelined parallel_enable
3 is
4 lsby_object_row logstdby$urec := logstdby$urec('','','','',0,0,0,0,0);
5 type lsby_tcur is ref cursor return logstdby_unsupport_tab_10_2%ROWTYPE;
6 dataset lsby_tcur;
7 lsby_query_row dataset%ROWTYPE;
8 cmpat varchar2(16);
9 compat_num number;
10 role varchar2(16);
11 begin
12 if ((viewusage <> 'LOGSTDBY') and (viewusage <> 'ROLLING')) then
13 return;
14 end if;
15
16 select database_role into role from sys.v$database;
17
18 if role = 'PRIMARY' then
19 select substr(value,1,8) into cmpat
20 from sys.v$parameter where name = 'compatible';
21 else
22 select nvl(
23 (select substr(x.redo_compat,1,8)
24 from sys.x$krvslvst x),
25 (select substr(v.value,1,8) from sys.v$parameter v
26 where name = 'compatible')) into cmpat from dual;
27 end if;
28
29 /* Convert version string like 10.2.0.3.1 to number like 10020003 */
30 /* preserving only the first 4 numbers in the string. */
31
32 compat_num :=
33 nvl(to_number(substr(regexp_substr(cmpat, '\d+', 1,1),1,7),'9999999'),0)
34 * 1000000 +
35 nvl(to_number(substr(regexp_substr(cmpat, '\d+', 1,2),1,2), '99'),0)
36 * 10000 +
37 nvl(to_number(substr(regexp_substr(cmpat, '\d+', 1,3),1,2), '99'),0)
38 * 100 +
39 nvl(to_number(substr(regexp_substr(cmpat, '\d+', 1,4),1,2), '99'),0);
40
41 if compat_num >= 12000000 then
42 if viewusage = 'ROLLING' then
43 open dataset for select * from logstdby_ru_unsupport_tab_12_1;
44 else
45 open dataset for select * from logstdby_unsupport_tab_12_1;
46 end if;
47 elsif compat_num >= 11020003 then
48 open dataset for select * from logstdby_unsupport_tab_11_2b;
49 elsif compat_num >= 11020000 then
50 open dataset for select * from logstdby_unsupport_tab_11_2;
51 elsif compat_num >= 11000000 then
52 open dataset for select * from logstdby_unsupport_tab_11_1;
53 elsif compat_num >= 10020000 then
54 open dataset for select * from logstdby_unsupport_tab_10_2;
55 else
56 open dataset for select * from logstdby_unsupport_tab_10_1;
57 end if;
58
59 loop
60 fetch dataset into lsby_query_row;
61 exit when dataset%NOTFOUND;
62 if lsby_query_row.gensby = 0 or
63 lsby_query_row.gensby = -2
64 then
65 lsby_object_row.owner := lsby_query_row.owner;
66 lsby_object_row.table_name := lsby_query_row.table_name;
67 lsby_object_row.column_name := lsby_query_row.column_name;
68 lsby_object_row.attributes := lsby_query_row.attributes;
69 lsby_object_row.type# := lsby_query_row.type#;
70 lsby_object_row.scale := lsby_query_row.scale;
71 lsby_object_row.precision# := lsby_query_row.precision#;
72 lsby_object_row.charsetform := lsby_query_row.charsetform;
73 lsby_object_row.gensby := lsby_query_row.gensby;
74 pipe row (lsby_object_row);
75 end if;
76 end loop;
77 close dataset;
78
79 exception when others then
80 if dataset%ISOPEN then
81 close dataset;
82 end if;
83 end;
[Home] [Help]