Oracle PL/SQL Programming: A Developer's Workbook

Intermediate

Q:
30-8.	Here’s a suggested procedure: /* Filename on web page: analyze_oem.sp */ CREATE OR REPLACE PROCEDURE analyze_oem IS -- Analyze the OEM schema. This job will be submitted -- every hour by DBMS_JOB. -- cursor to get the current hour CURSOR curs_get_hour IS SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL; v_current_hour NUMBER(2); -- option for analyze command v_method_opt VARCHAR2(15); BEGIN -- get the current hour OPEN curs_get_hour; FETCH curs_get_hour INTO v_current_hour; CLOSE curs_get_hour; -- if the current hour is 1 or 2 inclusive -- then perform a complete analysis otherwise just do -- it for the indexes IF v_current_hour IN (1,2) THEN v_method_opt := NULL; ELSE v_method_opt := 'FOR ALL INDEXES'; END IF; DBMS_UTILITY.ANALYZE_SCHEMA( SCHEMA => 'OEM', METHOD => 'COMPUTE', METHOD_OPT => v_method_opt ); END analyze_oem;
Q:
30-9.	This is one possible implementation: /* Filename on web page: add_trx.month.sp*/ CREATE OR REPLACE PROCEDURE add_trx_month IS /* || This function adds a new partition to the account_trx || table. It is to be run on the 20th of the month to || create a partition for the next month. */ -- cursor to build date format requirements CURSOR curs_get_date_info IS SELECT '01-' || TO_CHAR(ADD_MONTHS(SYSDATE,1),'MON-YYYY') upper_bound, TO_CHAR(SYSDATE,'YYYYMM') ...