1 function logstdby$tabf (request in varchar2 default null)
2 return logstdby$srecs pipelined parallel_enable
3 is
4 lsby_object_row logstdby$srec := logstdby$srec('','',0,0,0,0);
5 type lsby_tcur is ref cursor return logstdby_support_tab_10_1%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 request is null then
13 select database_role into role from sys.v$database;
14 else
15 role := request;
16 end if;
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 open dataset for select * from logstdby_support_tab_12_1;
43 elsif compat_num >= 11020003 then
44 open dataset for select * from logstdby_support_tab_11_2b;
45 elsif compat_num >= 11020000 then
46 open dataset for select * from logstdby_support_tab_11_2;
47 elsif compat_num >= 11000000 then
48 open dataset for select * from logstdby_support_tab_11_1;
49 elsif compat_num >= 10020000 then
50 open dataset for select * from logstdby_support_tab_10_2;
51 else
52 open dataset for select * from logstdby_support_tab_10_1;
53 end if;
54
55 loop
56 fetch dataset into lsby_query_row;
57 exit when dataset%NOTFOUND;
58 lsby_object_row.owner := lsby_query_row.owner;
59 lsby_object_row.name := lsby_query_row.name ;
60 lsby_object_row.type# := lsby_query_row.type#;
61 lsby_object_row.obj# := lsby_query_row.obj# ;
62 lsby_object_row.current_sby := lsby_query_row.current_sby;
63 lsby_object_row.gensby := lsby_query_row.gensby;
64 pipe row (lsby_object_row);
65 end loop;
66 close dataset;
67
68 exception when others then
69 if dataset%ISOPEN then
70 close dataset;
71 end if;
72 end;
[Home] [Help]