CREATE GLOBAL TEMPORARY TABLE HR.SYS_TEMP_FBT ( SCHEMA VARCHAR2(130), OBJECT_NAME VARCHAR2(130), OBJECT# NUMBER, RID UROWID(4000), ACTION CHAR(1) ) ON COMMIT PRESERVE ROWS;
CREATE OR REPLACE VIEW HR.EMP_DETAILS_VIEW ( EMPLOYEE_ID, JOB_ID, MANAGER_ID, DEPARTMENT_ID, LOCATION_ID, COUNTRY_ID, FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT, DEPARTMENT_NAME, JOB_TITLE, CITY, STATE_PROVINCE, COUNTRY_NAME, REGION_NAME ) AS SELECT e.employee_id, e.job_id, e.manager_id, e.department_id, d.location_id, l.country_id, e.first_name, e.last_name, e.salary, e.commission_pct, d.department_name, j.job_title, l.city, l.state_province, c.country_name, r.region_name FROM employees e, departments d, jobs j, locations l, countries c, regions r WHERE e.department_id = d.department_id AND d.location_id = l.location_id AND l.country_id = c.country_id AND c.region_id = r.region_id AND j.job_id = e.job_id WITH READ ONLY;
CREATE SEQUENCE HR.DEPARTMENTS_SEQ START WITH 280 INCREMENT BY 10 MINVALUE 1 MAXVALUE 9990 NOCACHE;
CREATE SEQUENCE HR.EMPLOYEES_SEQ START WITH 207 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 NOCACHE;
CREATE SEQUENCE HR.LOCATIONS_SEQ START WITH 3300 INCREMENT BY 100 MINVALUE 1 MAXVALUE 9900 NOCACHE;
CREATE OR REPLACE PROCEDURE HR.add_job_history ( p_emp_id job_history.employee_id%type , p_start_date job_history.start_date%type , p_end_date job_history.end_date%type , p_job_id job_history.job_id%type , p_department_id job_history.department_id%type ) IS BEGIN INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id); END add_job_history; /
CREATE OR REPLACE PROCEDURE HR.secure_dml IS BEGIN IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00' OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN RAISE_APPLICATION_ERROR (-20205, 'You may only make changes during normal office hours'); END IF; END secure_dml; /
|