SQL>
SQL> CREATE OR REPLACE PROCEDURE analyze_oem
2 IS
3 CURSOR curs_get_hour IS SELECT TO_CHAR (SYSDATE, 'HH24') FROM dual;
4 v_current_hour NUMBER (2);
5 v_method_opt VARCHAR2 (15);
6 BEGIN
7 OPEN curs_get_hour;
8 FETCH curs_get_hour INTO v_current_hour;
9 CLOSE curs_get_hour;
10
11 IF v_current_hour IN (1, 2)
12 THEN
13 v_method_opt := NULL;
14 ELSE
15 v_method_opt := 'FOR ALL INDEXES';
16 END IF;
17
18 DBMS_UTILITY.analyze_schema (
19 schema => 'OEM',
20 method => 'COMPUTE',
21 method_opt => v_method_opt
22 );
23 END analyze_oem;
24 /
Procedure created.