Here is the step-by-step process to create a job in Oracle that executes the procedure P_TEST to create a backup of the EMPLOYEES table:
1. Connect to the Oracle database as a user with the necessary privileges (e.g., SYSTEM or a user with the CREATE JOB privilege).
2. Create the procedure P_TEST (if it doesn't already exist):
CREATE OR REPLACE PROCEDURE P_TEST AS
BEGIN
DECLARE
backup_table_name VARCHAR2(255);
BEGIN
SELECT 'EMPLOYEES_' || TO_CHAR(SYSTIMESTAMP, 'YYYYMMDD_HH24MISS') INTO backup_table_name FROM dual;
EXECUTE IMMEDIATE 'CREATE TABLE ' || backup_table_name || ' AS SELECT * FROM EMPLOYEES';
END;
END P_TEST;
1. Create a new job using the DBMS_SCHEDULER package:
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'EMPLOYEES_BACKUP_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN P_TEST; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=2;', -- Optional
end_date => NULL, -- Optional
enabled => TRUE);
END;
This will create a new job called EMPLOYEES_BACKUP_JOB that executes the P_TEST procedure. You can adjust the start_date, repeat_interval, and end_date as needed.
Note: Make sure to replace the -- Optional comments with the appropriate values for your specific requirements.
Also, you can use DBMS_SCHEDULER.CREATE_PROGRAM and DBMS_SCHEDULER.CREATE_JOB to separate the program and job creation.
