[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Schedule  Oracle Job

Posted on 2012-09-20
9
Medium Priority
?
654 Views
Last Modified: 2012-10-02
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.
0
Comment
Question by:Nana Hemaa
  • 4
  • 4
9 Comments
 
LVL 5

Assisted Solution

by:sameer_goyal
sameer_goyal earned 1125 total points
ID: 38420592
It is a 2 step process basically.

First, create a Job

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN my_job_procedure; 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;

Second, create a schedule

-- Create the schedule.
BEGIN
  DBMS_SCHEDULER.create_schedule (
    schedule_name   => 'test_hourly_schedule',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0',
    end_date        => NULL,
    comments        => 'Repeats hourly, on the hour, for ever.');
END;


You can change frequency to suit your need.

Hope it helps.

For further detail, http://www.oracle-base.com/articles/10g/scheduler-10g.php
0
 

Author Comment

by:Nana Hemaa
ID: 38420614
Do I have to have a stored procedure to be able to this.  I just have the sql statements to pull the data from Oracle DB
0
 
LVL 5

Expert Comment

by:sameer_goyal
ID: 38420617
Not necessarily. You can include your script instead of the stored procedure in between BEGIN and END for Job_Action parameter.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 

Author Comment

by:Nana Hemaa
ID: 38420664
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;
0
 
LVL 5

Assisted Solution

by:sameer_goyal
sameer_goyal earned 1125 total points
ID: 38420672
Keep the job_type to PLSQL_BLOCK only

And yes, execute this from sqlplus script
0
 

Author Comment

by:Nana Hemaa
ID: 38421031
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 :
0
 
LVL 5

Accepted Solution

by:
sameer_goyal earned 1125 total points
ID: 38421078
Qualify the name of the table with the database and/or schema

select * from [dbname].[schemaname].customer;

and try again
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 375 total points
ID: 38421415
>>And yes, execute this from sqlplus script

you cannot execute a sqlplus script using PL/SQL Block from dbms_scheduler.

Best case you change the job to 'EXECUTABLE' and create a BAT/Powershell/shell script or external program.

If you can provide a little more about how the sqlplus script loads trhe data we can likley provide alternatives.
0
 

Author Comment

by:Nana Hemaa
ID: 38437317
INSERT ALL INTO Customer2(field1, field2, field3,field4,field5)
SELECT DISTINCT   ......  from Customer.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question