1 procedure DBMS_FEATURE_APEX
2 ( feature_boolean OUT NUMBER,
3 aux_count OUT NUMBER,
4 feature_info OUT CLOB)
5 AS
6 l_apex_schema varchar2(30) := null;
7 l_usage_detect number := 0;
8 l_num_apps number := 0;
9 l_num_workspace number := 0;
10 l_num_users number := 0;
11 begin
12 /* Determine current schema for Application Express
13 Note: this will only return one row */
14 for c1 in (select schema
15 from dba_registry
16 where comp_id = 'APEX' ) loop
17 l_apex_schema := dbms_assert.enquote_name(c1.schema, FALSE);
18 end loop;
19
20 /* If not found, then APEX is not installed */
21 if l_apex_schema is null then
22 feature_boolean := 0;
23 aux_count := 0;
24 feature_info := to_clob('APEX usage not detected');
25 return;
26 end if;
27
28 /* Determine if any activity since last sample date */
29 execute immediate 'select count(*)
30 from dual
31 where exists (select null
32 from '||l_apex_schema||'.wwv_flow_activity_log
33 where time_stamp > nvl((select last_sample_date
34 from dba_feature_usage_statistics
35 where name = ''Application Express''),
36 (sysdate -7)) )' into l_usage_detect;
37
38 if l_usage_detect = 1 then
39
40 /* Determine the number of user-created applications */
41 execute immediate 'select count(*)
42 from '||l_apex_schema||'.wwv_flows
43 where security_group_id != 10' into l_num_apps;
44
45 /* Determine the number of workspaces */
46 execute immediate 'select count(*)
47 from '||l_apex_schema||'.wwv_flow_companies
48 where provisioning_company_id != 10' into l_num_workspace;
49
50 /* Determine the number of non-internal Application Express users */
51 execute immediate 'select count(*)
52 from '||l_apex_schema||'.wwv_flow_fnd_user
53 where security_group_id != 10' into l_num_users;
54
55 feature_boolean := 1;
56 aux_count := l_num_apps;
57 feature_info := to_clob('Number of applications: '||to_char(l_num_apps)||
58 ', '||'Number of workspaces: '||to_char(l_num_workspace)||
59 ', '||'Number of users: '||to_char(l_num_users));
60
61 else
62 feature_boolean := 0;
63 aux_count := 0;
64 feature_info := to_clob('APEX usage not detected');
65 end if;
66
67 end DBMS_FEATURE_APEX;
[Home] [Help]