Schedule Oracle Job
I need to schedule a job in oracle that will insert data into an oracle table every night, I already have the sql script. Not sure how to do it in Oracle.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not necessarily. You can include your script instead of the stored procedure in between BEGIN and END for Job_Action parameter.
ASKER
do I change the job_type from PLSQL_BLOCK? Do I run all these jobs at sqlplus script?
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'Myjob',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN Insert into Customers Select * from CustomerInfo; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'Myjob',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN Insert into Customers Select * from CustomerInfo; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It does not seem to like the SQL statements in the PL/SQL. I tried it in RMAN
begin
-- Insert PL/SQL code here
select * from customer;
end;
--the table is there not sure why the errorbelow
Details ORA-06550: line ORA-06550: line 2, column 15: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 2, column 1: PL/SQL: SQL Statement ignored , column :
begin
-- Insert PL/SQL code here
select * from customer;
end;
--the table is there not sure why the errorbelow
Details ORA-06550: line ORA-06550: line 2, column 15: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 2, column 1: PL/SQL: SQL Statement ignored , column :
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
INSERT ALL INTO Customer2(field1, field2, field3,field4,field5)
SELECT DISTINCT ...... from Customer.
SELECT DISTINCT ...... from Customer.
ASKER